Sql-server – Receiving “The SELECT permission was denied on the object” even though it’s been granted

permissionssql serversql-server-2008-r2

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!

Best Answer

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.

Related Solutions

Sql-server – The UPDATE permission was denied on the object

I've set GRANT on all the securables.

You want to assign roles to write data for a specific database. When I read this correct you clicked everthing to do this.

Now I think that your user is member of the role db_datawriter and db_denydatawriter. Please remove the role db_denydatawriter and try it again.

Sql-server – Execute permission denied on object sp_start_job

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.) So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

Create the procedure in msdb.

Create a user for this user's login in msdb:

USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

Grant the user execute privileges on the stored procedure:
```
GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;
```

Test it - either by calling the procedure from another database:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

Result should be:

I am really...
---------------
sysadminaccount

Validate that this doesn't expose anything else in msdb to this user:
```
USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;
```
Result should be...

Msg 229, Level 14, State 5, Line 21
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

Best Answer

Related Solutions

Sql-server – The UPDATE permission was denied on the object

Sql-server – Execute permission denied on object sp_start_job

Related Question