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 sysusers returns a row for every Windows user or group, SQL Server user, or role in a database, otherwise known as principals.  The view identifies the principals and some basic security setting for each principle.

The compatibility view is replaced by the catalog view sys.database_principals.  This view provides the same set of rows, where each row represents a principle in the database.  The catalog view some similar details on the principals, but there is a lot returned that does not overlap the compatibility view.

Status Column

Similar to other compatibility views, sysusers has a statuscolumn that contains permission information on the principals.  The values in the

• 0×4:isntgroup
• 0x0C:isntuser
• 0×20: isapprole

The values stored in the status column are not that useful.  Each of the values are materialized in a column that already exists in sysusers.

Query Via sysusers

To query sysusers, there isn’t much needed to get the information that is needed.  Since the values in the status column are already in the view, they can be skipped in a query.  For completeness, though, the query in Listing 1 includes the status column and expressions to extract the stored values.

--Listing 1 – Query for sys.sysusers
SELECT uid
, name
, sid
, roles
, createdate
, updatedate
, altuid
, password
, gid
, environ
, hasdbaccess
, islogin
, isntname
, isntgroup
, isntuser
, issqluser
, isaliased
, issqlrole
, isapprole
, status
, CONVERT(INT,status & 0x4) / 4 AS isntgroup
, CONVERT(INT,status & 0x0C) / 12 AS isntuser
, CONVERT(INT,status & 0x20) / 32 AS isapprole
FROM sys.sysusers

The main thing to consider when looking at using sysusers is the columns that do not store any values any longer. These columns are roles, password, environ, and isaliased. For all rows, these columns either return NULL or 0, which makes any business logic based on them entirely suspect.

Query via sys.database_principals

The query for accessing the same information provided in sysusers from the catalog view sys.database_principals contains a few things that need to be considered.  First, there are a few columns that always return either 0 or NULL, mentioned in the previous section.  Next there are the columns that pivot the type_desc value into some calculated values.  These are columns such as altuid, gid, islogin, and etc.  They are all based on type_desc, which is provided in a single column in the catalog view.  The last item is the hasdbaccess column which denormalizes the CONNECT permission from sys.database_permissions into the compatibility view.  Putting these all together results in a query like the one provided in Listing 2.

--Listing 2 – Query for sys.database_principals
SELECT principal_id AS uid
, name
, sid
, NULL AS roles
, create_date AS createdate
, modify_date AS updatedate
, CASE WHEN type_desc = 'DATABASE_ROLE' THEN 1 END AS altuid
, NULL AS password
, CASE WHEN type_desc = 'DATABASE_ROLE' THEN principal_id ELSE 0 END AS gid
, NULL AS environ
, CASE WHEN x.state_desc = 'GRANT' THEN 1 ELSE 0 END AS hasdbaccess
, CASE WHEN type_desc = 'SQL_USER' THEN 1 ELSE 0 END AS islogin
, CASE WHEN type_desc IN ('WINDOWS_USER','WINDOWS_GROUP') THEN 1 ELSE 0 END AS isntname
, CASE WHEN type_desc = 'WINDOWS_GROUP' THEN 1 ELSE 0 END AS isntgroup
, CASE WHEN type_desc = 'WINDOWS_USER' THEN 1 ELSE 0 END AS isntuser
, CASE WHEN type_desc = 'SQL_USER' THEN 1 ELSE 0 END AS issqluser
, 0 AS isaliased
, CASE WHEN type_desc = 'DATABASE_ROLE' THEN 1 ELSE 0 END AS issqlrole
, CASE WHEN type_desc = 'APPLICATION_ROLE' THEN 1 ELSE 0 END AS isapprole
, CASE WHEN type_desc = 'DATABASE_ROLE' THEN 1 ELSE 0 END AS status
, type
, type_desc
, default_schema_name
, owning_principal_id
, is_fixed_role
, authentication_type
, authentication_type_desc
, default_language_name
, default_language_lcid
FROM sys.database_principals d
OUTER APPLY (SELECT dp.state_desc
FROM sys.database_permissions dp
WHERE d.principal_id = dp.grantee_principal_id
AND permission_name = 'CONNECT') x

Outside of the compatibility columns, there are a few additional columns in sys.database_principals.  The main new columns are type and type_desc which replaces many of the previous columns in the view.  There are also columns to identify fixed roles, default languages, default schema and authentication types.  Overall, these provide a clearer picture of the principal and allow better insight into the accounts accessing the database.

Summary

In this post, we compared the compatibility view sysusers with the catalog view sys.database_principals.  As demonstrated, the biggest difference between the two views is the multiple columns that can now be rolled up into the type_desc column. The domain of information covered between the two, though, is the same.  The thing that should be considered with any current use of sysusers are the columns that no longer contain meaningful information; which makes a great case for reviewing all uses of sysusers.  After reading all of this, do you see any reason to continue using sysusers?  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 – sysmessages
2. Lost in Translation – Deprecated System Tables – syslogins
3. Lost in Translation – Deprecated System Tables – sysobjects