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

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

Comments

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

Loading comments...