Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to get database role securables

29 March,2021 by Tom Collins

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




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'
ascii(prmssn.state) AS [PermissionState],
null AS [Code], 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]
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
(prmssn.class = 100)and(

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:




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: Tom Collins (


Verify your Comment

Previewing your Comment

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

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.


Post a comment on How to get database role securables | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer