Tag: Replication

Stored Procedures to Store and Get Database Users with All Permissions

Posted on by David Levy

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

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.