You are currently viewing A replacement for syslogins
Microsoft SQL Server

A replacement for syslogins

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 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

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

Leave a Reply