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.

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:

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

when running

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

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 permisions 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 permisions. How to find what causes the error on those 2 tables?

share|improve this question
3  
Are the statements run dynamically within the body of the procedures? –  Mr.Brownstone Feb 22 '13 at 12:47
    
I guess that is the idd the problem, I'll test to see if you are right. –  peer Feb 22 '13 at 12:50
4  
Ownership chaining doesn't apply to dynamic SQL. –  Martin Smith Feb 22 '13 at 12:58

1 Answer 1

up vote 0 down vote accepted

The difference between the two tables and the other tables was not in the table it self, but in how the storedprocedures 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.

share|improve this answer

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.