sp_SrvPermissions

9
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 V6.1
Kenneth Fisher
http://www.sqlstudies.com
https://github.com/sqlstudent144/SQL-Server-Scripts/blob/master/sp_SrvPermissions.sql
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 + '%'
@Type
If NOT NULL then all three queries will only pull principals of that type.
S = SQL login
U = Windows login
G = Windows group
R = Server role
C = Login mapped to a certificate
K = Login mapped to an asymmetric key
@DBName
If NOT NULL then only return those principals and information about them where the
principal exists within the DB specified.
@UseLikeSearch
When this is set to 1 (the default) then the search parameters will use LIKE (and
%'s will be added around the @Principal and @Role parameters).
When set to 0 searchs will use =.
@IncludeMSShipped
When this is set to 1 (the default) then all principals will be included. When set
to 0 the fixed server roles and SA and Public principals will be excluded.
@DropTempTables
When this is set to 1 (the default) the temp tables used are dropped. If it's 0
then the tempt ables are kept for references after the code has finished.
The temp tables are:
##SrvPrincipals
##SrvRoles
##SrvPermissions
@Output
What type of output is desired.
Default – Either 'Default' or it doesn't match any of the allowed values then the SP
will return the standard 3 outputs.
None – No output at all. Usually used if you keeping the temp tables to do your own
reporting.
CreateOnly – Only return the create scripts where they aren't NULL.
DropOnly – Only return the drop scripts where they aren't NULL.
ScriptsOnly – Return drop and create scripts where they aren't NULL.
Report – Returns one output with one row per principal and a comma delimited list of
roles the principal is a member of and a comma delimited list of the
individual permissions they have.
@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.
Data is ordered as follows
1st result set: SrvPrincipal
2nd result set: RoleName, LoginName if the parameter @Role is used else
LoginName, RoleName
3rd result set: GranteeName
*********************************************************************************************
— 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.
— V3.0
— 10/5/2013 – Added @Type parameter to pull only principals of a given type.
— 10/20/2013 – Remove SID in CREATE LOGIN script from v2005 and lower since it requires
a special function to convert from binary to varchar.
— V4.0
— 11/18/2013 – Corrected bug in the order of the parameters for sp_addsrvrolemember
and sp_dropsrvrolemember, also added parameter names both.
— 01/09/2014 – Added an ORDER BY to each of the result sets. See above for details.
— V5.0
— 04/27/2014 – Add @DBName parameter
— V5.5
— 7/22/2014 – Changed strings to unicode
— V6.0
— 10/19/2014 – Add @UserLikeSearch and @IncludeMSShipped parameters.
— 03/25/2017 – Move SID towards the end of the first output so the more important
— columns are closer to the front.
— 03/25/2017 – Add IF Exists to drop and create principal scripts
— 03/25/2017 – Add @DropTempTables to keep the temp tables after the SP is run.
— 03/26/2017 – Add @Output to allow different types of output.
— V6.1
— 06/13/2018 – Removed scripts for principal IDs under 100 (anecdotally the system IDs)
— – Added SET NOCOUNT ON
— 05/28/2019 – Add scripts & mappings for certificate & asymmetric key mapped principals.
— – Start cleaning up the dynamic SQL a bit to make it easier to read.
— – Fix SERVER ROLE scripts
— – Add CHECK_POLICY and CHECK_EXPIRATION
— – Add script support for disabled
— – Add script support for a single credential. Will not support multiple credentials.
*********************************************************************************************/
ALTER PROCEDURE dbo.sp_SrvPermissions
(
@Principal sysname = NULL,
@Role sysname = NULL,
@Type nvarchar(30) = NULL,
@DBName sysname = NULL,
@UseLikeSearch bit = 1,
@IncludeMSShipped bit = 1,
@DropTempTables bit = 1,
@Output varchar(30) = 'Default',
@Print bit = 0
)
AS
SET NOCOUNT ON
IF @DBName IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @DBName)
BEGIN
RAISERROR (N'%s is not a valid database name.',
16,
1,
@DBName)
RETURN
END
DECLARE @Collation nvarchar(50)
SET @Collation = N' COLLATE ' + CAST(SERVERPROPERTY('Collation') AS nvarchar(50))
DECLARE @Version2005orLower bit
SELECT @Version2005orLower = CASE WHEN PARSENAME(CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)),4) < 10 THEN 1
ELSE 0 END
DECLARE @sql nvarchar(max)
DECLARE @LikeOperator nvarchar(4)
IF @UseLikeSearch = 1
SET @LikeOperator = N'LIKE'
ELSE
SET @LikeOperator = N'='
IF @UseLikeSearch = 1
BEGIN
IF LEN(ISNULL(@Principal,'')) > 0
SET @Principal = N'%' + @Principal + N'%'
IF LEN(ISNULL(@Role,'')) > 0
SET @Role = N'%' + @Role+ N'%'
END
=========================================================================
Server Principals
SET @sql =
N'SELECT Logins.principal_id AS SrvPrincipalId, Logins.name AS SrvPrincipal, Logins.type, Logins.type_desc,
Logins.is_disabled, Logins.default_database_name, Logins.default_language_name,
CASE sql_logins.is_policy_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' END AS check_policy,
CASE sql_logins.is_expiration_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' END AS check_expiration,
ISNULL(Cert.name,aKey.name) AS Cert_or_asymmetric_key,
Logins.sid,
CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
''IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '' + QuoteName(Logins.name,'''''''') + '') '' +
''DROP '' + CASE Logins.type
WHEN ''R'' THEN ''SERVER ROLE''
ELSE ''LOGIN'' END +
'' ''+QUOTENAME(Logins.name' + @Collation + ') END + '';'' AS DropScript,
CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
''IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = '' + QuoteName(Logins.name,'''''''') + '') '' +
''CREATE '' + CASE Logins.type
WHEN ''R'' THEN ''SERVER ROLE''
ELSE ''LOGIN'' END +
'' ''+QUOTENAME(Logins.name' + @Collation + ') END +
CASE WHEN Logins.type = (''S'') THEN '' WITH PASSWORD = '' +
CONVERT(varchar(256), LOGINPROPERTY(Logins.name, ''PasswordHash''),1 ) + '' HASHED' +
CASE WHEN @Version2005orLower = 0 THEN N','' +
'' SID = '' + CONVERT(varchar(85), Logins.sid, 1) '
ELSE N''' + ' END + '
WHEN Logins.type IN (''U'',''G'') THEN '' FROM WINDOWS ''
WHEN Logins.type = ''C'' THEN ISNULL('' FROM CERTIFICATE '' + QUOTENAME(Cert.name),'''')
WHEN Logins.type = ''K'' THEN ISNULL('' FROM ASYMMETRIC KEY '' + QUOTENAME(aKey.name),'''')
ELSE '''' END +
CASE WHEN Logins.type IN (''S'',''U'',''G'') THEN — Note: Types, S, U and G are the only ones that have additional options.
CASE WHEN Logins.default_database_name IS NOT NULL OR Logins.default_language_name IS NOT NULL THEN
CASE WHEN Logins.Type = ''S'' THEN '','' ELSE '' WITH '' END
ELSE '''' END +
ISNULL('' DEFAULT_DATABASE = '' + QUOTENAME(Logins.default_database_name' + @Collation + N'), '''') +
CASE WHEN Logins.default_database_name IS NOT NULL AND Logins.default_language_name IS NOT NULL THEN '','' ELSE '''' END +
ISNULL('' DEFAULT_LANGUAGE = '' + QUOTENAME(Logins.default_language_name' + @Collation + N'), '''') +
CASE WHEN Logins.type = ''S'' THEN
ISNULL('', CHECK_EXPIRATION = '' + CASE WHEN sql_logins.is_expiration_checked = 1 THEN ''ON'' ELSE ''OFF'' END, '''') +
ISNULL('', CHECK_POLICY = '' + CASE WHEN sql_logins.is_policy_checked = 1 THEN ''ON'' ELSE ''OFF'' END, '''') +
ISNULL('', CREDENTIAL = '' + QUOTENAME(Creds.name), '''')
ELSE '''' END
ELSE '''' END +
''; '' +
CASE WHEN Logins.is_disabled = 1 THEN ''ALTER LOGIN '' + QUOTENAME(Logins.name) + '' DISABLE; '' ELSE '''' END
AS CreateScript
FROM sys.server_principals Logins
LEFT OUTER JOIN sys.certificates Cert
ON Logins.sid = Cert.sid
LEFT OUTER JOIN sys.asymmetric_keys aKey
ON Logins.sid = aKey.sid
LEFT OUTER JOIN sys.sql_logins
ON Logins.sid = sql_logins.sid
LEFT OUTER JOIN sys.server_principal_credentials LoginCreds
ON Logins.principal_id = LoginCreds.principal_id
LEFT OUTER JOIN sys.credentials Creds
ON LoginCreds.credential_id = Creds.credential_id
WHERE 1=1 '
IF LEN(ISNULL(@Principal,@Role)) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' ' + ISNULL(+QUOTENAME(@Principal,''''),QUOTENAME(@Role,''''))
ELSE
SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' ISNULL(@Principal,@Role) '
IF LEN(@Type) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
ELSE
SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' @Type'
IF @DBName IS NOT NULL
SET @sql = @sql + NCHAR(13) + N' AND Logins.SID IN (SELECT SID FROM [' + @DBName + N'].sys.database_principals
WHERE type IN (''G'',''S'',''U'',''K'',''C''))'
IF @IncludeMSShipped = 0
SET @sql = @sql + NCHAR(13) + N' AND Logins.is_fixed_role = 0 ' + NCHAR(13) +
' AND Logins.name NOT IN (''sa'',''public'') '
IF @Print = 1
PRINT '— Server Principals' + NCHAR(13) + @sql + NCHAR(13) + NCHAR(13)
ELSE
BEGIN
IF object_id('tempdb..##SrvPrincipals') IS NOT NULL
DROP TABLE ##SrvPrincipals
Create temp table to store the data in
CREATE TABLE ##SrvPrincipals (
SrvPrincipalId int NULL,
SrvPrincipal sysname NULL,
type char(1) NULL,
type_desc nchar(60) NULL,
is_disabled bit NULL,
default_database_name sysname NULL,
default_language_name sysname NULL,
[check_policy] char(3) NULL,
[check_expiration] char(3) NULL,
Cert_or_asymmetric_key sysname NULL,
sid varbinary(85) NULL,
DropScript nvarchar(max) NULL,
CreateScript nvarchar(max) NULL
)
SET @sql = N'INSERT INTO ##SrvPrincipals ' + NCHAR(13) + @sql
EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type varchar(30)', @Principal, @Role, @Type
END
=========================================================================
Server level roles
SET @sql =
N'SELECT Logins.principal_id AS LoginPrincipalId, Logins.name AS LoginName, Roles.name AS RoleName,
CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
''EXEC sp_dropsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation +
','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation +
','''''''') + '';'' END AS DropScript,
CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
''EXEC sp_addsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation +
','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation +
','''''''') + '';'' END AS AddScript
FROM sys.server_role_members RoleMembers
JOIN sys.server_principals Logins
ON RoleMembers.member_principal_id = Logins.principal_id
JOIN sys.server_principals Roles
ON RoleMembers.role_principal_id = Roles.principal_id
WHERE 1=1 '
IF LEN(ISNULL(@Principal,'')) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' '+QUOTENAME(@Principal,'''')
ELSE
SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' @Principal'
IF LEN(ISNULL(@Role,'')) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N' AND Roles.name ' + @LikeOperator + N' '+QUOTENAME(@Role,'''')
ELSE
SET @sql = @sql + NCHAR(13) + N' AND Roles.name ' + @LikeOperator + N' @Role'
IF LEN(@Type) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
ELSE
SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' @Type'
IF @DBName IS NOT NULL
SET @sql = @sql + NCHAR(13) + N' AND Logins.SID IN (SELECT SID FROM [' + @DBName + N'].sys.database_principals
WHERE type IN (''G'',''S'',''U'',''K'',''C''))'
IF @IncludeMSShipped = 0
SET @sql = @sql + NCHAR(13) + N' AND Logins.is_fixed_role = 0 ' + NCHAR(13) +
' AND Logins.name NOT IN (''sa'',''public'') '
IF @Print = 1
PRINT '— Server Role Members' + NCHAR(13) + @sql + NCHAR(13) + NCHAR(13)
ELSE
BEGIN
IF object_id('tempdb..##SrvRoles') IS NOT NULL
DROP TABLE ##SrvRoles
Create temp table to store the data in
CREATE TABLE ##SrvRoles (
LoginPrincipalId int NULL,
LoginName sysname NULL,
RoleName sysname NULL,
DropScript nvarchar(max) NULL,
AddScript nvarchar(max) NULL
)
SET @sql = 'INSERT INTO ##SrvRoles ' + NCHAR(13) + @sql
EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type nvarchar(30)', @Principal, @Role, @Type
END
=========================================================================
Server Permissions
SET @sql =
N'SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName,
Grantor.name AS GrantorName, Permission.class_desc, Permission.permission_name,
Permission.state_desc,
CASE WHEN Grantee.principal_id < 100 THEN NULL ELSE
''REVOKE '' +
CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL
WHEN Permission.[state] = ''W'' THEN ''GRANT OPTION FOR '' ELSE '''' END +
'' '' + Permission.permission_name' + @Collation + ' +
'' FROM '' + QUOTENAME(Grantee.name' + @Collation + ') + ''; '' END AS RevokeScript,
CASE WHEN Grantee.principal_id < 100 THEN NULL ELSE
CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL
WHEN Permission.[state] = ''W'' THEN ''GRANT'' ELSE Permission.state_desc' + @Collation +
' END +
'' '' + Permission.permission_name' + @Collation + ' +
'' TO '' + QUOTENAME(Grantee.name' + @Collation + ') + '' '' +
CASE WHEN Permission.[state] = ''W'' THEN '' WITH GRANT OPTION '' ELSE '''' END +
'' AS ''+ QUOTENAME(Grantor.name' + @Collation + ') + '';'' END AS GrantScript
FROM sys.server_permissions Permission
JOIN sys.server_principals Grantee
ON Permission.grantee_principal_id = Grantee.principal_id
JOIN sys.server_principals Grantor
ON Permission.grantor_principal_id = Grantor.principal_id
WHERE 1=1 '
IF LEN(ISNULL(@Principal,@Role)) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N' AND Grantee.name ' + @LikeOperator + N' ' + ISNULL(+QUOTENAME(@Principal,''''),QUOTENAME(@Role,''''))
ELSE
SET @sql = @sql + NCHAR(13) + N' AND Grantee.name ' + @LikeOperator + N' ISNULL(@Principal,@Role) '
IF LEN(@Type) > 0
IF @Print = 1
SET @sql = @sql + NCHAR(13) + N' AND Grantee.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
ELSE
SET @sql = @sql + NCHAR(13) + N' AND Grantee.type ' + @LikeOperator + N' @Type'
IF @DBName IS NOT NULL
SET @sql = @sql + NCHAR(13) + N' AND Grantee.SID IN (SELECT SID FROM [' + @DBName + N'].sys.database_principals
WHERE type IN (''G'',''S'',''U'',''K'',''C''))'
IF @IncludeMSShipped = 0
SET @sql = @sql + NCHAR(13) + N' AND Grantee.is_fixed_role = 0 ' + NCHAR(13) +
' AND Grantee.name NOT IN (''sa'',''public'') '
IF @Print = 1
PRINT '— Server Permissions' + NCHAR(13) + @sql + NCHAR(13) + NCHAR(13)
ELSE
BEGIN
IF object_id('tempdb..##SrvPermissions') IS NOT NULL
DROP TABLE ##SrvPermissions
Create temp table to store the data in
CREATE TABLE ##SrvPermissions (
GranteePrincipalId int NULL,
GranteeName sysname NULL,
GrantorName sysname NULL,
class_desc nvarchar(60) NULL,
permission_name nvarchar(128) NULL,
state_desc nvarchar(60) NULL,
RevokeScript nvarchar(max) NULL,
GrantScript nvarchar(max) NULL
)
Add insert statement to @sql
SET @sql = N'INSERT INTO ##SrvPermissions ' + NCHAR(13) + @sql
EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type nvarchar(30)', @Principal, @Role, @Type
END
IF @Print <> 1
BEGIN
IF @Output = 'None'
PRINT ''
ELSE IF @Output = 'CreateOnly'
BEGIN
SELECT CreateScript FROM ##SrvPrincipals WHERE CreateScript IS NOT NULL
UNION ALL
SELECT AddScript FROM ##SrvRoles WHERE AddScript IS NOT NULL
UNION ALL
SELECT GrantScript FROM ##SrvPermissions WHERE GrantScript IS NOT NULL
END
ELSE IF @Output = 'DropOnly'
BEGIN
SELECT DropScript FROM ##SrvPrincipals WHERE DropScript IS NOT NULL
UNION ALL
SELECT DropScript FROM ##SrvRoles WHERE DropScript IS NOT NULL
UNION ALL
SELECT RevokeScript FROM ##SrvPermissions WHERE RevokeScript IS NOT NULL
END
ELSE IF @Output = 'ScriptOnly'
BEGIN
SELECT DropScript, CreateScript FROM ##SrvPrincipals WHERE DropScript IS NOT NULL OR CreateScript IS NOT NULL
UNION ALL
SELECT DropScript, AddScript FROM ##SrvRoles WHERE DropScript IS NOT NULL OR AddScript IS NOT NULL
UNION ALL
SELECT RevokeScript, GrantScript FROM ##SrvPermissions WHERE RevokeScript IS NOT NULL OR GrantScript IS NOT NULL
END
ELSE IF @Output = 'Report'
BEGIN
SELECT SrvPrincipal, type, type_desc, is_disabled,
STUFF((SELECT ', ' + ##SrvRoles.RoleName
FROM ##SrvRoles
WHERE ##SrvPrincipals.SrvPrincipalId = ##SrvRoles.LoginPrincipalId
ORDER BY ##SrvRoles.RoleName
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
, 1, 2, '') AS RoleMembership,
STUFF((SELECT ', ' + ##SrvPermissions.state_desc + ' ' + ##SrvPermissions.permission_name + ' ' +
CASE WHEN class_desc <> 'SERVER' THEN class_desc ELSE '' END
FROM (SELECT DISTINCT * FROM ##SrvPermissions) ##SrvPermissions
WHERE ##SrvPrincipals.SrvPrincipalId = ##SrvPermissions.GranteePrincipalId
ORDER BY ##SrvPermissions.state_desc, ##SrvPermissions.permission_name
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
, 1, 2, '') AS DirectPermissions
FROM ##SrvPrincipals
ORDER BY SrvPrincipal
END
ELSE 'Default' or no match
BEGIN
SELECT SrvPrincipal, type, type_desc, is_disabled, default_database_name,
default_language_name, [check_policy], [check_expiration], Cert_or_asymmetric_key,
sid, DropScript, CreateScript
FROM ##SrvPrincipals ORDER BY SrvPrincipal
IF LEN(@Role) > 0
SELECT LoginName, RoleName, DropScript, AddScript FROM ##SrvRoles ORDER BY RoleName, LoginName
ELSE
SELECT LoginName, RoleName, DropScript, AddScript FROM ##SrvRoles ORDER BY LoginName, RoleName
SELECT GranteeName, GrantorName, class_desc, permission_name, state_desc, RevokeScript, GrantScript
FROM ##SrvPermissions ORDER BY GranteeName
END
IF @DropTempTables = 1
BEGIN
DROP TABLE ##SrvPrincipals
DROP TABLE ##SrvRoles
DROP TABLE ##SrvPermissions
END
END
GO

9 thoughts on “sp_SrvPermissions

  1. brenda grossnickle says:

    The second result set does not have column alias names for Drop_Script, Add_Script. They are shown as (No column Name).

    • brenda grossnickle says:
      -- Server level roles
      SET @sql = 
          N'SELECT Logins.name AS UserName, Roles.name AS RoleName, ' + NCHAR(13) + 
          N'   ''EXEC sp_dropsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation + 
                  ','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation + 
                  ','''''''') + '';'' AS Drop_Script, ' + NCHAR(13) + 
          N'   ''EXEC sp_addsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation + 
                  ','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation + 
                  ','''''''') + '';''  AS Add_Script' + NCHAR(13) + 
          N'FROM sys.server_role_members RoleMembers ' + NCHAR(13) + 
          N'JOIN sys.server_principals Logins ' + NCHAR(13) + 
          N'   ON RoleMembers.member_principal_id = Logins.principal_id ' + NCHAR(13) + 
          N'JOIN sys.server_principals Roles ' + NCHAR(13) + 
          N'   ON RoleMembers.role_principal_id = Roles.principal_id ' + NCHAR(13) + 
          N'WHERE 1=1 '
  2. Bahti Samet Coban says:

    First of all, thanks for this amazing script and sharing with us.
    I found your script while I was trying to build the same query and got stuck at one point. I realised I couldn’t get necesssary info with your script too.
    I have a login which has a connect grant for an endpoint. I can query this but I can’t develop a create script since there is no info about the endpoint name in sys.server_permissions table.

    I believe there should be a way for this?

    • Thanks 🙂 I’m rather proud of it myself. It should be easy enough to fix the endpoint part I think. It’s going to be similar to what I’ve got in sp_DBPermissions. sys.server_principals.major_id = sys.endpoints.endpoint_id. I’ve never dealt much with endpoints so I didn’t have any easy way to test it. If you’d like to email me a script to create an endpoint or two and add some permissions I’ll get the script working.

      • Bahti Samet Coban says:

        Hi Kenneth,

        Currently I am working on this script. I successfully added Endpoints, then I realized that server principals are also an issue. I also fixed that. Now I am working on a new class added after SQL 2014 I believe: Availability Groups. I will share with you when it is finished.
        Cheers

  3. Jason Thurston says:

    I had some trouble with the generated revoke and grant scripts for impersonation. I made a code change to get it to work. Not sure whether it’s the right solution and whether to submit to here or github, perhaps I’ll post both places. Here’s the relevant section:

    Attempting to change the generated code from:
    REVOKE IMPERSONATE FROM [foodomain\domain users];
    GRANT IMPERSONATE TO [foodomain\domain users] AS [svr_readonly];

    to this:
    REVOKE IMPERSONATE ON LOGIN::svr_readonly FROM [foodomain\domain users];
    GRANT IMPERSONATE on LOGIN::svr_readonly TO [foodomain\domain users] AS [svr_readonly];

    New code fragment:
    SET @sql =
    N’SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName,
    Grantor.name AS GrantorName, Permission.class_desc, Permission.permission_name,
    Permission.state_desc,
    CASE WHEN Grantee.principal_id < 100 THEN NULL ELSE
    ''REVOKE '' +
    CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL
    WHEN Permission.[state] = ''W'' THEN ''GRANT OPTION FOR '' ELSE '''' END +
    '' '' + Permission.permission_name' + @Collation + ' +
    CASE WHEN Permission.class_desc = ''SERVER_PRINCIPAL''
    THEN '' ON ''+ (select CASE WHEN type=''R'' THEN ''LOGIN::'' ELSE ''LOGIN::''END + name from sys.server_principals where principal_id = Permission.major_id) ELSE '''' END +
    '' FROM '' + QUOTENAME(Grantee.name' + @Collation + ') + ''; '' END AS RevokeScript,
    CASE WHEN Grantee.principal_id < 100 THEN NULL ELSE
    CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL
    WHEN Permission.[state] = ''W'' THEN ''GRANT'' ELSE Permission.state_desc' + @Collation +
    ' END +
    '' '' + Permission.permission_name' + @Collation + ' +
    CASE WHEN Permission.class_desc = ''SERVER_PRINCIPAL''
    THEN '' ON ''+ (select CASE WHEN type=''R'' THEN ''LOGIN::'' ELSE ''LOGIN::''END + name from sys.server_principals where principal_id = Permission.major_id) ELSE '''' END +
    '' TO '' + QUOTENAME(Grantee.name' + @Collation + ') + '' '' +
    CASE WHEN Permission.[state] = ''W'' THEN '' WITH GRANT OPTION '' ELSE '''' END +
    '' AS ''+ QUOTENAME(Grantor.name' + @Collation + ') + '';'' END AS GrantScript
    FROM sys.server_permissions Permission
    JOIN sys.server_principals Grantee
    ON Permission.grantee_principal_id = Grantee.principal_id
    JOIN sys.server_principals Grantor
    ON Permission.grantor_principal_id = Grantor.principal_id
    WHERE 1=1 '

  4. Susan K Miller says:

    @Kenneth, did you have an suggestion for an ALL excluding system dbs in the works?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,154 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: