Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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
1  
Thank 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 '11 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.