Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

SQL Server user roles and permissions query

04 October,2022 by Tom Collins

Question: Do you have a query that returns the user permissions for the database objects ?

Answer:  This query utilises the sys.database_principals view joined with the sys.database_permissions. 

The sys.database_principals returns a row for every security principal for a SQL Server database

The sys.database_permissions  returns a recordset of rows for every permission


,       db_pri.type_desc AS USER_TYPE
,       pmt.permission_name AS PERMISSION
,       pmt.state_desc AS PERMISSION_STATE
,       pmt.class_desc CLASS
,       object_name(pmt.major_id) AS [OBJECT_NAME]
FROM    sys.database_principals db_pri
        sys.database_permissions pmt
ON      pmt.grantee_principal_id = db_pri.principal_id

read more on user permissions

How to Script SQL database role permissions and securables

Powershell sql server security audit - SQL Server DBA

SQL Database roles and members


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 SQL Server user roles and permissions query | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer