Last week I posted my stored procedure for database permissions, sp_DBPermissions. A couple of days later I was demonstrating it to one of my co-workers and his response was “Where’s the server version?” Unable to come up with an appropriate rude response I’m posting the server version this week, sp_SrvPermissions.

It has the same basic setup. It returns three data sets, a list of principals, a list of role membership, and a list of server level permissions. The information can be narrowed down by passing in a principal and/or role name. Additional details are in the comments of the SP itself. If you create the stored procedure in master you will be able to call it from anywhere on the instance because the name begins with sp_.

/*********************************************************************************************
sp_SrvPermissions V1.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.
     
*********************************************************************************************/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 
    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
    SET @sql = @sql + CHAR(13) + '  AND Logins.name LIKE @Principal'
 
IF LEN(ISNULL(@Role,'')) > 0
    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
    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 Tagged: code language, dynamic sql, language sql, microsoft sql server, security, T-SQL