Finding All Access and Permissions for All Users in SQL server

Share the blog:

We recently encountered an issue with one of our users being denied access to their login. While troubleshooting, we decided to check the permissions assigned to the login. After a few Google searches, we found the following steps to obtain the desired outcome.

This is a SQL query that retrieves information about server-level permissions and the principals (users or roles) that have been granted or denied those permissions. The query is selecting columns from the sys.server_permissions and sys.server_principals tables, joining the two tables on the grantee_principal_id and grantor_principal_id columns, and ordering the results by the grantee_principal_name and permission_name.

The query uses INNER JOIN to join the sys.server_permissions and sys.server_principals tables, using the grantee_principal_id and grantor_principal_id columns as the join keys.

It is also using a CASE statement to change the state_desc value when the state is ‘W’ and QUOTENAME function to format the results.

It is important to note that this query is for server-level permissions and not for database-level permissions.


Facebook Comments Box
Share the blog:


Leave a Reply

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