I attempted to revoke delete permissions to most users in a database by using the deny and revoke sql commands and checking the deny box for delete when adding the table as a securable in the UI.
When I did that, the users are now not able to select from any table in the database. They have select permission granted in the UI for all tables including the table I am trying to revoke delete permissions to.
I tried to recreate what I did on a backup copy of the database and I have not been able to deny select on the tables.
The only deny permissions I found on any of the users were deny delete on all objects and deny on the one table I am trying to prevent delete on.
I did not find any deny selects on any users or roles.
No users are part of the db_denydatareader role.
What is the best way to find out why the users are not able to select from any table in the original database?
I am using SQL Server 2008.
Best Answer
I would check if the users are part of the db_denydatareader role, check for deny permissions on the tables schema, and also for a deny permission on the public role. I would suspect these are the most likely culprits.