Finding All Access and Permissions for All Users in SQL server
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT sys.server_permissions.class , sys.server_permissions.class_desc , sys.server_permissions.major_id , sys.server_permissions.minor_id , sys.server_permissions.grantee_principal_id , sys.server_permissions.grantor_principal_id , sys.server_permissions.type , sys.server_permissions.permission_name , sys.server_permissions.state , sys.server_permissions.state_desc , granteeserverprincipal.name AS grantee_principal_name , granteeserverprincipal.type_desc AS grantee_principal_type_desc , grantorserverprinicipal.name AS grantor_name , CASE WHEN sys.server_permissions.state = N'W' THEN N'GRANT' ELSE sys.server_permissions.state_desc END + N' ' + sys.server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' TO ' + QUOTENAME(granteeserverprincipal.name) AS permissionstatement FROM sys.server_principals AS granteeserverprincipal INNER JOIN sys.server_permissions ON sys.server_permissions.grantee_principal_id = granteeserverprincipal.principal_id INNER JOIN sys.server_principals AS grantorserverprinicipal ON grantorserverprinicipal.principal_id = sys.server_permissions.grantor_principal_id ORDER BY granteeserverprincipal.name , sys.server_permissions.permission_name |