sqlstudent144
Fix grant/revoke scripts for impersonate on sp_SrvPermissions
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 '
