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 syspermissions returns rows that describe permissions granted and denied to users, groups, and roles.  Most of the columns in the view are no longer populated and the view is only useful in identifying the existence of the permission relationship, rather than the type of permission granted.

For real information on permissions granted and denied, the catalog views s.database_permissions and sys.server_permissions are available as replacements to syspermissions.  These catalog views return a row for every permission relationship, along with a trove of other details that fully define permission sets.

Query Via syspermissions

Querying syspermissions is rather simple.  There are no bit columns or undefined columns that contain values.  There are some undefined columns, such as actadd, actmod, etc. but these columns will always return null.  The only columns that return values are id, grantee, and grantor.  Enough information to know there is a relationship, but not enough to define the relationship.  A sample query against syspermissions is provided in Listing 1.

--Listing 1 – Query for sys.syspermissions
SELECT id
, grantee
, grantor
, actadd
, actmod
, seladd
, selmod
, updadd
, updmod
, refadd
, refmod
FROM syspermissions

Query via sys.database_permissions

With only three useful columns in syspermissions, mapping the columns from sys.database_permissions to the compatibility view is a simple feat.  The query, in Listing 2, provides this mapping, along with providing the additional information available in the catalog view.  There is information on the class that the permission is granted to, the minor_id for when the permission is column based, the type of the permission, and the permission state granted.  In total, the catalog view provides a full view of the permissions that have been granted.

--Listing 2 – Query for sys.database_permissions
SELECT major_id AS id
, grantee_principal_id AS grantee
, grantor_principal_id AS grantor
, class
, class_desc
, minor_id
, type
, permission_name
, state
, state_desc
FROM sys.database_permissions

Two additional items of note in comparing the catalog view to the compatibility view, is that the compatibility view only provides permission relationships on database or object permissions.  The other permission classes, such as schema, service, certificate, etc. are not covered by the compatibility view.  Also, the permissions in the catalog view only encompass database level permissions, which is evident by the views name.

Query via sys.server_permissions

In a similar fashion to sys.database_permissions, the catalog view sys.server_permissions provides information on permission relationships.  This view, however, is scoped to the server level.  The columns between the two are nearly identical in nature, save for the scope and the items that are covered.  When comparing the catalog view to syspermissions, the context of the views are the same but there is no overlap in actual data.  The information in this catalog view just wasn’t previously available.  To query the catalog view, use the query in Listing 3.

--Listing 3 – Query for sys.server_permissions
SELECT major_id AS id
, grantee_principal_id AS grantee
, grantor_principal_id AS grantor
, class
, class_desc
, minor_id
, type
, permission_name
, state
, state_desc
FROM sys.server_permissions

Summary

In this post, we compared the compatibility view syspermissions with the catalog views sys.database_permissions and sys.server_permissions.  There really isn’t any justifiable reason to continue using syspermissions, it doesn’t cover nearly the detail or depth that the catalog views do.  By using the catalog views, all of the details for permissions that have been configured can be retrieved.  After reading all of this, do you see any reason to continue using syspermissions?  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 – sysusers
2. Lost in Translation – Deprecated System Tables – sysmembers
3. Lost in Translation – Deprecated System Tables – sysindexkeys