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_SrvPermissions 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_SrvPermissions is a stored procedure that will output 3 record sets containing information on the server level principals, what roles they belong to and what explicit permissions they have.

USE master
GO
IF OBJECT_ID('dbo.sp_SrvPermissions') IS NULL
	EXEC sp_executesql N'CREATE PROCEDURE dbo.sp_SrvPermissions AS PRINT ''Stub'';'
GO
/*********************************************************************************************
sp_SrvPermissions V2.0
Kenneth Fisher

http://www.sqlstudies.com

This stored procedure returns 3 data sets.  The first dataset is the list of server
principals, the second is role membership, and the third is server 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 server principals query and server permissions query are works in 
progress.  In particular certificates and keys 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.

Notes on the create script for server principals:
1)	I have included a hashed version of the password and the sid.  This means that when run
	on another server the password and the sid will remain the same.  
2)	In SQL 2005 the create script on the server principals query DOES NOT WORK.  This is 
	because the conversion of the sid (in varbinary) to character doesn't appear to work 
	as I expected in SQL 2005.  It works fine in SQL 2008 and above.  If you want to use
	this script in SQL 2005 you can change the CONVERTs in the principal script to 
	master.sys.fn_varbintohexstr

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:
    @Principal
        If NOT NULL then all three queries only pull for that server 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
        @Principal is NULL then Server 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
-- 9/04/2013 – Change print option to show values of variables not the 
--             Variable names.
*********************************************************************************************/
ALTER PROCEDURE dbo.sp_SrvPermissions 
(
@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)
 
IF LEN(ISNULL(@Principal,'')) > 0
    SET @Principal = '%' + @Principal + '%'
     
IF LEN(ISNULL(@Role,'')) > 0
    SET @Role = '%' + @Role+ '%'
 
--=========================================================================
-- Server Principals
SET @sql = 
	'SELECT name AS SrvPrincipal, sid, type, type_desc, is_disabled, default_database_name, default_language_name, ' + CHAR(13) + 
	'	CASE WHEN principal_id < 100 THEN NULL ELSE ' + CHAR(13) + 
	'			''DROP '' + CASE [type] WHEN ''C'' THEN NULL ' + CHAR(13) + 
	'				WHEN ''K'' THEN NULL ' + CHAR(13) + 
	'				WHEN ''R'' THEN ''ROLE'' ' + CHAR(13) + 
	'				ELSE ''LOGIN'' END + ' + CHAR(13) + 
	'			'' ''+QUOTENAME(name' + @Collation + ') END + '';'' AS Drop_Script, ' + CHAR(13) + 
	'	CASE WHEN principal_id < 100 THEN NULL ELSE ' + CHAR(13) + 
	'			''CREATE '' + CASE [type] WHEN ''C'' THEN NULL ' + CHAR(13) + 
	'				WHEN ''K'' THEN NULL ' + CHAR(13) + 
	'				WHEN ''R'' THEN ''ROLE'' ' + CHAR(13) + 
	'				ELSE ''LOGIN'' END + ' + CHAR(13) + 
	'			'' ''+QUOTENAME(name' + @Collation + ') END + ' + CHAR(13) + 
	'			CASE WHEN [type] = (''S'') THEN ' + CHAR(13) + 
	'			'' WITH PASSWORD = '' + ' + CHAR(13) + 
	'			CONVERT(varchar(256), LOGINPROPERTY(name, ''PasswordHash''),1 ) + '' HASHED,'' +  ' + CHAR(13) + 
	'				'' SID = '' + CONVERT(varchar(85), sid, 1) +  ' + CHAR(13) + 
	'				CASE WHEN default_database_name IS NOT NULL OR default_language_name IS NOT NULL THEN '','' ELSE '''' END ' + CHAR(13) + 
	'			WHEN [type] IN (''U'',''G'') THEN '' FROM WINDOWS '' + ' + CHAR(13) + 
	'			CASE WHEN default_database_name IS NOT NULL OR default_language_name IS NOT NULL THEN '' WITH '' ELSE '''' END ' + CHAR(13) + 
	'			ELSE '''' END + ' + CHAR(13) + 
	'			ISNULL('' DEFAULT_DATABASE = '' + QUOTENAME(default_database_name' + @Collation + '), '''') + ' + CHAR(13) + 
	'			CASE WHEN default_database_name IS NOT NULL AND default_language_name IS NOT NULL THEN '','' ELSE '''' END + ' + CHAR(13) + 
	'			ISNULL('' DEFAULT_LANGUAGE = '' + QUOTENAME(default_language_name' + @Collation + '), '''') + ' + CHAR(13) + 
	'			'';'' ' + CHAR(13) + 
	'		AS Create_Script ' + CHAR(13) + 
	'FROM sys.server_principals Logins '

IF LEN(ISNULL(@Principal,@Role)) > 0 
	IF @Print = 1
		SET @sql = @sql + CHAR(13) + 'WHERE Logins.name LIKE ' + ISNULL(+QUOTENAME(@Principal,''''),QUOTENAME(@Role,'''')) 
	ELSE
		SET @sql = @sql + CHAR(13) + 'WHERE Logins.name LIKE ISNULL(@Principal,@Role) '

IF @Print = 1
    PRINT '-- Server Principals' + CHAR(13) + @sql + CHAR(13) + CHAR(13)
ELSE
    EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname', @Principal, @Role
 
--=========================================================================
-- Server level roles
SET @sql = 
	'SELECT Logins.name AS UserName, Roles.name AS RoleName, ' + CHAR(13) + 
	'	''EXEC sp_dropsrvrolemember ''+QUOTENAME(Roles.name' + @Collation + 
			','''''''')+'',''+QUOTENAME(Logins.name' + @Collation + 
			','''''''') + '';'', ' + CHAR(13) + 
	'	''EXEC sp_addsrvrolemember ''+QUOTENAME(Roles.name' + @Collation + 
			','''''''')+'',''+QUOTENAME(Logins.name' + @Collation + 
			','''''''') + '';'' ' + CHAR(13) + 
	'FROM sys.server_role_members RoleMembers ' + CHAR(13) + 
	'JOIN sys.server_principals Logins ' + CHAR(13) + 
	'	ON RoleMembers.member_principal_id = Logins.principal_id ' + CHAR(13) + 
	'JOIN sys.server_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 Logins.name LIKE '+QUOTENAME(@Principal,'''')
	ELSE
		SET @sql = @sql + CHAR(13) + '  AND Logins.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 '-- Server 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 =
	'SELECT Grantee.name AS Grantee_Name, Grantor.name AS Grantor_Name, ' + CHAR(13) + 
	'	Permission.class_desc, Permission.permission_name, ' + CHAR(13) + 
	'	Permission.state_desc,  ' + CHAR(13) + 
	'	''REVOKE '' + ' + CHAR(13) + 
	'		CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL ' + CHAR(13) + 
	'		WHEN Permission.[state]  = ''W'' THEN ''GRANT OPTION FOR '' ELSE '''' END + ' + CHAR(13) + 
	'		'' '' + Permission.permission_name' + @Collation + ' +  ' + CHAR(13) + 
	'		'' FROM '' + QUOTENAME(Grantee.name' + @Collation + ')  + ''; '' AS Revoke_Statement, ' + CHAR(13) + 
	'	CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL ' + CHAR(13) + 
	'		WHEN Permission.[state]  = ''W'' THEN ''GRANT'' ELSE Permission.state_desc' + @Collation + ' END + ' + CHAR(13) + 
	'		'' '' + Permission.permission_name' + @Collation + ' +  ' + 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.server_permissions Permission ' + CHAR(13) + 
	'JOIN sys.server_principals Grantee ' + CHAR(13) + 
	'	ON Permission.grantee_principal_id = Grantee.principal_id ' + CHAR(13) + 
	'JOIN sys.server_principals Grantor ' + CHAR(13) + 
	'	ON Permission.grantor_principal_id = Grantor.principal_id ' 


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 '-- Server 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: 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...