mssqltips logo

Determining Permission Issues for a SQL Server Object

By:   |   Updated: 2013-09-24   |   Comments (7)   |   Related: More > Security

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.

If you look at the properties for the object within SQL Server Management Studio, it may be easy to spot the issue

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.

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


Last Updated: 2013-09-24


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, January 02, 2018 - 9:28:54 AM - Barak G. Back To Top

* sorry missed part of the code in the previous comment.

 

Great help!

Used this piece of code to clean users permissions and get everything into role peermissions.

This is how you can locate explicit permissions for users outside of a role.

Grant to role + revoking all explicit permissions to a user will not cause loss of access.

 

 

-- Find out which users are getting explicit permissions

select distinct u.name as User_or_Role

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

 

 

 

-- Find permissions applied to users outside of a Databse Role --

select dp.class_desc, o.name as Object, u.name as User_or_Role,u.type_desc, 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 u.name in ('user1','user2', 'user3') AND NOT EXISTS 

(select 1

from sys.database_permissions as dp2

join sys.objects as o2 on dp2.major_id = o2.object_id

join sys.database_principals as u2 on dp2.grantee_principal_id = u2.principal_id

where u2.name = 'DatabaseRole' and  dp2.class_desc = dp.class_desc and o2.name = o.name

and dp2.state_desc = dp.state_desc and dp2.permission_name = dp.permission_name)

order by u.name,count(1) over (partition by dp.class_desc),o.name,dp.permission_name;

 

 

 

Or change the select criteria to create the modifications:

select distinct dp.state_desc COLLATE DATABASE_DEFAULT +' '+ dp.permission_name +' ON ' 

+ case dp.class_desc when 'SCHEMA' then 'SCHEMA' ELSE 'OBJECT' END +'::'+ s.name +'.'

+ o.name +' TO VBolsaPP'

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

join sys.schemas s on o.schema_id = s.schema_id

where u.name in ('vbcibsa_portfolio','vbcibsa', 'Portfolio') AND NOT EXISTS 

(select 1

from sys.database_permissions as dp2

join sys.objects as o2 on dp2.major_id = o2.object_id

join sys.database_principals as u2 on dp2.grantee_principal_id = u2.principal_id

where u2.name = 'VBolsaPP' and  dp2.class_desc = dp.class_desc and o2.name = o.name 

and dp2.state_desc = dp.state_desc 

and dp2.permission_name = dp.permission_name)

 


Saturday, December 30, 2017 - 8:02:57 AM - Haris Back To Top
 
Hi Sir
 
         I faced a Server problem, which i formatted OS and install SQL Server 2000 and restored all database which is about 14 Database (modules). 
 
        And it works fine but unable to apply exisiting Permission for database/tables/view/stored Procedure which already there in for evey Login
 
Please suggest ! 
 
Thanks for your kind cooperation
 
Best regards
Haris

Wednesday, December 06, 2017 - 9:51:58 AM - trump Back To Top

 

 very good


Monday, September 22, 2014 - 9:06:57 AM - Bill Back To Top

What the heck is a "rotect", anyways? And what about sp_addumpdevice - what is an "ump"?
Short an sweet article.

While a niche case, it would be nice to also see which objects have users or roles with permission to grant permissions (WITH GRANT OPTION, state='W' in sys.database_permissions), but that might be confusing :)


Friday, August 22, 2014 - 10:14:20 AM - K. Brian Kelley Back To Top

Keep in mind that as of SQL Server 2005, sp_helprotect is deprecated. For one thing, it only returns permissions on objects that existed in SQL Server 2000. Therefore, it'll never report permissions at the schema or database level. So while it works for this simple example, it no longer works in all cases.

 


Friday, August 22, 2014 - 7:50:49 AM - indy Back To Top

Hi,

I think the below sp would give the table permissions.

EXEC SP_HELPROTECT 'SalesLT.Customer'

Result 

Owner          Object            Grantee      Grantor  ProtectType     Action       Column

-------------- ------------   ------------ ------------ --------------- ------------ ------------------

SalesLT        Customer         public       dbo      Deny              Select       (All+New)

 

Wednesday, September 25, 2013 - 9:40:59 AM - Dana Back To Top

Thank you.  I actually understand from your explination and have a better understanding of permissions in general.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools