A replacement for syslogins

Microsoft SQL Server

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

Leave a Reply

Required fields are marked *.