View server roles and permissions per Login

Understanding Server Principals

The server_principals table stores a row for each server-level principal. This table holds details like the principal’s name, type, and whether the account is disabled. Additionally, it tracks the date the account was created and last modified.

Example Query to Retrieve Server Principals

Here is a sample SQL query to gather detailed information about server principals:

SELECT 
    sp.[Name] AS ServerPrincipal, 
    sp.[type_desc] AS LoginType, 
    CASE sp.is_disabled 
        WHEN 0 THEN 'No' 
        WHEN 1 THEN 'Yes' 
    END AS UserDisabled, 
    sp.create_date AS DateCreated, 
    sp.modify_date AS DateModified, 
    sp.default_database_name AS DefaultDB, 
    sp.default_language_name AS DefaultLang, 
    ISNULL(STUFF((
        SELECT ',' + 
            CASE ssp22.[Name] 
                WHEN 'sysadmin' THEN ssp22.[Name] + ' "Full privileges"' 
                ELSE ssp22.[Name] 
            END
        FROM [sys].server_principals ssp2
        INNER JOIN [sys].server_role_members ssrm2 ON ssp2.principal_id = ssrm2.member_principal_id
        INNER JOIN [sys].server_principals ssp22 ON ssrm2.role_principal_id = ssp22.principal_id
        WHERE ssp2.principal_id = sp.principal_id
        ORDER BY ssp2.[Name]
        FOR XML PATH (''), TYPE
    ).value('.[1]', 'nvarchar(max)'), 1, 1, ''), 'No Roles Held') AS ListofServerRoles,
    ISNULL(STUFF((
        SELECT ';' + ' Permission [' + sspm3.[permission_name] + '] is [' + 
            CASE 
                WHEN sspm3.[state_desc] = 'GRANT' THEN 'Granted]' 
                WHEN sspm3.[state_desc] = 'DENY' THEN 'Denied]' 
            END AS PermGrants
        FROM [sys].server_principals ssp3
        INNER JOIN [sys].server_permissions sspm3 ON ssp3.principal_id = sspm3.[grantee_principal_id]
        WHERE sspm3.[class] = 100 AND sspm3.[grantee_principal_id] = sp.principal_id
        FOR XML PATH (''), TYPE
    ).value('.[1]', 'nvarchar(max)'), 1, 1, ''), 'No Server Permissions') + ' in Server::' + @ServerName AS PermGrants
FROM [sys].server_principals sp
WHERE sp.[Type] IN ('S', 'G', 'U') AND sp.[Name] NOT LIKE '##%##'
ORDER BY ServerPrincipal;

This query retrieves the server principal name, its type, its roles, and associated permissions.

Additional Useful Queries

Built-In Permissions Hierarchy

To retrieve a list of the built-in permissions available for both servers and databases, you can use the following queries:

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;

Login and Server Role Mapping

To view the mapping of logins to their respective server roles, run this query:

SELECT spU.name,
       MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin,
       MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin,
       MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin,
       MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin,
       MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin,
       MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin,
       MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator,
       MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
FROM sys.server_principals AS spR
JOIN sys.server_role_members AS srm ON spR.principal_id = srm.role_principal_id
JOIN sys.server_principals AS spU ON srm.member_principal_id = spU.principal_id
WHERE spR.[type] = 'R'
GROUP BY spU.name;

This query will list the users along with their assigned roles, such as sysadmin, securityadmin, and others.

Orphaned Database User Discovery

To check for orphaned database users and their login mappings, you can use:

exec sp_change_users_login @Action='Report';

This command helps identify orphaned users in your database.

For more details, you can refer to the official documentation here.

These SQL queries and examples provide a comprehensive way to manage and query server-level roles and permissions in SQL Server.