Using SQL Sever 2008 Database Extended Properties to Automate Database Management Tasks

No Comments

Written by Luis Miguel – Originally published on June 12, 2011

Like any manual process, adding users to databases and setting their permissions on more than a few databases is a tedious and error-prone endeavor. By creating a couple of “admin” stored procedures, a DBA can automate this process to the point of being able to execute this task in one fell swoop, at the query editor by typing a few words just once.

If you care for documentation [I do], SQL Server Extended Properties are very convenient when it comes to adding tags, or labels to objects. I particularly find very useful to mark databases with notes that describe their purpose or their level of security sensitivity, etc…

Once you have a standard for these documentation tags and you keep them updated they can be a huge helper for performing database administration tasks very effectively.

Here is a list of the components I’m using to build this solution. I’m keeping all these administrative objects in the (I hope this name is descriptive enough) UTILTY_ADMIN user database

1 – The table to keep a list of the databases that are currently online and have a some kind of Extended Property

USE utility_admin
CREATE TABLE extendedproperties
     dbname   VARCHAR(255),
     ep_name  VARCHAR(128),
     ep_value VARCHAR(128)

2 – The stored procedure that does the brunt of the work, querying the Extended Properties of each database and storing them in the UTILITY_ADMIN.dbo.ExtendedProperties table

USE utility_admin
CREATE PROCEDURE Getextendedproperties @DatabaseName VARCHAR(255) = NULL
  IF @DatabaseName IS NULL
    SET @DatabaseName = Db_name()
  IF Object_id(N’tempdb.dbo.##temp_ExtendedProperties’) IS NOT NULL
    DROP TABLE ##temp_extendedproperties
  CREATE TABLE ##temp_extendedproperties
       ep_name  VARCHAR(128),
       ep_value VARCHAR(128)
  SET @SQLText =
‘INSERT INTO ##temp_ExtendedProperties SELECT CAST(name AS VARCHAR(128)), CAST(value AS VARCHAR(128)) FROM ’
+ @databasename +
‘.sys.fn_listextendedproperty(default, default, default, default, default, default, default)’
  -- That is 7 defaults

3 – An SQL Server Agent job that runs every hour so that the list of databases with extended properties stays up to date

TRUNCATE TABLE utility_admin.dbo.extendedproperties
EXEC MASTER.sys.Sp_msforeachdb
‘INSERT INTO utility_admin.dbo.ExtendedProperties EXEC utility_admin.dbo.GetExtendedProperties ?’ 

4 – UTILTIY_ADMIN.dbo.dbaManageUserRole – This is the stored procedure you are going to execute to actually add the user and set his/her permissions. It requires three parameters, the name of the user, the name of the extended property and the value of the extended property. I have hardwired the db_datareader role into this procedure but with a little bit of imagination you could also add the role as a parameter and make it even more flexible.

CREATE PROCEDURE Dbamanageuserrole @user     VARCHAR(64),
                                   @ep_name  VARCHAR(128),
                                   @ep_value VARCHAR(128)
  DECLARE @dbname VARCHAR(128)
  DECLARE @adduser VARCHAR(512)
  DECLARE @addrole VARCHAR(512)
    SELECT dbname
    FROM   utility_admin.dbo.extendedproperties
    WHERE  ep_name = @ep_name
           AND ep_value = @ep_value
  OPEN dbcursor
  FETCH NEXT FROM dbcursor INTO @dbname
        SET @adduser = ‘USE ’ + @dbname + ‘; CREATE USER ’ + @user +
                       ‘ FOR LOGIN ’
        SET @addrole = ‘USE ’ + @dbname +
                       ‘; EXEC sp_addrolemember N”db_datareader”, ’ + @user
        FETCH NEXT FROM dbcursor INTO @dbname
  CLOSE dbcursor
  DEALLOCATE dbcursor 

Now we are ready to enjoy the rewards of our hard work time and time again. Here is the command I used to add the user ‘canela’ as a ‘db_datareader’ to all the databases I have tagged with ‘application_’ ‘reporting_’

EXEC Dbamanageuserrole

Here is a picture of one of my lab servers showing a long list of dummy databases on the left pane and a shorter result list of the ones with extended properties on the bottom right side.

Powered by Netfirms