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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dba_StoreDatabasePermissions]') AND type in (N'P', N'PC'))
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','WINDOWS_USER') --Updated to include WINDOWS_USER on 10/5/2011. Thanks to Alex Hatcher for catching that.
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
EXEC sp_MS_marksystemobject 'sp_dba_StoreDatabasePermissions'
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dba_GetDatabasePermissions]') AND type in (N'P', N'PC'))
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
EXEC sp_MS_marksystemobject 'sp_dba_GetDatabasePermissions'
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.