Introduction
Traditionally when doing a restore, moving a database, etc. a DBA would simply go into Management Studio and use the Generate Scripts wizard to script all logins and permissions. This approach can take several minutes and as I recently learned does not script database level permissions.
Solution
Bothered by the amount of effort and the fact that I kept forgetting to script out the permissions until just after I had wiped them out by starting a restore, I set out to create scripts that I could just include as steps in my restore jobs. Given that the restore wipes out the database, I knew I had to have 2 steps. The first step stores the permissions before the restore, while the second puts them back after. In the spirit of keeping the restore jobs simple, I wrapped up all of the logic into 2 stored procedures that do not require any arguments.
The first stored procedure, sp_dba_StoreDatabasePermissions, stores the logins roles and permissions to a table in msdb. I use msdb here because everybody has it and it is not master. The table is named for the database it corresponds to followed by ‘_permission_store’. The permission store table has 2 columns, the first is the actual SQL command while the second is the order to run it in. The ordering is done in groups, with all roles ranked to be run first, followed by users, adding users to roles and finally the permissions that correspond to the users and roles. The stored procedure makes use of a synonym to point to the permission store, cutting down on the use of dynamic SQL and enhancing readability. The logic to get the permissions is based on the logic of a script by Narayana Vyas Kondreddi.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | USE [master] GO GO GO DROP PROCEDURE [dbo].[sp_dba_StoreDatabasePermissions] GO CREATE PROCEDURE [dbo].[sp_dba_StoreDatabasePermissions] AS BEGIN DECLARE @table_name sysname, @create_cmd nvarchar(4000) SELECT @table_name = db_name() + '_permission_store' IF NOT EXISTS(SELECT * FROM msdb.sys.sysobjects WHERE name = @table_name) BEGIN SELECT @create_cmd = 'CREATE TABLE [msdb].[dbo].' + QUOTENAME(@table_name) + ' (command nvarchar(max), run_order int)' END EXEC sp_executesql @create_cmd SELECT @create_cmd = 'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N''permission_store'') CREATE SYNONYM permission_store FOR [msdb].[dbo].' + QUOTENAME(@table_name) EXEC sp_executesql @create_cmd INSERT permission_store SELECT 'IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''' + name + ''') CREATE ROLE [' + name + ']' AS Command, 0 AS run_order FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE' AND is_fixed_role = 0 INSERT permission_store SELECT 'IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''' + name + ''') CREATE USER [' + name + '] FOR LOGIN [' + name + ']' + ISNULL(' WITH DEFAULT_SCHEMA=[' + default_schema_name + ']', '') AS Command, 1 AS run_order FROM sys.database_principals WHERE type_desc in ('WINDOWS_GROUP', 'SQL_USER') INSERT permission_store SELECT 'EXEC sp_addrolemember @rolename=''' + dp_r.name + ''', @membername =''' + dp_m.name + '''' AS Command, 2 AS run_order FROM sys.database_role_members drm INNER JOIN sys.database_principals dp_r ON drm.role_principal_id = dp_r.principal_id INNER JOIN sys.database_principals dp_m ON drm.member_principal_id = dp_m.principal_id WHERE dp_m.name NOT IN ('dbo') INSERT permission_store SELECT 'IF EXISTS(SELECT * FROM sys.objects WHERE name = ''' + obj.name + ''' AND USER_NAME(schema_id) = ''' + USER_NAME(obj.schema_id) + ''' AND type = ''' + RTRIM(obj.type) + ''') ' + CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + ' ' + perm.permission_name + ' ' + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) + CASE WHEN cl.column_id IS NULL THEN '' ELSE '(' + QUOTENAME(cl.name) + ')' END + ' TO ' + QUOTENAME(usr.name) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN '' ELSE ' ' + 'WITH GRANT OPTION' END AS Command, 3 AS run_order FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id INNER JOIN sys.database_principals AS adm ON perm.grantor_principal_id = adm.principal_id LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id END GO GO |
The second stored procedure in the pair, sp_dba_GetDatabasePermissions, is very simple. First it checks for the synonym in the database and creates it if it is missing. After verifying the synonym, the stored procedure opens up a cursor against the permission store and begins executing commands ordered ascending by run_order. After running all commands, the stored procedure checks the value of the optional parameter @keep_permission_store to see if it should clean up the permission store table or leave it out there. The default behavior is to drop the permission store when done with it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | USE [master] GO GO GO DROP PROCEDURE [dbo].[sp_dba_GetDatabasePermissions] GO CREATE PROCEDURE [dbo].[sp_dba_GetDatabasePermissions] @keep_permission_store bit = 0 AS BEGIN DECLARE @command nvarchar(max) SELECT @command = 'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N''permission_store'') CREATE SYNONYM permission_store FOR [msdb].[dbo].' + QUOTENAME(db_name() + '_permission_store') EXEC sp_executesql @command DECLARE commands CURSOR FAST_FORWARD READ_ONLY FOR SELECT command FROM dbo.permission_store ORDER BY run_order OPEN commands FETCH NEXT from commands INTO @command WHILE(@@FETCH_STATUS <> -1) BEGIN PRINT @command EXEC sp_executesql @command FETCH NEXT from commands INTO @command END CLOSE commands DEALLOCATE commands IF @keep_permission_store != 1 BEGIN SELECT @command = 'DROP TABLE ' + base_object_name FROM sys.synonyms WHERE name = 'permission_store' DROP SYNONYM dbo.permission_store PRINT @command EXEC sp_executesql @command END END GO GO |
Other Uses
It seems like every day I find more uses for these stored procedures. Most recently I have been running them at replication subscribers before making any changes at the publisher. The @keep_permission_store flag of sp_dba_GetDatabasePermissions comes in really handy here. Passing a value of 1 allows permissions to be put back several times, a lifesaver when things do not go right on the first try.
I also use these stored procedures to copy permissions between databases on different servers. I started out running sp_dba_StoreDatabasePermissions then selecting from the permission store table ordered by rank in text, copying, pasting and running on the new server. After adding the print statement to sp_dba_GetDatabasePermissions it was just easier to call sp_dba_StoreDatabasePermissions then sp_dba_GetDatabasePermissions right away, copying the commands from the messages window and pasting them into a query window to run wherever I want.
Wrap-Up
These stored procedures have served me well and I hope they will serve you well. I would love to hear how people are using them and any suggestions for enhancements. As always, scripts on the internet are like Halloween candy, check them thoroughly before consuming.
