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 sysprotects returns rows for permissions within a database.  The view provides information on GRANT and DENY permissions that have been applied to objects in the database.

To replace sysprotects, there are two catalog views available in SQL Server.  These are sys.database_permissions and sys.server_permissions; which are also the catalog view that replace syspermissions..  The output from the catalog views is similar in content to that of sysprotects, there is just a bit less work in returning the information.

Query Via sysprotects

Queries for sysprotects require a bit of translation before their results can be useful.  The columns action and protecttype both are returned as integer values, but need to be converted to text values to be useful.  The values can be found on Books Online, and are also included in the query provided in Listing 1.  The only column of concern in sysprotects is columns. The columns column provides information on the columns that are affected by the permission.  Generally, the value indicates either that all or some columns are affected.  With that definition, the value returned isn’t the most useful.  Because of this, effort was not spent to parse or recreate the value.

--Listing 1 – Query for sys.sysprotects
SELECT id
    ,uid
    ,grantor
    ,CASE action
        WHEN '26' THEN 'REFERENCES'
        WHEN '178' THEN 'CREATE FUNCTION'
        WHEN '193' THEN 'SELECT'
        WHEN '195' THEN 'INSERT'
        WHEN '196' THEN 'DELETE'
        WHEN '197' THEN 'UPDATE'
        WHEN '198' THEN 'CREATE TABLE'
        WHEN '203' THEN 'CREATE DATABASE'
        WHEN '207' THEN 'CREATE VIEW'
        WHEN '222' THEN 'CREATE PROCEDURE'
        WHEN '224' THEN 'EXECUTE'
        WHEN '228' THEN 'BACKUP DATABASE'
        WHEN '233' THEN 'CREATE DEFAULT'
        WHEN '235' THEN 'BACKUP LOG'
        WHEN '236' THEN 'CREATE RULE' END AS protecttype
    ,CASE protecttype
        WHEN 204 THEN 'GRANT_W_GRANT'
        WHEN 205 THEN 'GRANT'
        WHEN 206 THEN 'DENY' END AS protecttype
    ,columns
FROM sysprotects

Query via sys.database_permissions

Querying permissions from sys.database_permissions is much more direct than with the compatibility view.  The two columns in sysprotects that need to be translated in the compatibility view are provided directly in the catalog view, as the columns permission_name and state_desc.  As with many other comparisons of compatibility views to catalog views, the column names between the two are different.  The only difference between the two views is the columns column which is returning as NULL in the query in Listing 2, the reason for the NULL value was explained in the last section.

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

Query via sys.server_permissions

For sys.server_permissions, the query is similar to sys.database_permissions, shown in Listing 3.  The main difference between the database and server version of the view is the scope of the catalog views.  When comparing sys.server_permissions to the compatibility view the biggest difference between the them are the scope of data.  Sysprotects is limited to database level permissions and sys.server_permissions are scoped to the server.

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

Summary

In this post, we compared the compatibility view sysprotects with the catalog view sys.database_permissions.  Modifying applications or processes to use the supported catalog view over the compatibility view is a simple feat; which you will hopefully be open to partaking in.  After reading all of this, do you see any reason to continue using sysprotects?  Is there anything missing from this post that people continuing to use the compatibility view should know?