Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

sp_DBPermissions V2.0

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
GO

Filed 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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...