Advertisement
Guest User

Untitled

a guest
Feb 22nd, 2019
120
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.44 KB | None | 0 0
  1. select prin.name [Grantor], prin2.name [Grantee], perm.*
  2. from sys.server_permissions perm
  3. left join sys.server_principals prin
  4. on perm.grantor_principal_id =prin.principal_id
  5. left join sys.server_principals prin2
  6. on perm.grantee_principal_id =prin2.principal_id
  7.  
  8. DECLARE @_msparam_0 nvarchar(4000);
  9.  
  10. SET @_msparam_0 = '##MS_AgentSigningCertificate##';
  11.  
  12. SELECT ascii(prmssn.state) AS [PermissionState]
  13. , null AS [Code]
  14. , grantor_principal.name AS [Grantor]
  15. , prmssn.type AS [SqlCodePP]
  16. , CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
  17. CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
  18. WHEN 'R' THEN CASE prmssn.class
  19. WHEN 4 THEN 201
  20. ELSE 301
  21. END
  22. WHEN 'A' THEN 202
  23. ELSE
  24. CASE prmssn.class
  25. WHEN 4 THEN 200
  26. ELSE 101
  27. END
  28. END
  29. ELSE prmssn.class
  30. END AS [HiddenObjectClass]
  31. FROM sys.server_permissions AS prmssn
  32. INNER JOIN sys.server_principals AS grantor_principal ON
  33. grantor_principal.principal_id = prmssn.grantor_principal_id
  34. INNER JOIN sys.server_principals AS grantee_principal ON
  35. grantee_principal.principal_id = prmssn.grantee_principal_id
  36. WHERE (prmssn.class = 100)
  37. AND (grantee_principal.name = @_msparam_0);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement