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 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
Summary
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:
- Lost in Translation – Deprecated System Tables – syspermissions
- Lost in Translation – Deprecated System Tables – sysmessages
- Lost in Translation – Deprecated System Tables – sysindexkeys
