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:
/***************************************************************
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
