Identifying Database Permissions Using The sys. database_ permissions Catalog View

When troubleshooting a permissions issue in SQL Server I have found a quick method for identifying (or in some cases ruling out) the cause.   The sys.database_permissions catalog view can be used to identify active database permissions assigned within the current database.  A record is returned for each GRANT or DENY permission assigned to the user.

Just last week I had a user in one of my lab environments who was unable to INSERT, UPDATE or DELETE within a particular database on the server.  After reviewing the user’s role mappings within SQL Management Studio, the user appeared to be set up with all the necessary permissions.  Unfortunately, what was not immediately apparent was that the user had a DENY permission assigned that was trumping the existing GRANT permissions.

The following script queries the sys.database_permissions catalog view within SQL Server to display all GRANT and DENY permissions for each user. This script will run against the active database. If you are reviewing multiple databases on a single server you will need to run it against each database separately.  In this example I am running it against the RPF database.

You can also limit the number or records returned by removing the comment code in the WHERE clause and providing a Login Name.  This will return only the records associated with a particular login.

The screenshot below illustrates an example of the data that is returned by the script.

permissions_screenshot

In this example my user account has GRANT permissions only.  In the case described above, the user had a DENY permission that was preventing him from executing certain commands in SQL.  It is important to remember that a DENY will always trump a GRANT.   Once the DENY permission was revoked the user was able to perform all the necessary tasks in the lab environment.

To better illustrate this lets take a look at the results of running the permissions script against the Dynamics GP company database for my login.  The results are displayed below:

permissions_screenshot_2

A single record is returned showing that I have the ability to connect to the database.

Now lets run the following script to restrict my ability to delete data from the UPR00100 table in the database.

Once the above code has been executed we can run the permissions query script again and see that an additional record is returned showing the DENY on the UPR00100.

permissions_screenshot_3

If you want to remove the DENY record from the login in order to allow the account to delete from the specified table simply run the following script:

Be careful when revoking and denying permissions to accounts.  Granting too much access to a particular login can be a serious security concern.  Denying access to an established account can also cause issues.  If the account is used by an application or a web service it could result in a disruption that prevents the application from performing certain required actions.