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.
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; GO CREATE SCHEMA TestSchema1; GO 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:
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.