SQL Server Reporting Services provides functionality to automatically generate a report on a recurring basis. These reports can then be emailed or written to a file. The method for doing this is called a subscription. It's common for a developer to create a report and then create a subscription that sends that report to the business group or resource that requested it.
Problems come about, however, when the developer leaves the company. When this happens, most likely, his or her Active Directory account will be disabled. As soon as that happens, all subscriptions that were created by the developer stop working. This is because the developer was the owner of the subscription. Since the account is disabled, the subscription no longer has access to the database and SSRS server. To make matters worse, currently there is no way to change the owner of a subscription using the SSRS user interface. That means to fix this, DBAs are stuck manually deleting the subscriptions and recreating them using an account that won't be disabled in the future.
But there is a better way. We can go into the tables of the report server database and change the owner of the subscription. This information is stored in the OwnerID field of the Subscriptions table in the ReportServer database. Here are the first two columns of that table:
For our purposes, these are the only columns we need to worry about. You'll notice both the SubscriptionID and OwnerID columns are uniqueidentifier datatypes. SubscriptionID is the primary key for the table and OwnerID is a foreign key to the Users table. The User table looks like this:
So to change the owner of a subscription, we just need to change the value in the Subscription.OwnerID field to a UserID value from the Users table that corresponds to an Active Directory account that is not disabled.
As an infrequently performed task, this isn't too bad to do manually. However, if you are at a large company that has many SSRS developers, lots of subscriptions, and a normal to high employee turnover rate, this could become quite a hassle.
So, like any good DBA, I looked for a way to automate this process. I came up with this methodology:
- Weekly, scan the Subscriptions table and get a list of the OwnerIDs for all subscriptions.
- Cross-reference that list to the Users table to find the Active Directory accounts of those owners (stored in the Users.UserName field)
- Query Active Directory to see if those accounts are still valid.
- If they are not, update the Subscriptions.OwnerID field to use a valid Active Directory account.
- Log the original and replacement values of any data that changed, as well as the date of the change.
Querying Active Directory via T-SQL can be done by using a linked server. Details can be found here. Note that in order to use this method, your server must be configured to allow ad hoc distributed queries.
Before I get to the code that implements this, there are a couple of assumptions that I need to point out. If your environment does not share these assumptions, you'll need to tweak the code.
- In our Active Directory environment, accounts are disabled in two ways:
- If the account belonged to an employee who left, the account is moved to an AD organization unit (ou) called "Disabled Accounts"
- If the account belonged to a temporary employee, the account is deleted, meaning it is no longer in AD at all.
- We created a dedicated AD account that we use solely for a subscription owner. This is the account that will become the replacement subscription owner. You could use the account of another developer or DBA, or any other active AD account you want, however.
- This script assumes a single domain. If your environment utilizes multiple domains, you'll need to modify the script.
- You'll need to modify the @SQLString variable in two places to refer to your Active Directory environment in the OPENROWSET command.
- Our database servers are case-insensitive. I have not tested this script on a case-sensitive server. I tried to be consistent in my typing, but I make no guarantees.
- Replace items in <>'s with values appropriate to your environment.
Some notes on the data in the tables: As mentioned, we will be replacing the disabled owner account with a different account in the Users table. In order to exist in this table, that account will need to access SSRS and create a report or subscription first. (The report or subscription can then be deleted. The account just needs to create something first for SSRS to create an entry in the Users table.) Also, the UserName field of the Users table stores the AD account in the format "Domain\Account". When we query Active Directory via a linked server, we'll need to strip off the domain prefix.
Finally, here is the code. The high-level overview of how it works is:
- Check the status of the allow ad hoc distributed queries option on the server. The code will make a note of the setting when it first runs and will return it to the original value when complete. If this option is disabled, it enables it.
- The account that will be used as the new subscription owner is hard coded. You should make sure this account has an entry in the ReportServer.dbo.Users table. Enter the account in the script in the format "Domain\Account" and assign it to the @NewSubOwner variable.
- A list is made of all subscription owners.
- The entries in that list are checked to see if they are in the "Disabled Accounts" Active Directory ou. Subcriptions owned by those accounts will be changed to the new subscription owner.
- The entries in the list are checked to see if they are in Active Directory at all. Subscriptions owned by accounts that are not will be changed to the new subscription owner.
- Log the subscription IDs and old and new subscription owner IDs to a log table.
- Update the Subscriptions table.
- Reset the allow ad hoc distributed queries server option to what it was before we started.
- Perform log table maintenance by deleting entries older than 3 months.
(All the single quotes and backslashes appear to be too much for my text coloring plug-in to handle, so the text coloring is off on the blog. But if you paste the code into SSMS, you will be ok.)
USE ReportServer /* Procedure to check for SSRS subscriptions owned by AD accounts that are no longer active. (If account is no longer active, subscriptions will stop.) This will change those subscriptions to a new user. SJS 7-21-14 Initial release */ /* First, check status of ad hoc distributed queries. If disabled, enable it. All changes are logged to ReportSubscriptionOwnerChangeLog and retained for 3 months Reset status of ad hoc distributed queries to what it was before this ran Based on http://blogs.msdn.com/b/miah/archive/2008/07/10/tip-change-the-owner-of-report-server-subscription.aspx THIS ASSUMES ACCOUNTS ARE IN ONE DOMAIN! */ DECLARE @SQLString VARCHAR(500) DECLARE @SubOwner VARCHAR(260) DECLARE @OldSubOwner VARCHAR(260) DECLARE @NewSubOwner VARCHAR(260) DECLARE @OldOwnerID UNIQUEIDENTIFIER DECLARE @NewOwnerID UNIQUEIDENTIFIER SET @NewSubOwner = '<Domain>\<Account>' SELECT @NewOwnerID = UserID FROM Users WHERE UserName = @NewSubOwner /* Tables used: ##SubscriptionOwners - accounts that own report subscriptions ##DisabledAccounts - subscription owners that are in the AD 'Disabled Accounts' ou ##InAD - subscription owners that are in AD (will contain records in above table too) ##AdHocDistributedQueries - used to track status of SQL Server configuration so it can be reset to original value after execution ##SubOwnersToChange - list of subscription owners that we need to change */ /* House keeping - set up our tables */ IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results IF OBJECT_ID('tempdb..##DisabledAccounts') IS NOT NULL BEGIN DROP TABLE ##DisabledAccounts END CREATE TABLE ##DisabledAccounts ( name VARCHAR(500) ,displayname VARCHAR(500) ,givenname VARCHAR(500) ,distinguishedname VARCHAR(500) ,samaccountname VARCHAR(100) ) IF OBJECT_ID('tempdb..##InAD') IS NOT NULL BEGIN DROP TABLE ##InAD END CREATE TABLE ##InAD ( name VARCHAR(500) ,displayname VARCHAR(500) ,givenname VARCHAR(500) ,distinguishedname VARCHAR(500) ,samaccountname VARCHAR(100) ) IF OBJECT_ID('tempdb..##SubscriptionOwners') IS NOT NULL BEGIN DROP TABLE ##SubscriptionOwners END CREATE TABLE ##SubscriptionOwners (SubOwner VARCHAR(100)) IF OBJECT_ID('tempdb..##AdHocDistributedQueries') IS NOT NULL BEGIN DROP TABLE ##AdHocDistributedQueries END CREATE TABLE ##AdHocDistributedQueries ( name VARCHAR(200) ,minimum TINYINT ,maximum TINYINT ,config_value TINYINT ,run_value TINYINT ) IF OBJECT_ID('tempdb..##SubOwnersToChange') IS NOT NULL BEGIN DROP TABLE ##SubOwnersToChange END CREATE TABLE ##SubOwnersToChange (SubOwner VARCHAR(260)) /* Start processing */ /* Get list of distinct subscription Owners */ INSERT INTO ##SubscriptionOwners (SubOwner) SELECT DISTINCT REPLACE(u.[UserName], '<Domain>\', '') FROM Users u ,subscriptions s WHERE u.UserID = s.OwnerID /* Check value of Ad hoc distributed queries so we can reset it to its original state when done */ /* Make sure we can see advanced options */ EXEC sp_configure 'show advanced options' ,1 RECONFIGURE INSERT INTO ##AdHocDistributedQueries EXEC sp_configure 'ad hoc distributed queries' IF EXISTS ( SELECT run_value FROM ##AdHocDistributedQueries WHERE run_value = 0 ) BEGIN EXEC sp_configure 'ad hoc distributed queries' ,1 RECONFIGURE END DECLARE SubscriptionOwnersCursor CURSOR FOR SELECT subOwner FROM ##SubscriptionOwners OPEN SubscriptionOwnersCursor FETCH NEXT FROM SubscriptionOwnersCursor INTO @SubOwner WHILE @@fetch_status = 0 BEGIN /* See if any of those subscriptions owners are in the disabled accounts Active Directory ou. This handles people who were employees but left */ SET @SQLString = 'INSERT INTO ##DisabledAccounts SELECT name, displayname, givenname, distinguishedname, samaccountname FROM OPENROWSET(''ADSDSOObject'',''adsdatasource'';''<AD Account>'';''<password>'', ''SELECT name, displayname, givenname, distinguishedname, samaccountname FROM ''''LDAP://<fqdn>/ou=Disabled accounts,dc=<your dc>,dc=<your dc>,dc=<your dc>'''' WHERE samaccountname = ''''' + @SubOwner + ''''''')' --SELECT @SQLString EXEC (@SQLString) /* Holy crap, that is a lot of single quotes. Resulting string should look like: 'SELECT name, displayname, givenname, distinguishedname, samaccountname FROM ''LDAP://<fqdn>/dc=<your dc>,dc=<your dc>,dc=<your dc>'' WHERE samaccountname = ''<account name>''') */ /* See if any of those subscriptions owners are in Active Directory at all. This handles people who were never full time employees. This will find users that are _in_ AD and later we will exclude them from our list of users to change, leaving us with users _not_ in AD. */ SET @SQLString = 'INSERT INTO ##InAD SELECT name, displayname, givenname, distinguishedname, samaccountname FROM OPENROWSET(''ADSDSOObject'',''adsdatasource'';''<AD Account>'';''<password>'', ''SELECT name, displayname, givenname, distinguishedname, samaccountname FROM ''''LDAP://<fqdn>/dc=<your dc>,dc=<your dc>,dc=<your dc>'''' WHERE samaccountname = ''''' + @SubOwner + ''''''')' EXEC (@SQLString) FETCH NEXT FROM SubscriptionOwnersCursor INTO @SubOwner END CLOSE SubscriptionOwnersCursor DEALLOCATE SubscriptionOwnersCursor /* Now look at the data we gathered from AD and translate that into subscription owners we need to change */ /* Users that left */ INSERT INTO ##SubOwnersToChange (SubOwner) SELECT name FROM ##DisabledAccounts /*Users that never were FTEs */ INSERT INTO ##SubOwnersToChange (SubOwner) SELECT SubOwner FROM ##SubscriptionOwners WHERE SubOwner NOT IN (SELECT samaccountname FROM ##InAD) /* Now do the actual updating of subscriptions */ DECLARE SubOwnersToChangeCursor CURSOR FOR SELECT SubOwner FROM ##SubOwnersToChange OPEN SubOwnersToChangeCursor FETCH NEXT FROM SubOwnersToChangeCursor INTO @OldSubOwner WHILE @@fetch_status = 0 BEGIN SET @OldSubOwner = '<Domain>\' + @OldSubOwner SELECT @OldOwnerID = UserID FROM dbo.Users WHERE UserName = @OldSubOwner INSERT INTO ReportSubscriptionOwnerChangeLog (SubscriptionID ,OldSubOwner ,NewSubOwner ,OldSubOwnerID ,NewSubOwnerID) SELECT SubscriptionID ,@OldSubOwner ,@NewSubOwner ,@OldOwnerID ,@NewOwnerID FROM Subscriptions WHERE OwnerID = @OldOwnerID UPDATE Subscriptions SET OwnerID = @NewOwnerID WHERE OwnerID = @OldOwnerID FETCH NEXT FROM SubOwnersToChangeCursor INTO @OldSubOwner END CLOSE SubOwnersToChangeCursor DEALLOCATE SubOwnersToChangeCursor /* Set ad hoc distributed queries back to original value */ IF EXISTS ( SELECT run_value FROM ##AdHocDistributedQueries WHERE config_value = 0 ) BEGIN EXEC sp_configure 'ad hoc distributed queries' ,0 RECONFIGURE END /* For testing SELECT * FROM ##DisabledAccounts SELECT * FROM ##InAD SELECT * FROM ##SubscriptionOwners SELECT * FROM ##AdHocDistributedQueries SELECT * FROM ##SubOwnersToChange */ /* Clean up after ourselves */ DROP TABLE ##SubscriptionOwners DROP TABLE ##DisabledAccounts DROP TABLE ##InAD DROP TABLE ##AdHocDistributedQueries DROP TABLE ##SubOwnersToChange /* Maintain log table */ DELETE FROM ReportSubscriptionOwnerChangeLog WHERE ChangeDate < DATEADD(m, -3, CURRENT_TIMESTAMP)
Note: I am by no means an expert on querying Active Directory with the OPENROWSET command. There are several limitations and things to watch out for when doing this. Refer to the bottom of this post for more info.