Shaun J Stuart

Just another SQL Server weblog

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.

Share

With the release of SP2 for SQL 2012, SQL Server Management Studio got a bug fix that results in a nice quality of life improvement. Since the initial release of SQL 2012, plain text in SSMS always showed up in teal, no matter what you set it to via the Tools --> Options --> Environment --> Fonts And Colors setting. This bug was filed in a Microsoft Connect item over two years ago. With SP2, it has finally been fixed. This isn't a big deal, but it does make things easier on my tired old eyes.

SSMS 2012 prior to SP2

SSMS 2012 prior to SP2

SSMS 2012 post SP2

SSMS 2012 post SP2

Share

I'm offering a summer school discount of 50% on my Udemy online SQL Server courses!

SQL101

Microsoft SQL Server 101

SQLCompression

​Understanding Data​ Compression In SQL Server

sql server maintenance plans

​SQL Server Maintenance Plans​

 

Share

DBAs gained the ability to disable indexes in SQL Server 2005. This is a handy little option when you are tuning indexes or consolidating indexes. When you disable an index, you tell SQL Server to basically pretend the index doesn't exist. Data changes made to the underlying tables are not reflected in the disabled index. As a result, you can sometimes improve performance by disabling unused indexes.

Disabling an index is not the same as dropping an index. When you drop an index, the index and its definition are deleted from the database. When you disable an index, the index definition is retained in the system catalog and you can still see the index in Management Studio. This may lead you to believe the index data remains as well and that it is just no longer updated with data changes. In actuality, when you disable an index, although the index definition remains in the database, the actual index data is deleted from the database. Indeed, the BOL entry for the ALTER INDEX statement, which is what you use to disable an index, states: "Any index can be disabled. The index definition of a disabled index remains in the system catalog with no underlying index data." This is actually handy if you are unsure if any code is referencing the index. Just dropping the index would cause such references to fail. Disabling the index will also cause them to fail, but because the index definition is retained, it's an easy matter to recreate the index.

Let's verify this by running a test. First, we'll create a test database and make a table with a clustered index.

USE [master]
GO

CREATE DATABASE [Demo]
GO
ALTER DATABASE [Demo] SET RECOVERY SIMPLE
GO

USE Demo

CREATE TABLE [dbo].[BigTable](
[PK] [int] IDENTITY(1,1) NOT NULL,
[ColA] [varchar](100) NULL,
[ColB] [varchar](100) NULL,
[ColC] [varchar](100) NULL,
[ColD] [varchar](100) NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[BigTable]
(
[PK] ASC
) ON [PRIMARY]
GO

 

 

Now, let's add 10,000 rows of data:

INSERT INTO BigTable
(ColA,
ColB,
ColC,
ColD)
VALUES
(newid(),
newid(),
newid(),
newid())
GO 10000

 

 

Let's look at how much space this table takes up.

Now, we'll add a large index - an index on ColA and including ColB, ColC, and ColD. (Note this is not a good indexing strategy - adding every column in a table to an index. I'm doing it here simply to make a large index.)

CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[BigTable]
(
[ColA] ASC
)
INCLUDE (     [ColB],
[ColC],
[ColD])

GO

 

 

Once again, let's check the size of the table and indexes:

We can see that, compared to the sizes before we added the non-clustered index, our index_size increased by 1704 KB.

Now, let's disable the index and see what happens to our sizes:

ALTER INDEX [NonClusteredIndex] ON [dbo].[BigTable] DISABLE
GO

 

As you can see, the index_size dropped back down to what it was before we added the non-clustered index. When we disabled the index, SQL deleted the underlying index data. (If you use the sp_spaceused command without the table name, you will see results for the entire database and you will can verify that the data has indeed been removed from the database.)

When you think about what is going on, you can understand why there is no ENABLE INDEX command. To re-enable a disabled index, you issue an index rebuild command. There is no index data present to re-enable - SQL must recreate it.

The exception to this behavior is when you disable a clustered index. Because the clustered index actually is the table data, SQL cannot delete it. Instead, when you disable the clustered index on the table, the table becomes unavailable.

ALTER INDEX [ClusteredIndex] ON [dbo].[BigTable] DISABLE
GO

You can see that, even though the clustered index has been disabled, the data is still present:

 

 

However, attempting to access the table will generate an error:

So why is this information useful?

I ran into a problem the other day with one of my production databases. This database had been a constant size for months. Suddenly, one day I got a warning that the drive the database was on was running out of disk space. I did some investigating and narrowed down the cause of the file growth to a particular table in the database. The table was a log table and records were being written to it and deleted every day.

My first thought was perhaps we had an increase in activity and more records were being inserted. Luckily, the records included a datestamp, so I was able to look at the number of rows being inserted per day. That number was relatively constant over the past couple months, so that was not the cause of the growth.

Two of the columns in the table were XML data types, so my next thought was perhaps the XML data that was being logged had grown. I checked with our developers and they did not make any changes to the XML data being logged. I also used the DATALENGTH function and verified the average data size of the two fields had remained  the same over the past several months.

So what was going on? Why was my table growing? I needed to get an answer quickly. This table had 26 million rows in it and it was suddenly growing by about 300 MB per day where it had been a constant size previously.

I started running sp_spaceused on a regular basis and writing the output to a table. From here, I was able to see the index space used was very close to the data space used and was growing at about the same rate as the data. Hmmm.

That led me to take a closer look at the indexes. I found a newly added index! This index was on two columns, but had all the other columns of the table included in the index as INCLUDED columns. So this index basically doubled the size of the table. I had found my culprit! It just so happened that while I was digging into this, a developer was online rebuilding this index, so I was able to find out the person responsible for the index. After a brief visit, I found out he was attempting to resolve some performance issues. I gave some advice (including to test on a development system, not production), and I disabled the index. (I didn't just drop the index because we were worried other processes might be referencing it and we wanted to make sure nothing broke before we got rid of it altogether.) Once the index was disabled, the index data was removed from the database, and my file growth stopped.

My next step was to investigate how a developer got DDL access to a production database.

 

Share

I'm pleased to announce my new online course is now up and available: SQL Server Maintenance Plans. This course is almost 2 hours long and covers every aspect of SQL Server's maintenance plans. You'll learn what each and every task does and you'll see examples of how to create plans using both the Maintenance Plan Wizard and the Maintenance Plan Designer. The course also briefly covers setting up SQL Agent, Database Mail, and alerts.

sql server maintenance plans

From now until the end of April, the first 100 people to sign up using this link (or coupon code PLAN50) will get a 50% discount. Starting May 1, all readers of this blog can use the coupon code BLOG15OFF for a 15% discount.

Don't forget - my other courses are still available:

Microsoft SQL Server 101. Use this link for a 15% discount.

Understanding Data Compression in SQL Server. Use this link for a 15% discount.

Share