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
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 
