Backing up Server Logins and Permissions

In a previous post, i talked about how to add WMI Alerts for Create Database and Create Login. Now if we want to take this a step further, we can create a job to extract out server logins and database permissions every time a new login is created.

Extracting Server Logins

This is fairly straightforward as Microsoft provide the sp_help_revlogin procedure to make this a painless task. The procedure and how to use it can be found here

This generates a easy to import list of all server logins including encrypted passwords. This can be particularly useful when migrating databases or moving instances.

Ive amended this procedure to add functionality to retrieve server roles

DECLARE @prname sysname DECLARE @pmname sysname DECLARE sr_curs CURSOR FOR SELECT pr.name, pm.name
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS pm ON rm.role_principal_id=pm.principal_id
JOIN sys.server_principals AS pr ON rm.member_principal_id=pr.principal_id
WHERE pr.name <> 'sa' AND pr.name NOT LIKE 'NT SERVICE%' AND pr.name NOT LIKE 'NT AUTHORITY%' OPEN sr_curs
FETCH NEXT FROM sr_curs INTO @prname, @pmname
WHILE (@@fetch_status <> -1) BEGIN SET @tmpstr = 'EXEC sp_addsrvrolemember '''+@prname+''', '''+@pmname+'''' PRINT @tmpstr
FETCH NEXT FROM sr_curs INTO @prname, @pmname
END CLOSE sr_curs
DEALLOCATE sr_curs

Extracting user permissions on databases

Many years ago i found a procedure developed by Clint Herring (SQL Server Central) which extracts out user permissions for a DB. I’ve been using it in SQL 2000, 2005 and 2008 without any issues.

sp_getUserInfo procedure available here

Then by simply running the procedure with a database variable input, it will list out all the permissions on that db.

sp_getuserinfo 'DB_Monitoring'

Extracting ALL database permissions and Server logins

So, to make life easier, i created a job which initially extracts server logins to a file, (CMDExec)

sqlcmd -E -S MSSQLSERVER\Instance -d master -Q “exec sp_help_revlogin” -o”D:\Log\Logins\Server_logins.sql”

and then created a 2nd step which extracted all user permissions for all databases to a file per database. (T-SQL)

Set Nocount on Declare @dbname varchar(100) Declare @servername varchar(100) Declare db Cursor For Select name from master.dbo.sysdatabases

Declare @osql varchar(1000) select @servername = @@servername Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
    Begin Set @osql='EXEC master.dbo.xp_cmdshell '+''''+'sqlcmd -E -S '+@servername+' -d DB_Monitoring -Q "sp_getuserinfo ['+@dbname+']" -o"D:\Log\Logins\'+@dbname+'.sql"'+'''' EXEC (@osql) --Execute the osql statement Fetch Next from db into @dbname
    End Close db

Deallocate db

Connecting the above to an Alert

Go into the Alert properties and add a response to execute the job

image

So now every time a new login is created, we back up all permissions and server logins!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: