How to get database role securables

29 March,2021 by Jack Vamvas

Question: I  need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions  including the Permission,Grantor,Grant,With Grant and Deny.  And also the Securables - Schema,Name,Type

Securables

 

 

How can I get this information via t-sql?

Answer: Initially you can check the SQL Server Profiler and trap the actual sql query executed as part of generating the display. An example of the query is :

 

declare @name varchar(20)
set @name = 'myuser'
SELECT
ascii(prmssn.state) AS [PermissionState],
null AS [Code],
grantor_principal.name AS [Grantor],
prmssn.type AS [SqlCodePP],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN 'R' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN 'A' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [HiddenObjectClass]
FROM
sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(prmssn.class = 100)and(grantee_principal.name=@name)


I have modified this statement slightly . One thing you'll notice with the output is some esoteric columns - which you may not have previously explored e.g sys.server_permissions.PermissionState &  sys.server_permissions.class

sys.server_permissions.class = Identifies class of thing on which permission exists

sys.server_permissions.State = Permission state

sys.server_permissions.Type = server permission type

Just using the query above you'll get some data , but unless you're familiar with the codes and the permissions state numbers , it can be of little use. 

A more readable query and  more useful to read Is:

 

 

 

SELECT
       grantee_principal_id,
       class_desc,
       [permission_name],
       state_desc
FROM sys.server_permissions
WHERE grantee_principal_id IN(281);


Some more reading on user permissions

How to Script database role permissions and securables

SQL Server database roles and GDPR

Powershell sql server security audit

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to get database role securables


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer