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
DENY permission assigned to the user.
Just last week I had a user in one of my lab environments who was unable to
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
The following script queries the
sys.database_permissions catalog view within SQL Server to display all
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.
USE [RPF] --provide database name
SELECT object_name(major_id) AS object,
USER_NAME(grantee_principal_id) AS 'grantee',
USER_NAME(grantor_principal_id) AS 'grantor',
class_desc AS 'description',
--WHERE USER_NAME(grantee_principal_id) = '' --enter Login Name
ORDER BY grantee
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.
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:
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.
DENY DELETE ON UPR00100 TO [JWorthen]
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
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:
REVOKE DELETE ON UPR00100 TO [JWorthen]
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.