Fix grant/revoke scripts for impersonate on sp_SrvPermissions #14
Description
Comment from Jason Thurston
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 '