Printed 2016/07/25 02:50PM

Lost in Translation – Deprecated System Tables – sysmembers

By StrateSQL, 2012/11/16

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

The compatibility view sysmembers returns a single row for each member of a database role.  The compatibility view is scoped to the database level.

The view sysmembers is being replaced by the catalog view  sys.database_role_members.  In near identical fashion, the catalog view is also scoped to the database and returns one row for every member of a database role.

Query Via sysmembers

The compatibility view sysmembers is quite simple.  It includes two columns used to map the login to the database role.  A query against the view would be written as the query in Listing 1.

--Listing 1 – Query for sys.sysmembers

SELECT  memberuid
, groupuid
FROM sysmembers

Query via sys.database_role_members

The catalog view replacing sysmembers is as simple as the compatibility view.  Sys.database_role_members also only contains two columns which represent the same information.  The chief difference between the two views is the names of the columns.  Mapping the columns from the catalog view to match the compatibility view results in the query provided in Listing 2.

--Listing 2 – Query for sys.database_role_members

SELECT member_principal_id AS memberuid
, role_principal_id AS groupuid
FROM sys.database_role_members


In this post, we demonstrated the similarities between sysmembers and sys.database_role_members.  With just the changes in column names differentiating the two, a transition from one to the other is a simple task.  After reading all of this, do you see any reason to continue using sysmembers?  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 – syspermissions
  2. Lost in Translation – Deprecated System Tables – sysmessages
  3. Lost in Translation – Deprecated System Tables – sysindexkeys

Copyright © 2002-2016 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.