How to query the effective permission of sql login (windows and/or sql user)?

Posted on

Question :

How to query the SQL server to fetch the effective permission of the following SQL logins under the Security/Logins:

  1. SQL user
  2. Windows user
  3. Windows group

Answer :

You can use the system view sys.server_permissions to query for all server level permissions (which would include permissions assigned to Logins). If you join it to the sys.server_principals system view, you can get information about the Login itself as well.

Example from the aforementioned docs:

SELECT pr.principal_id,, pr.type_desc,   
    pe.state_desc, pe.permission_name   
FROM sys.server_principals AS pr   
JOIN sys.server_permissions AS pe   
    ON pe.grantee_principal_id = pr.principal_id;  

You may also be interested in the system view sys.database_permissions to query for database specific permissions for any database Users associated with those Logins.

Leave a Reply

Your email address will not be published. Required fields are marked *