Last week I posted a series of database permissions scripts. I’ve been using them quite a bit over the last week and decided they were too unwieldy the way they were. I had to copy over each script, put in each parameter and then run it. Not all that much work, but more than it needed to be. So I decided to combine them together into a single stored procedure sp_DBPermissions. Because it starts with sp_, if created in the master database it can then be called from any database. The majority of the details on how to run it are in the comments of the stored procedure itself, but to give a quick overview, by passing in a database name it will return security information on any database. The information can be narrowed down by passing in a principal and/or role name.

Of course any comments/bugs/suggestions for improvements you wish to make are more than welcome.

/*********************************************************************************************
sp_DBPermissions V1.0
Kenneth Fisher
http://www.sqlstudies.com
This stored procedure returns 3 data sets.  The first dataset is the list of database 
principals, the second is role membership, and the third is object and database level 
permissions.
The final 2 columns of each query are "Un-Do"/"Do" scripts.  For example removing a member
from a role or adding them to a role.  I am fairly confident in the role scripts, however, 
the scripts in the database principals query and database/object permissions query are 
works in progress.  In particular certificates, keys and column level permissions are not 
scripted out.  Also while the scripts have worked flawlessly on the systems I've tested 
them on, these systems are fairly similar when it comes to security so I can't say that 
in a more complicated system there won't be the odd bug.
Standard disclaimer: You use scripts off of the web at your own risk.  I fully expect this
     script to work without issue but I've been known to be wrong before.
Parameters:
@DBName
If NULL use the current database, otherwise give permissions based on the parameter.
@Principal
If NOT NULL then all three queries only pull for that database principal.  @Principal
is a pattern check.  The queries check for any row where the passed in value exists.
It uses the pattern '%' + @Principal + '%'
@Role
If NOT NULL then the roles query will pull members of the role.  If it is NOT NULL and
@DBName is NULL then DB principal and permissions query will pull the principal row for 
the role and the permissions for the role.  @Role is a pattern check.  The queries 
check for any row where the passed in value exists.  It uses the pattern '%' + @Role +
'%'
@Print
Defaults to 0, but if a 1 is passed in then the queries are not run but printed
out instead.  This is primarily for debugging.

*********************************************************************************************/ALTER PROCEDURE dbo.sp_DBPermissions 
(
@DBName sysname = NULL, 
@Principal sysname = NULL, 
@Role sysname = NULL, 
@Print bit = 0
)
AS
DECLARE @sql nvarchar(max)
DECLARE @use nvarchar(500)
IF @DBName IS NULL
BEGIN
SET @use = ''
SELECT @DBName = db_name(database_id) FROM sys.dm_exec_requests WHERE session_id = @@SPID
END
ELSE
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DBName)
SET @use = 'USE ' + QUOTENAME(@DBName) + ';' + CHAR(13)
ELSE
BEGIN
RAISERROR (N'%s is not a valid database name.',
                            16,
                            1,
                            @DBName)
RETURN
END 
IF LEN(ISNULL(@Principal,'')) > 0
SET @Principal = '%' + @Principal + '%'

IF LEN(ISNULL(@Role,'')) > 0
SET @Role = '%' + @Role+ '%'
--=========================================================================
-- Database Principals
SET @sql = 
@use +
'SELECT ''' + @DBName + ''' AS DBName, DBPrincipals.name AS DBPrincipal, SrvPrincipals.name AS SrvPrincipal, DBPrincipals.sid, ' + CHAR(13) + 
'DBPrincipals.type, DBPrincipals.type_desc, DBPrincipals.default_schema_name, DBPrincipals.create_date, ' + CHAR(13) + 
'DBPrincipals.modify_date, DBPrincipals.is_fixed_role, Authorizations.name AS Role_Authorization, ' + CHAR(13) + 
'CASE WHEN DBPrincipals.is_fixed_role = 0 THEN ' + CHAR(13) + 
'''DROP '' + CASE DBPrincipals.[type] WHEN ''C'' THEN NULL ' + CHAR(13) + 
'WHEN ''K'' THEN NULL ' + CHAR(13) + 
'WHEN ''R'' THEN ''ROLE'' ' + CHAR(13) + 
'WHEN ''A'' THEN ''APPLICATION ROLE'' ' + CHAR(13) + 
'ELSE ''USER'' END + ' + CHAR(13) + 
''' ''+QUOTENAME(DBPrincipals.name) + '';'' ELSE NULL END AS Drop_Script, ' + CHAR(13) + 
'CASE WHEN DBPrincipals.is_fixed_role = 0 THEN ' + CHAR(13) + 
'''CREATE '' + CASE DBPrincipals.[type] WHEN ''C'' THEN NULL ' + CHAR(13) + 
'WHEN ''K'' THEN NULL ' + CHAR(13) + 
'WHEN ''R'' THEN ''ROLE'' ' + CHAR(13) + 
'WHEN ''A'' THEN ''APPLICATION ROLE'' ' + CHAR(13) + 
'ELSE ''USER'' END + ' + CHAR(13) + 
''' ''+QUOTENAME(DBPrincipals.name) END + ' + CHAR(13) + 
'CASE WHEN DBPrincipals.[type] = ''R'' THEN ' + CHAR(13) + 
'ISNULL('' AUTHORIZATION ''+QUOTENAME(Authorizations.name),'''') ' + CHAR(13) + 
'WHEN DBPrincipals.[type] = ''A'' THEN ' + CHAR(13) + 
'''''  ' + CHAR(13) + 
'WHEN DBPrincipals.[type] NOT IN (''C'',''K'') THEN ' + CHAR(13) + 
'ISNULL('' FOR LOGIN '' + QUOTENAME(SrvPrincipals.name),'' WITHOUT LOGIN'') +  ' + CHAR(13) + 
'ISNULL('' WITH DEFAULT_SCHEMA =  ''+QUOTENAME(DBPrincipals.default_schema_name),'''') ' + CHAR(13) + 
'ELSE '''' ' + CHAR(13) + 
'END + '';'' +  ' + CHAR(13) + 
'CASE WHEN DBPrincipals.[type] NOT IN (''C'',''K'',''R'',''A'') ' + CHAR(13) + 
'AND SrvPrincipals.name IS NULL ' + CHAR(13) + 
'AND DBPrincipals.sid IS NOT NULL ' + CHAR(13) + 
'AND DBPrincipals.sid NOT IN (0x00, 0x01)  ' + CHAR(13) + 
'THEN '' -- Possible missing server principal''  ' + CHAR(13) + 
'ELSE '''' END ' + CHAR(13) + 
'AS Create_Script ' + CHAR(13) + 
'FROM sys.database_principals DBPrincipals ' + CHAR(13) + 
'LEFT OUTER JOIN sys.database_principals Authorizations ' + CHAR(13) + 
'ON DBPrincipals.owning_principal_id = Authorizations.principal_id ' + CHAR(13) + 
'LEFT OUTER JOIN sys.server_principals SrvPrincipals ' + CHAR(13) + 
'ON DBPrincipals.sid = SrvPrincipals.sid ' + CHAR(13) + 
'AND DBPrincipals.sid NOT IN (0x00, 0x01) ' 
IF LEN(ISNULL(@Principal,@Role)) > 0 
SET @sql = @sql + CHAR(13) + 'WHERE DBPrincipals.name LIKE ISNULL(@Principal,@Role) '
IF @Print = 1
PRINT '-- Database Principals' + CHAR(13) + @sql + CHAR(13) + CHAR(13)
ELSE
EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname', @Principal, @Role
--=========================================================================
-- Database Role Members
SET @sql = 
@use + 
'SELECT ''' + @DBName + ''' AS DBName, Users.name AS UserName, Roles.name AS RoleName, ' + CHAR(13) + 
'''EXEC sp_droprolemember ''+QUOTENAME(Roles.name,'''''''')+'',''+QUOTENAME(Users.name,'''''''')+'';'' AS Drop_Script, ' + CHAR(13) + 
'''EXEC sp_addrolemember ''+QUOTENAME(Roles.name,'''''''')+'',''+QUOTENAME(Users.name,'''''''')+'';'' AS Add_Script ' + CHAR(13) + 
'FROM sys.database_role_members RoleMembers ' + CHAR(13) + 
'JOIN sys.database_principals Users ' + CHAR(13) + 
'ON RoleMembers.member_principal_id = Users.principal_id ' + CHAR(13) + 
'JOIN sys.database_principals Roles ' + CHAR(13) + 
'ON RoleMembers.role_principal_id = Roles.principal_id ' + CHAR(13) + 
'WHERE 1=1 '

IF LEN(ISNULL(@Principal,'')) > 0
SET @sql = @sql + CHAR(13) + '  AND Users.name LIKE @Principal'
IF LEN(ISNULL(@Role,'')) > 0
SET @sql = @sql + CHAR(13) + '  AND Roles.name LIKE @Role'
IF @Print = 1
PRINT '-- Database Role Members' + CHAR(13) + @sql + CHAR(13) + CHAR(13)
ELSE
EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname', @Principal, @Role
--=========================================================================
-- Database & object Permissions
SET @sql =
@use + 
'SELECT ''' + @DBName + ''' AS DBName, Grantee.name AS Grantee_Name, Grantor.name AS Grantor_Name, ' + CHAR(13) + 
'Permission.class_desc, Permission.permission_name, ' + CHAR(13) + 
'[Objects].name AS ObjectName, Permission.state_desc,  ' + CHAR(13) + 
'''REVOKE '' + ' + CHAR(13) + 
'CASE WHEN Permission.[state]  = ''W'' THEN ''GRANT OPTION FOR '' ELSE '''' END + ' + CHAR(13) + 
''' '' + Permission.permission_name COLLATE SQL_Latin1_General_CP437_CI_AS +  ' + CHAR(13) + 
'CASE WHEN Permission.major_id <> 0 THEN '' ON '' + QUOTENAME([Objects].name) + '' '' ELSE '''' END + ' + CHAR(13) + 
''' FROM '' + QUOTENAME(Grantee.name)  + ''; '' AS Revoke_Statement, ' + CHAR(13) + 
'CASE WHEN Permission.[state]  = ''W'' THEN ''GRANT'' ELSE Permission.state_desc COLLATE SQL_Latin1_General_CP437_CI_AS  END + ' + CHAR(13) + 
''' '' + Permission.permission_name  +  ' + CHAR(13) + 
'CASE WHEN Permission.major_id <> 0 THEN '' ON '' + QUOTENAME([Objects].name) + '' '' ELSE '''' END + ' + CHAR(13) + 
''' TO '' + QUOTENAME(Grantee.name)  + '' '' +  ' + CHAR(13) + 
'CASE WHEN Permission.[state]  = ''W'' THEN '' WITH GRANT OPTION '' ELSE '''' END +  ' + CHAR(13) + 
''' AS ''+ QUOTENAME(Grantor.name)+'';'' AS Grant_Statement ' + CHAR(13) + 
'FROM sys.database_permissions Permission ' + CHAR(13) + 
'JOIN sys.database_principals Grantee ' + CHAR(13) + 
'ON Permission.grantee_principal_id = Grantee.principal_id ' + CHAR(13) + 
'JOIN sys.database_principals Grantor ' + CHAR(13) + 
'ON Permission.grantor_principal_id = Grantor.principal_id ' + CHAR(13) + 
'LEFT OUTER JOIN sys.all_objects [Objects] ' + CHAR(13) + 
'ON Permission.major_id = [Objects].object_id '
IF LEN(ISNULL(@Principal,@Role)) > 0
SET @sql = @sql + CHAR(13) + 'WHERE Grantee.name LIKE ISNULL(@Principal,@Role) '
IF @Print = 1
PRINT '-- Database & object Permissions' + CHAR(13) + @sql + CHAR(13) + CHAR(13)
ELSE
EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname', @Principal, @Role
GO

Filed under: Dynamic SQL, Microsoft SQL Server, Security, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: database permissions, dynamic sql, language sql, microsoft sql server, security, system functions