This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view syslogins returns one row for each login account in SQL Server.  This includes logins which are windows, certificate, or SQL authentication based.

Syslogins is primarily replace by two catalog views.  The first is sys.server_principals; which provides a row for each login on the server, regardless of whether they are windows, certificate, or SQL authentication based.  The second is sys.sql_logins which only returns information on SQL authentication logins with the same data as sys.server_principals with additional columns that specifically apply to SQL authentication.

Status Column

Similar to many other status columns in compatibility view, the column in syslogins is defined as being for informational purposes with future compatibility not guaranteed.  Unlike most other status columns, though, the definition for the bits is easily identifiable.  The first part of the value is 8, which at this point in SQL Server’s lifecycle has no meaning.  The second part comes from the state column in sysprvs, which is an internal table.  If the value in state is G or W then the value is 1 otherwise the value is 2.  Both of these are added together create the status value.  Considering the components in the status column, there is not a lot of value in translating these into the catalog views.

Query Via syslogins

Applications that query syslogins are likely to be fairly simple.  As an example, the query in Listing 1, likely represents a typical syslogins query.  While the query is simple, there are a couple things that bear mentioning.  First, as already described, the status column does not represent any business rules that continue to be easily definable.  Second, there are a number of statistics columns that do not return any value other than 0.  These columns are totcpu, totio, spacelimit, timelimit, and resultlimit.

--Listing 1 – Query for sys.syslogins
SELECT sid
, status
, createdate
, updatedate
, accdate
, totcpu
, totio
, spacelimit
, timelimit
, resultlimit
, name
, dbname
, password
, language
, denylogin
, hasaccess
, isntname
, isntgroup
, isntuser
, sysadmin
, securityadmin
, serveradmin
, setupadmin
, processadmin
, diskadmin
, dbcreator
, bulkadmin
, loginname
FROM syslogins

Query via sys.server_principals and sys.sql_logins

Recreating the syslogins output with sys.server_principals and sys.sql_logins is a bit more complex than with some of the other compatibility views.  Besides the two core catalog views, the views sys.server_permissions and sys.server_role_members are required as well.  These two catalog views are necessary to denormalize the permissions and role memberships that are included in syslogins.  Another complexity, or variation from the compatibility view, pertains to the accdate column.  In the catalog views, this data is no longer maintained and is instead represented by create_date.  The biggest variance, which cannot be reverse engineered, is the password column.  With the compatibility view, there were methods that could be leveraged to decipher the value, making SQL logins a bit less secure.  With the new catalog views, the security around the column is much improved.  Lastly, the results need to be filtered to remove server roles from the results, since the new catalog views include those in the results.  The end result is a query like the one provided in Listing 2.

--Listing 2 – Query for sys.server_principals and sys.sql_logins
SELECT sp.sid
,NULL AS status
,sp.create_date
,sp.modify_date
,sp.create_date AS accdate
,0 AS totcpu
,0 AS totio
,0 AS spacelimit
,0 AS timelimit
,0 AS resultlimit
,sp.name
,sp.default_database_name AS dbname
,sl.password_hash AS PASSWORD
,sp.default_language_name AS language
,CASE WHEN spm.state_desc='DENY' THEN 1 ELSE 0 END AS denylogin
,CASE WHEN spm.state_desc='GRANT' THEN 1 ELSE 0 END AS hasaccess
,CASE WHEN sp.type_desc IN ('WINDOWS_GROUP','WINDOWS_LOGIN') THEN 1 ELSE 0 END AS isntname
,CASE WHEN sp.type_desc='WINDOWS_GROUP' THEN 1 ELSE 0 END AS isntgroup
,CASE WHEN sp.type_desc='WINDOWS_LOGIN' THEN 1 ELSE 0 END AS isntuser
,COALESCE(x.sysadmin, 0) AS sysadmin
,COALESCE(x.securityadmin, 0) AS securityadmin
,COALESCE(x.serveradmin, 0) AS serveradmin
,COALESCE(x.setupadmin, 0) AS setupadmin
,COALESCE(x.processadmin, 0) AS processadmin
,COALESCE(x.diskadmin, 0) AS diskadmin
,COALESCE(x.dbcreator, 0) AS dbcreator
,COALESCE(x.bulkadmin, 0) AS bulkadmin
,sl.name AS loginname
,sp.principal_id
,sp.type
,sp.type_desc
,sp.is_disabled
,sp.credential_id
,sp.owning_principal_id
,sp.is_fixed_role
,sl.is_policy_checked
,sl.is_expiration_checked
FROM sys.server_principals sp
LEFT OUTER JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
LEFT OUTER JOIN sys.server_permissions spm ON sp.principal_id = spm.grantee_principal_id AND spm.type = 'COSQ'
CROSS APPLY (SELECT MAX(CASE WHEN isp.name = 'sysadmin' THEN 1 ELSE 0 END) AS sysadmin
,MAX(CASE WHEN isp.name = 'securityadmin' THEN 1 ELSE 0 END) AS securityadmin
,MAX(CASE WHEN isp.name = 'serveradmin' THEN 1 ELSE 0 END) AS serveradmin
,MAX(CASE WHEN isp.name = 'setupadmin' THEN 1 ELSE 0 END) AS setupadmin
,MAX(CASE WHEN isp.name = 'processadmin' THEN 1 ELSE 0 END) AS processadmin
,MAX(CASE WHEN isp.name = 'diskadmin' THEN 1 ELSE 0 END) AS diskadmin
,MAX(CASE WHEN isp.name = 'dbcreator' THEN 1 ELSE 0 END) AS dbcreator
,MAX(CASE WHEN isp.name = 'bulkadmin' THEN 1 ELSE 0 END) AS bulkadmin
FROM sys.server_role_members srm
INNER JOIN sys.server_principals isp ON srm.role_principal_id = isp.principal_id
WHERE sp.principal_id = srm.member_principal_id) x
WHERE sp.type_desc <> 'SERVER_ROLE'

There are two things to consider beyond the complexity in translating between the compatibility and catalog views.  First, with the replacement catalog views, the information for SQL logins is available in a separate catalog view that serves as a subset of the other catalog view.  If only SQL logins are required by the query, they can be retrieved from a single catalog view without filtering with the columns specifically related to them included.

The second item is the additional columns available in the catalog views.  The first item to note is the principal_id column which provides an identity value across the logins.  Next is the type which defines the type of login that is being described, such as a windows group,etc.  There are also columns to determine whether the account is disabled, is a fixed role, adheres to the password policy, and other properties.  Overall, logins are better described within the catalog views and with information that is not available through the compatibility view.

Summary

In this post, we discussed the use of sys.server_principals and sys.sql_logins instead of syslogins.  While recreating the compatibility view with the catalog views takes a bit of complexity, the additional information available should make the effort worth the trouble.  After reading all of this, do you see any reason to continue using syslogins?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – sysindexkeys
2. Lost in Translation – Deprecated System Tables – sysmembers
3. Lost in Translation – Deprecated System Tables – sysfulltextcatalogs