Category: Security

How Do I Kill All Sessions from a Windows Group?

Posted on by David Levy

Introduction

The stored procedure I am posting today will kill all sessions for users that are members of a given domain group. I use this stored procedure to keep ad-hoc (write down Access) users out of the way of nightly builds on my data warehouse. I have created two jobs for each group of users that I want to keep out of the way. The first job denies connect to the Windows Group then calls this stored procedure to kick all of the users off. I run this job just before the build begins. The second job grants connect to the group after the nightly build finishes.

The Script

The stored procedure gets all users logged in via Windows Authentication by looking for the slash in their login name, cursoring through the list of users getting all windows groups for each user. If any windows groups that user is a member of match the one passed in then the session is killed.

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
CREATE PROCEDURE dbo.sp_dba_kill_spids_for_domain_group(@domain_group sysname)

AS

SET NOCOUNT ON

DECLARE @spid           smallint,
        @loginame       nvarchar(128),
        @command        nvarchar(max)

CREATE TABLE #Logins
(
    account_name        sysname NULL,
    type                char(8) NULL,
    privilege           char(9) NULL,
    mapped_login_name   sysname NULL,
    permission_path     sysname NULL
)

DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT  RTRIM(login_name),
            session_id
    FROM    sys.dm_exec_sessions
    WHERE   login_nameLIKE '%%' --Look for the slash between domain and login

OPEN csr

FETCH NEXT FROM csr INTO @loginame, @spid

WHILE @@FETCH_STATUS <> -1
 BEGIN
    INSERT #Logins
        EXEC xp_logininfo @loginame, 'all'
   
    IF EXISTS(  SELECT  *
                FROM    #Logins
                WHERE   permission_path = @domain_group    )
     BEGIN
        SELECT  @command = 'KILL ' + CAST(@spid AS nvarchar) + ' --' + @loginame
        PRINT @command
        EXEC sp_executesql @command
     END

    TRUNCATE TABLE #Logins

    FETCH NEXT FROM csr INTO @loginame, @spid
 END

CLOSE csr

DEALLOCATE csr

DROP TABLE #Logins

SET NOCOUNT OFF

GO

Conclusion

As usual, I hope you find this stored procedure helpful. Be careful that you know who is in a group before killing the sessions. I have seen situations where people found out they were in the wrong group because their sessions kept getting killed. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

How Can I Tell if a Windows Login has Rights to My Server?

Posted on by David Levy

Introduction

Here is another utility stored procedure that I use. This stored procedure will tell you how a user has access to your server. This is a great stored procedure to use for things like server dashboards where you only want people to see the dashboard for servers that they have access to without granting them all the rights that would go with the dashboard.

The Script

This stored procedure is quite simple. For a given login name call xp_logininfo, passing in the login name, piping the results into a temporary table. Once the table is built check to see if it has any rows and whether or not we should raise an exception based on the @hide_exceptions flag. If yes then raise an exception else just move along. Lastly, return any information returned about the login to the caller.

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
CREATE PROCEDURE dbo.sp_dba_logininfo @loginame nvarchar(128), @hide_exceptions bit = 0

AS

 BEGIN

    CREATE TABLE #Logins
    (
        account_name        sysname NULL,
        type                char(8) NULL,
        privilege           char(9) NULL,
        mapped_login_name   sysname NULL,
        permission_path     sysname NULL
    )

        INSERT #Logins
            EXEC xp_logininfo @loginame

    IF (SELECT COUNT(*) FROM #Logins) = 0 AND @hide_exceptions = 0
     BEGIN
        RAISERROR('Specified user does not have access to this server.', 14, 1)
     END

    SELECT  account_name,
            type,
            privilege,
            mapped_login_name,
            permission_path
    FROM    #Logins

    DROP TABLE #Logins
 
 END

GO

Conclusion

As usual, I hope you find this stored procedure helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

Using sp_ExecuteSql to Run Dynamic SQL

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

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

Lately it seems like I am being bombarded by Dynamic SQL and people who insist it is the only way to accomplish something. Dynamic SQL is almost never the best way to accomplish a given task, but sometimes (and I do mean just sometimes) it is. In the post I will show why to use sp_ExecuteSql and how to convert an existing procedure to use it properly.

The Example

Below is an example stored procedure that I came up with that takes multiple parameters and based on those parameters formulates and executes a query. It is intentionally easy and could well be written otherwise but it works well for this purpose.

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
CREATE PROCEDURE dbo.GetTableInfo @object_id int = null, @name sysname = null
AS
DECLARE @sql_command nvarchar(max)

SELECT @sql_command = N'
SELECT [name],
[object_id]
FROM sys.tables
'

IF @object_id IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [object_id] = ' + CAST(@object_id AS nvarchar)
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'
AND [name] = ''' + @name + ''''
END
END
ELSE
BEGIN
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [name] = ''' + @name + ''''
END
ELSE
BEGIN
SELECT @sql_command = N''
END
END

PRINT @sql_command
EXEC sp_executesql @sql_command

The stored procedure takes 2 parameters, @object_id and/or @name. Based on the inputs a WHERE clause is created and appended to the rest of the SQL statement. In case anyone tries to pull a fast one to see a full table list by submitting null for both arguments a check has been added to clear the string, causing nothing to be returned. At this point, the example looks fairly robust and somewhat bulletproof.

The Test(s)

Testing the stored procedure should be fairly simple. To keep things easy I will test in msdb by searching for the table sysjobs by name, object_id and then both. I will then test for the possibility of SQL Injection.

Test #1

EXEC dbo.GetTableInfo @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = ‘sysjobs’

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #2

EXEC dbo.GetTableInfo @object_id = 277576027

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = 277576027

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #3

EXEC dbo.GetTableInfo @object_id = 277576027, @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = 277576027
AND [name] = ‘sysjobs’

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #4 – SQL Injection

EXEC dbo.GetTableInfo @name = ‘sysjobs” OR ”1”=”1”; SELECT name FROM sys.server_principals WHERE name != ”’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = ‘sysjobs’ OR ‘1’=’1′; SELECT name FROM sys.server_principals WHERE name != ”

name object_id
—————– —————
syssubsystems 5575058
sysproxysubsystem 21575115
restorefilegroup 30623152
.
.
.

(97 row(s) affected)

name
——————————-
sa
public
sysadmin
.
.
.

(33 row(s) affected)

The Problem

Testing looked great until the SQL Injection test. By mangling the parameters passed into the stored procedure I was able to get it to dump a list of all logins on the server. There is no reason that a call to a stored procedure could not be added in there instead followed by “–” to comment out the rest of the string. If xp_cmdshell were unsecured on this server the box would now belong to anyone that could exploit this vulnerability. This is about as bad as it gets short of leaving the sa password blank.

The Fix

In the example above I made a key error to prove a point. Even though I used sp_ExecuteSql, I used it in a way that was no better than using the EXEC statement. The proper way to use it is to build a parameterized SQL statement and pass the parameters into sp_ExecuteSql seperately. I know this sounds like a lot of extra work but an important thing to note is that all of the parameters can be passed for every call and will only used if they are in the SQL string that is passed in.

The added benefit of parameterized SQL is the elimination of procedure cache bloat. The example above creates a unique query string that will cause a plan to be added to the procedure cache every time the procedure is called unless the same exact parameters are passed. The fixed or improved version will add 3 plans to the procedure cache, slightly more if there are wide variations in the data but far less than the 1 for every call the example will add.

Here is the fixed version:

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
CREATE PROCEDURE dbo.GetTableInfo @object_id int = null, @name sysname = null
AS
DECLARE @sql_command nvarchar(max),
@sql_parameters nvarchar(max)

SELECT @sql_command = N'
SELECT [name],
[object_id]
FROM sys.tables
',
@sql_parameters = N'@object_id int, @name sysname'

IF @object_id IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [object_id] = @object_id'
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'
AND [name] = @name'
END
END
ELSE
BEGIN
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [name] = @name'
END
ELSE
BEGIN
SELECT @sql_command = N''
END
END

PRINT @sql_command
EXEC sp_executesql @sql_command, @sql_parameters, @object_id = @object_id, @name = @name

GO

The Re-Test(s)

Testing the stored procedure should be fairly simple. To keep things easy I will test in msdb by searching for the table sysjobs by name, object_id and then both. I will then test for the possibility of SQL Injection.

Test #1

EXEC dbo.GetTableInfo @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = @name

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #2

EXEC dbo.GetTableInfo @object_id = 277576027

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = @object_id

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #3

EXEC dbo.GetTableInfo @object_id = 277576027, @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = @object_id
AND [name] = @name

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #4 – SQL Injection

EXEC dbo.GetTableInfo @name = ‘sysjobs” OR ”1”=”1”; SELECT name FROM sys.server_principals WHERE name != ”’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = @name

name object_id
—————– —————

(0 row(s) affected)

Conclusion

Dynamic SQL should be considered a highly specialized tool of last resort and used in the way I have described. As I have shown, misuse can lead to your server and the data stored on it no longer belonging to you.

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.