Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

A domain user (User1) belonging to the roles public, db_datareader, and db_datawriter on a particular database is getting the following message when he attempts to select from any tables on that database (including newly created test tables):

The SELECT permission was denied on the object 'table1', database 'database1', schema 'dbo'.

This user works perfectly on several of other databases on the same server. It is not explicitly denied permissions at the database or the table level (that I can find at least).

I've tried (to no avail):

  • Removing and remapping the user to the database
  • Assigning the user to the db_owner role
  • Specifically granting select privileges at the database and table levels

The only thing that has made a difference is setting the user to the sysadmin server role - but this is not viable for production.

How else can I troubleshoot this?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Most likely that the user is a member of a group that has permissions denied - denies take precedence over grants.

Run the following to see the permissions;

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
 where major_id = object_id('table1')
 and class = 1

Could it be you're using ownership chaining, and hitting this issue?

Hope this helps

share|improve this answer
    
the above query is one reason i still like to use sp_helprotect –  Nick Kavadias Feb 6 '10 at 1:38
    
Your diagnosis was correct, but the query didn't actually show this. Possibly because it was a database-level setting and not table level? –  sh-beta Feb 8 '10 at 17:41

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.