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

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.name, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.