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!