/*************************************************************** Tomas Lind 2014 The listed SQL statement mimics the deprecated sys.syslogins, except for password & status. The cte can be used alone to view a summary of server level permissions. ***************************************************************/ WITH cte_srm (principal_id, sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin) 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) [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.principal_id WHERE pr.[type] <> 'R' GO