In my database there are about 100 tables on 2 tables I get the following error when accessing the database by a SP (all data access uses SP):

The SELECT permission was denied on the object 'XXX' database 'XXX' schema 'dbo'. sql server 2008

I can fix the error by giving grant on the select:

  ON OBJECT::[dbo].[XXX] TO [allsp_user]
  AS [dbo];

when running

select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
from sys.database_permissions

I see only for my 2 tables:

XXX allsp_user  dbo SELECT  GRANT

My problem is that the error comes only on 2 tables, all others are working fine with selects. None of them have a special permissions except the 2 I added the Select Grant. I do not want to Grant the user the select permissions because I do not understand why he needs them, in all other projects and on all other tables it works fine without the permissions. How to find what causes the error on those 2 tables?

  • 3
    Are the statements run dynamically within the body of the procedures? Feb 22, 2013 at 12:47
  • I guess that is the idd the problem, I'll test to see if you are right.
    – peer
    Feb 22, 2013 at 12:50
  • 4
    Ownership chaining doesn't apply to dynamic SQL. Feb 22, 2013 at 12:58

1 Answer 1


The difference between the two tables and the other tables was not in the table it self, but in how the stored procedures where doing the select. In this case the two tables where the only 2 which had some dynamic SQL in them which causes the error. Replacing the dynamic sql fixes the need for Grant select on the tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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