The system table sys.syslogins was marked as deprecated in SQL Server 2005, and is included up until 2012 for backwards compability only.
So what is the replacement of sys.syslogins? As it turns out, there is no exact match in SQL Server 2005 and later. Instead there are various system views that can be combined to gather the same information.
I created a SQL statement to combine those system views into a single view, matching the old sys.syslogins.
Note that the old encrypted password for SQL logins doesn’t exist in the new system views. I have replaced the passwords with NULL. Another column I could not recreate is the “status” column. It is replaced with NULL as well.
The SQL replacement for sys.syslogins can be found here: Syslogins Replacement. Or copied from here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | /***************************************************************Tomas Lind 2014The listed SQL statement mimics the deprecated sys.syslogins,except for password & status.The cte can be used alone to view a summary of serverlevel 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 loginnameFROM sys.server_principals AS prLEFT OUTER JOIN sys.server_permissions AS peON pr.principal_id = pe.grantee_principal_idAND pe.[type] = 'COSQ'LEFT OUTER JOIN cte_srmON pr.principal_id = cte_srm.principal_idWHERE pr.[type] <> 'R'GO |
