SQL Server Permission

 

Query to List SQL Server Logins with Their Server-Level Roles

In SQL Server, it’s often necessary to audit or review logins and determine what server-level roles are assigned to each. The following query retrieves a list of all SQL Server logins, along with flags indicating whether each login is a member of a particular server role (such as sysadmin, securityadmin, serveradmin, etc.).

This script uses a Common Table Expression (CTE) to pivot role membership data into columns, making it easy to view role assignments at a glance.

SQL Query

WITH cte_srm AS (
  SELECT
    srm.member_principal_id,
    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 sp
  JOIN sys.server_role_members AS srm 
    ON sp.principal_id = srm.role_principal_id
  WHERE sp.[type] = 'R'
  GROUP BY srm.member_principal_id
)

SELECT
  pr.[sid],
  CAST(NULL AS SMALLINT) AS [status],
  pr.create_date,
  pr.modify_date AS updatedate,
  pr.create_date AS accdate,
  0 AS totcpu,
  0 AS totio,
  0 AS spacelimit,
  0 AS timelimit,
  0 AS resultlimit,
  pr.[name],
  pr.default_database_name AS dbname,
  CAST(NULL AS SYSNAME) AS [password],
  pr.default_language_name AS [language],
  CAST(CASE WHEN pe.state = 'D' THEN 1 ELSE 0 END AS INT) AS denylogin,
  CAST(CASE WHEN pe.state = 'G' THEN 1 ELSE 0 END AS INT) AS hasaccess,
  CAST(CASE WHEN pr.[type] IN ('U', 'G') THEN 1 ELSE 0 END AS INT) AS isntname,
  CAST(CASE WHEN pr.[type] = 'G' THEN 1 ELSE 0 END AS INT) AS isntgroup,
  CAST(CASE WHEN pr.[type] = 'U' THEN 1 ELSE 0 END AS INT) AS isntuser,
  ISNULL(cte_srm.sysadmin, 0) AS sysadmin,
  ISNULL(cte_srm.securityadmin, 0) AS securityadmin,
  ISNULL(cte_srm.serveradmin, 0) AS serveradmin,
  ISNULL(cte_srm.setupadmin, 0) AS setupadmin,
  ISNULL(cte_srm.processadmin, 0) AS processadmin,
  ISNULL(cte_srm.diskadmin, 0) AS diskadmin,
  ISNULL(cte_srm.dbcreator, 0) AS dbcreator,
  ISNULL(cte_srm.bulkadmin, 0) AS bulkadmin,
  pr.[name] AS loginname
FROM sys.server_principals AS pr
LEFT OUTER JOIN sys.server_permissions AS pe 
  ON pr.principal_id = pe.grantee_principal_id AND pe.[type] = 'COSQ'
LEFT OUTER JOIN cte_srm 
  ON pr.principal_id = cte_srm.member_principal_id
WHERE pr.[type] <> 'R';
GO

Summary

This query provides a comprehensive overview of each SQL Server login, including:

• Login metadata (creation date, default database, language)
• Access status (denied login, granted access)
• Role membership (server-level roles shown as 0 or 1)

You can use or adapt this script for server audits, role reviews, or security assessments.