RSSUsing SQL Sever 2008 Database Extended Properties to Automate Database Management Tasks
Jun 12
Instance Administration No Comments
Written by Luis Miguel – Originally published on June 12, 2011
Problem:
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
GO
CREATE PROCEDURE Getextendedproperties @DatabaseName VARCHAR(255) = NULL
AS
IF @DatabaseName IS NULL
SET @DatabaseName = Db_name()
DECLARE @SQLText NVARCHAR(2000)
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
EXECUTE(@SQLText)
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)
AS
DECLARE @dbname VARCHAR(128)
DECLARE @adduser VARCHAR(512)
DECLARE @addrole VARCHAR(512)
DECLARE dbcursor CURSOR FOR
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
WHILE @@FETCH_STATUS = 0
BEGIN
SET @adduser = ‘USE ’ + @dbname + ‘; CREATE USER ’ + @user +
‘ FOR LOGIN ’
+
@user
SET @addrole = ‘USE ’ + @dbname +
‘; EXEC sp_addrolemember N”db_datareader”, ’ + @user
EXECUTE(@adduser)
EXECUTE(@addrole)
FETCH NEXT FROM dbcursor INTO @dbname
END
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
canela,
application_,
reporting_
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.
