I have written quite a few posts about granting, revoking and denying permissions over the last month. Today I would like to show you a catalog view that you can use to see all the permissions that have been granted in the current database.
Identifying active database permissions
As usual, we first need to create a few things to be able to see this catalog view in action:
CREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE USER TestUser1 FOR LOGIN TestLogin1;
CREATE ROLE TestRole1;
ALTER ROLE TestRole1 ADD MEMBER TestUser1;
CREATE SCHEMA TestSchema1;
CREATE TABLE TestSchema1.tst(id INT,col1 INT);
INSERT INTO TestSchema1.tst VALUES(42,17);
With that taken care of, we can set up two granted and a denied permission:
GRANT INSERT ON SCHEMA::[TestSchema1] TO [TestRole1];
GRANT UPDATE ON OBJECT::[TestSchema1].[tst] TO [TestUser1] WITH GRANT OPTION;
DENY SELECT ON OBJECT::[TestSchema1].[tst]([col1]) TO [TestUser1];
To see the above permission, we are going to use the sys.database_permissions catalog view like this:
SELECT * FROM sys.database_permissions AS DP
WHERE DP.grantee_principal_id IN (USER_ID('TestUser1'), USER_ID('TestRole1'));
The sys.database_permissions catalog view returns one row for each permission that has been granted or denied in the current database. In our case, the above query will return the following result:
Let us look at the columns individually:
- class_desc: This identifies the type of securable that this permission pertains to. You need this value to be able to correctly interpret major_id and minor_id.
- major_id and minor_id: These two values identify the securable itself. You can use them to join to other catalog views like sys.objects, sys.columns or sys.certificates. Which catalog view to join to can be determined using the value in the previous column. minor_id is only used for column permissions and represents the column_id of the column the permission was defined on. In all other cases it returns the value 0.
- grantee_principal_id: This column identifies the database principal that the permission was granted to. You can use it for example to join to sys.database_principals.
- grantor_principal_id: This identifies the principal that granted the permission. It is for example used by SQL Server for the REVOKE CASCADE logic. As with the previous column, you can join to sys.database_principals to get more information about the principal.
- permission_name: This value identifies the privilege itself. You will find values like SELECT, EXECUTE or CONTROL in here.
- state_desc: Here you can see if the permission was granted or denied. WITH GRANT OPTION is also identified here. You might even find the value REVOKE in here. I will show you an example of the latter in a later post.
The columns that are not part of the above list each contain some SQL Server internal representation of one of the values described above.
The sys.database_permissions catalog view can be used to identify active database permissions that are defined in the current database. It returns one row for each granted or denied permission.