2

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 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? – 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
0

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, privacy policy and cookie policy

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