Changing ownership on ad hoc query selections

Mitchell Gibbs

We're running into issues with a bunch of selections based on ad hoc queries that were created by consultants or former staff. The selections are silently breaking when the rights to the user accounts are removed if they didn't have sysadmin rights when the selection was created. There aren't errors, but no results are returned. This is especially inconvenient for standard exclusions, inclusions, notifications, etc.

At this point, we've got ~800 selections like this. I'm looking at writing either a global change or record operation to clean this up. However, it's a bit complicated and I thought I'd see if anyone has already tackled this and might be willing to share some code?

This is a kb article that's related - which basically says to have a developer fix it :-|

Thanks!

Mitch

Chris Whisenhunt

Mitch, as you know this isn't straight forward, but it can be done with a light hand. You have to assign ownership to a service account and then update the FK relationship tables.

Will Mercer

I don't have an answer for your question but we got around this issue by deactivating users who have left whilst keeping their rights in tact. Might not be a good solution for everyone but it has worked for us since this functionality was introduced.

David Wanless

Dear Mitch, Will and Chris,

I'm intrigued by this. In what circumstances do the selections silently break? Is it when they are run manually or when used in Generate Payments Processes, Post to GL, Global Changes or other Business Processes?

We've just been through a very painful three weeks after two of our old staff accounts had their system administrator permissions revoked in a cleanup, and many core Business Processes silently broke. They would process 0 records when there were actually records to process. We had no idea why they had broken or how many or which other processes had broken. We found that recreating the processes solved the problem for each one, but had no idea which ones had broken so spent ages trawling through hundreds of processes trying to work out which ones had broken, which was tricky because 0 was often the correct number. Despite some valiant efforts from some support staff, Blackbaud as a whole was very slow to take the problem seriously and, to our astonishment, decided that the problem was not mission critical, but did eventually work out what had happened.

See the BB ideas

and

for ways that this problem might be avoided for other organisations in future, and please consider voting for them.

See also

which Blackbaud has updated in response to our problem.

However, in our testing, we didn't see any instances where the selections themselves stopped working when run manually or by the recreated processes. So, the ownership of the Ad-hoc Queries didn't seem to be an issue. It was the ownership of the Business Processes which caused the problem.

I'd be really interested to know whether you have found something which we've not yet observed, or have just another instance of the same problem which caused us so much grief.

Kind regards,

David Wanless

david.wanless@wilderness.org.au

+61 3 6270 1781ph

Mitchell Gibbs

David - This becomes an issue when a query is saved as a Dynamic Selection and then used as input by a process (eg a Smartfield or Global Change - possibly notifications, but I'm not certain). When run manually, it is run using the security context of the current user. However, if it isn't “reprocessed” it runs as saved, which includes the security context of the last person to save it.

David Wanless

Dear Mitchell,

Thanks. That sounds very similar to our situation in terms of outcome (processes silently failing to process any records) except that ours was not, as far as I could tell, to do with the ownership of the Selection/Ad-hoc Query, but rather the ownership of the Business Process itself. I can imagine that this would sometimes be hard to tell apart, as often the user who created the Selection/Ad-hoc Query would be the same as the one who created the Business Process. However, I'm pretty confident that our testing showed that it was the ownership of the Business Process itself which was the issue, and this is certainly what Blackbaud Support and the Knowledgebase were telling us.

I'll ask BB Support if they know anything about the problem you're describing, because I'm pretty sure we saw instances which didn't follow the pattern you observed.

Are you confident that it was the ownership of the Selections/Ad-hoc Queries and not the ownership of the Business Processes, which was the problem?

Is there a KB article about the problem you observed?

Ciao,

David.

Ref: https://kb.blackbaud.com/knowledgebase/articles/Article/107873

Mitchell Gibbs

David-

The Business Process and Selection issues are distinct - though similar. If you run the following SQL:

SELECT
selections.ID, selections.NAME, selections.DESCRIPTION, selections.DBOBJECTNAMEWITHASOFDATE, selections.STATIC, selections.USEINQUERYDESIGNER, f.ROUTINE_DEFINITION
FROM
IDSETREGISTER AS selections
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS f
ON selections.DBOBJECTNAMEWITHASOFDATE = f.ROUTINE_NAME
WHERE
PATINDEX('%record access security%', f.ROUTINE_DEFINITION) > 0

It will return some of the SQL functions generated by the Selections where you can see the embedded security, for example:

CREATE function dbo.[UFN_ADHOCQUERYIDSET_00BAD11F_B7EF_4B47_A929_3991600EE420_WITHASOFDATE](@ASOFDATE datetime)
returns table
as
return
(
/* Record Access Security defined by DOMAIN\\user */
with
[V_QUERY_CONSTITUENT_RACS] as (
select [V_QUERY_CONSTITUENT].*
from dbo.[V_QUERY_CONSTITUENT]
/* Security clause for dbo.V_QUERY_CONSTITUENT */
where
(select ISSYSADMIN from dbo.APPUSER where ID = 'cd6e19b1-2498-454c-b928-ab7d629a4198') = 1 or
(((