Database Administration
sql-server-2008 security
Updated Tue, 24 Jan 2023 19:00:13 GMT

"The SELECT permission was denied" only for some of my tables

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?


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.