How to query the SQL server to fetch the effective permission of the following SQL logins under the Security/Logins:
- SQL user
- Windows user
- 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.