What to do when select permission was denied on the object?

If select permission was denied on the object-

Database Administrators
Receiving "The SELECT permission was denied on the object" even though it's been granted
Asked 11 years, 3 months ago
Modified 3 years, 9 months ago
Viewed 54k times12

I'm a programmer, not a dba... I know just enough to be dangerous.

I've inherited a database with a legacy user that is a db_owner for the database. We can't adjust this user's permission for existing tables, schemas, etc., for business reasons, but some new tables are being created, and I only want this user to have SELECT access on them.
Permissions have been set for this user for these tables so that everything is DENIED, except SELECT, which is set to GRANT.
Yet when this user (dbadmin) attempts to perform a SELECT on one of these tables (AccountingAudit), this error happens:
The SELECT permission was denied on the object 'AccountingAudit', database 'billing', schema 'dbo'.
I've run this SQL to try and see what permissions are set for this table/user:
select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
 permission_name,
 state_desc
from sys.database_permissions
And this is what I get back:
AccountingAudit dbadmin dbo ALTER   DENY
AccountingAudit dbadmin dbo CONTROL DENY
AccountingAudit dbadmin dbo DELETE  DENY
AccountingAudit dbadmin dbo INSERT  DENY
AccountingAudit dbadmin dbo REFERENCES  DENY
AccountingAudit dbadmin dbo SELECT  GRANT
AccountingAudit dbadmin dbo TAKE OWNERSHIP  DENY
AccountingAudit dbadmin dbo UPDATE  DENY
AccountingAudit dbadmin dbo VIEW DEFINITION DENY
AccountingAudit dbadmin dbo VIEW CHANGE TRACKING    DENY
Seems like it should be working right?
The SELECT call I'm making is a very basic SELECT * FROM AccountingAudit, from within SSMS. I'm not doing any special sp_executesql or anything like that.

I've tried explicitly granting permission:

GRANT SELECT ON [dbo].AccountingAudit TO dbadmin
This has no effect (why would it, the query above already shows it's granted! 
I've searched through stackoverflow.com and elsewhere, and cannot find anything I haven't tried yet. I'm wondering if it has something to do with how the schemas are setup. (At this point I know very little about schemas.)
Any ideas? Thanks!
sql-serversql-server-2008-r2permissions
Share
Improve this question
Follow
asked Oct 25, 2011 at 0:56
Mason G. Zhwiti's user avatar
Mason G. Zhwiti
32311 gold badge33 silver badges88 bronze badges
Add a comment
2 Answers
Sorted by:
Highest score (default)
12

I'm not sure here, but I'm going to go out on a limb. I think your issue might be with your DENY CONTROL record. See here about half way down the page:

Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database will not be able to connect to that database.

I realize that example is for a database, but take it one more granual level. A DENY CONTROL on a table will deny all privileges on it, I'm guessing. Do a REVOKE CONTROL to get rid of that and see if that fixes your issue.

If so, you'll have to place the user in a database role or deny them the explicit privileges against the table.

Share

Improve this answer
Follow
answered Oct 25, 2011 at 1:10
Thomas Stringer's user avatar

  Thomas Stringer41.6k99 gold badges115115 silver badges154154 bronze badges 

1Thank you! Initially in my experimenting I did discover that if CONTROL was not denied, then they could SELECT. But in reading BOL I had incorrectly interpreted this to mean I was giving the user full control over the table. I see now that as long as I don't deny them CONTROL, I can still keep the other permissions (INSERT, DELETE, etc) at the DENY level, and achieve the permission levels I want. Thanks! –

Mason G. Zhwiti
 Oct 25, 2011 at 19:24

This is a subtlety that I upvoted, despite not solving my problem, that I feel most would overlook. Separately, I have found if using Active Directory groups, if you have changed group membership, repadmin /syncall doesn't necessarily fix issues and I found rebooting the server fixed the problem. Still looking for less of a sledgehammer approach, though. –

John Zabroski
 Apr 5, 2019 at 15:09
Add a comment
1

Use Ken Fisher's sp_DBPermissions stored procedure to look at the permissions.

Make sure DENY CONTROL is not applied to the table, in addition to the common DENY SELECT, DENY INSERT, DENY UPDATE, DENY DELETE and DENY REFERENCES.

If the SELECT statement contains table-valued functions, make sure there is either an EXECUTE AS OWNER on the table-valued function or a GRANT EXECUTE on it (and no DENY EXECUTE!). If this is the case, read the error message more carefully as it likely won't say the SELECT permission was denied on the table, but instead something about EXECUTE being denied.

If the user is an AD user or group, use the following script to determine the user's login_token(s):

EXECUTE AS LOGIN = 'EXAMPLEDOMAINJOHN.DOE';

SELECT * FROM sys.login_token;

REVERT;

Look at the actual execution plan. If the error is inside a stored procedure with SET NOCOUNT ON;, then the actual execution plan will give you insight you might not pay attention to by just looking at the Messages tab in SSMS, since "Rows affected" may be outside your control.

Look for triggers or temporal tables.

You can compile the statement as a stored procedure and SSMS "View Object Dependencies", as well as the tricks outlined by Svetlana Golovko in Different Ways to find SQL Server Object Dependencies

Use SQL Server Profiler Security event "Audit Schema Object Access Event" and the columns "TextData" and "Success" to track which objects SQL Server is evaluating permissions on. - I have seen situations where there are two rows emitted for this event, and one value says Success=1 and the other says Success=0. In this scenario, the only solution I have found to work is to reboot the server. Even running repadmin /syncall did not fix the problem, neither did starting and stopping the application (and therefore the connection pool).

Determine the effective permissions for the login:
-- '' is a domain user in the group you wish to test
EXECUTE AS LOGIN = '';
SELECT * FROM fn_my_permissions('Database.Schema.Table', 'OBJECT');

REVERT;

If the user is tied to an AD user or group, consider running repadmin /syncall to force any changes made in active directory to sync across your domain controllers. - If someone knows of a good way to compare the current values of two domain controllers, please let me know.

Before considering a hard reboot of the whole system, try killing all active connections for that user. The reason is the user gets their windows token from the DC which includes their groups. The token won’t get updated until the user gets a new token – usually by logging off and then logging back on.

Hard reboot the system. It has worked for me. Still not 100% sure why yet. ONLY DO THIS IF YOU CAN SURVIVE DOWN TIME! BE CAREFUL ABOUT DOING THIS WHILE YOU HAVE LARGE OUTSTANDING TRANSACTIONS!