Problem
I have a security problem. Users have the ability to access the tables in a database with one exception. No matter what permission I grant or what role I give within the database, users are still denied access. I’ve even made users a member of the db_owner role, but to no avail. The only thing that works is to make the user a member of the sysadmin role. However, I can’t give this out for obvious reasons. What’s going on?
Solution
First, let’s cover why making a user a member of the sysadmin role works. Then we’ll explore likely causes and how to troubleshoot it.
SQL Server SysAdmin Role Members Ignore Permissions Because dbo Ignores Permissions
When a member of the sysadmin role comes into a database, it does so as the dbo user. The dbo user is a member of the db_owner role, but it’s a special user. What makes it special? One of the things is that it effectively ignores permissions within the database. Even if it doesn’t have an explicit permission to access an object like a table, it can. And in cases where there is an explicit DENY which would affect dbo, that DENY is ignored. Therefore, you should assume that making someone a member of the sysadmin role will always work and it won’t aid you in your troubleshooting.
No Permission in SQL Server Means No Access
If a user does not have an explicit permission to an object and is not a member of a role that has either implicit or explicit permission to an object, the user cannot access the object. What roles have implicit access?
- db_datareader
- db_datawriter
- db_owner
Therefore, it could be the case where a user has permissions on every other object, just not the one object you’re concerned about. If that’s the case, and the user isn’t a member of a role that has permissions, the user will be denied access. SQL Server works under the model that if you don’t tell it someone should have access, then that person doesn’t have access.
But the User Does Have SQL Server Permissions
If the user has permission, such as by being a member of db_owner, then that likely means a DENY permission is on the object. This is especially true if there are objects of the same type in the same schema to which the user has permissions. If this isn’t the case, for instance, this is the only table in the SomeOtherTable schema, then the DENY could be at the schema level. However, usually it’s a DENY against the object itself.
Why does the DENY work this way? The DENY is a trump. It’s the tool you break out when the user has permission through a role or some other means but that particular database user (and keep in mind, database “user” could map to a login which is a Windows group) needs to be blocked. For instance, if the user is a member of db_datareader but you don’t want users accessing a particular table. The DENY trumps the permissions, implicit or explicit, unless the user we’re talking about is dbo.
Troubleshooting the SQL Server Permissions Issue
If you look at the properties for the object within SQL Server Management Studio, it may be easy to spot the issue. If there aren’t a lot of users or roles with explicit permissions against the object, then this may be the easiest approach. For instance, in Figure 1 we see that DENY is in place for the public role. This affects every user in the database except dbo. Even as db_owner, unless the user is dbo, the user won’t have access.
Figure 1
However, if there are a lot of permissions, or if looking at all those checkboxes is an eye test for you, a simple T-SQL query will do the job nicely:
SELECT o.name AS ‘Object’, u.name AS ‘User_or_Role’, dp.state_desc, dp.permission_name
FROM sys.database_permissions AS dp
JOIN sys.objects AS o
ON dp.major_id = o.object_id
JOIN sys.database_principals AS u
ON dp.grantee_principal_id = u.principal_id
WHERE dp.class = 1
AND o.name = ‘**Your Object Name Here**’;
If we run it against the Person.Contact table, we see the permission very clearly in the results, as shown in Figure 2.
Figure 2
Next Steps
- Read a tip on how GRANT, DENY, and REVOKE work.
- Learn what the permissions are for the various included database roles.
- Understand why certain users have implicit permissions against various objects.
