Last month I posted my stored procedures sp_SrvPermissions and sp_DBPermissions. I’m posting V2.0 of each with a few fixes. The fixes are listed at the bottom of the comments block.
sp_DBPermissions is a stored procedure that will output 3 record sets containing information on the database level principals, what roles they belong to and what explicit permissions they have.
IF OBJECT_ID('dbo.sp_DBPermissions') IS NULL
EXEC sp_executesql N'CREATE PROCEDURE dbo.sp_DBPermissions AS PRINT ''Stub'';'
GO
/*********************************************************************************************
sp_DBPermissions V2.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.
-- V2.0
-- 8/18/2013 – Create a stub if the SP doesn’t exist, then always do an alter
-- 8/18/2013 - Use instance collation for all concatenated strings
-- 9/04/2013 - dbo can’t be added or removed from roles. Don’t script.
-- 9/04/2013 - Fix scripts for schema level permissions
-- 9/04/2013 – Change print option to show values of variables not the
-- Variable names.
*********************************************************************************************/ALTER PROCEDURE dbo.sp_DBPermissions
(
@DBName sysname = NULL,
@Principal sysname = NULL,
@Role sysname = NULL,
@Print bit = 0
)
AS
DECLARE @Collation nvarchar(50)
SET @Collation = ' COLLATE ' + CAST(SERVERPROPERTY('Collation') AS nvarchar(50))
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' + @Collation + ') + '';'' 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' + @Collation + ') END + ' + CHAR(13) +
'CASE WHEN DBPrincipals.[type] = ''R'' THEN ' + CHAR(13) +
'ISNULL('' AUTHORIZATION ''+QUOTENAME(Authorizations.name' + @Collation + '),'''') ' + 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' + @Collation + '),'' WITHOUT LOGIN'') + ' + CHAR(13) +
'ISNULL('' WITH DEFAULT_SCHEMA = ''+QUOTENAME(DBPrincipals.default_schema_name' + @Collation + '),'''') ' + 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
IF @Print = 1
SET @sql = @sql + CHAR(13) + 'WHERE DBPrincipals.name LIKE ' + ISNULL(+QUOTENAME(@Principal,''''),QUOTENAME(@Role,''''))
ELSE
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' + @Collation +
','''''''')+'',''+QUOTENAME(CASE WHEN Users.name = ''dbo'' THEN NULL ELSE Users.name END' + @Collation +
','''''''')+'';'' AS Drop_Script, ' + CHAR(13) +
'''EXEC sp_addrolemember ''+QUOTENAME(Roles.name' + @Collation +
','''''''')+'',''+QUOTENAME(CASE WHEN Users.name = ''dbo'' THEN NULL ELSE Users.name END' + @Collation +
','''''''')+'';'' 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
IF @Print = 1
SET @sql = @sql + CHAR(13) + ' AND Users.name LIKE '+QUOTENAME(@Principal,'''')
ELSE
SET @sql = @sql + CHAR(13) + ' AND Users.name LIKE @Principal'
IF LEN(ISNULL(@Role,'')) > 0
IF @Print = 1
SET @sql = @sql + CHAR(13) + ' AND Roles.name LIKE '+QUOTENAME(@Role,'''')
ELSE
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, Schemas.name AS SchemaName, Permission.state_desc, ' + CHAR(13) +
'''REVOKE '' + ' + CHAR(13) +
'CASE WHEN Permission.[state] = ''W'' THEN ''GRANT OPTION FOR '' ELSE '''' END + ' + CHAR(13) +
''' '' + Permission.permission_name' + @Collation + ' + ' + CHAR(13) +
'CASE WHEN Permission.major_id <> 0 THEN '' ON '' + ' + CHAR(13) +
'ISNULL(QUOTENAME([Objects].name), ''SCHEMA::''+QUOTENAME(Schemas.name))' + @Collation + ' + '' '' ELSE '''' END + ' + CHAR(13) +
''' FROM '' + QUOTENAME(Grantee.name' + @Collation + ') + ''; '' AS Revoke_Statement, ' + CHAR(13) +
'CASE WHEN Permission.[state] = ''W'' THEN ''GRANT'' ELSE Permission.state_desc' + @Collation + ' END + ' + CHAR(13) +
''' '' + Permission.permission_name' + @Collation + ' + ' + CHAR(13) +
'CASE WHEN Permission.major_id <> 0 THEN '' ON '' + ' + CHAR(13) +
'ISNULL(QUOTENAME([Objects].name), ''SCHEMA::''+QUOTENAME(Schemas.name))' + @Collation + ' + '' '' ELSE '''' END + ' + CHAR(13) +
''' TO '' + QUOTENAME(Grantee.name' + @Collation + ') + '' '' + ' + CHAR(13) +
'CASE WHEN Permission.[state] = ''W'' THEN '' WITH GRANT OPTION '' ELSE '''' END + ' + CHAR(13) +
''' AS ''+ QUOTENAME(Grantor.name' + @Collation + ')+'';'' 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 ' + CHAR(13) +
'LEFT OUTER JOIN sys.schemas Schemas ' + CHAR(13) +
'ON Permission.major_id = Schemas.schema_id ' + CHAR(13) +
'AND Permission.class_desc = ''SCHEMA'' '
IF LEN(ISNULL(@Principal,@Role)) > 0
IF @Print = 1
SET @sql = @sql + CHAR(13) + 'WHERE Grantee.name LIKE ' + ISNULL(+QUOTENAME(@Principal,''''),QUOTENAME(@Role,''''))
ELSE
SET @sql = @sql + CHAR(13) + 'WHERE Grantee.name LIKE ISNULL(@Principal,@Role) '
IF @Print = 1
PRINT '-- Database & object Permissions' + CHAR(13) + @sql
ELSE
EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname', @Principal, @Role
GOFiled under: Dynamic SQL, Microsoft SQL Server, Security, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: language sql, microsoft sql server, security, system functions, T-SQL 
