Fixing Broken SSRS Subscriptions

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:

SSRSSubTable

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:

SSRSUsersTable

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.

  1. In our Active Directory environment, accounts are disabled in two ways:
    1. If the account belonged to an employee who left, the account is moved to an AD organization unit (ou) called “Disabled Accounts”
    2. If the account belonged to a temporary employee, the account is deleted, meaning it is no longer in AD at all.
  2. 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.
  3. This script assumes a single domain. If your environment utilizes multiple domains, you’ll need to modify the script.
  4. You’ll need to modify the @SQLString variable in two places to refer to your Active Directory environment in the OPENROWSET command.
  5. 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.
  6. 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.

2 thoughts on “Fixing Broken SSRS Subscriptions

    1. That is explained after steps 1-6 above:

      “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.)

      So make a report with the account, then delete the report. SSRS will add the entry to the table for you.

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.