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