I have Windows server 2008 with MS SQL Server 2008 R2 standard edition.
On Windows server I created a local group and added domain users into it.
On SQL server I create a login mapped into the windows group.
For this login I created user mappings for several databases with public roles.
Each databases table has grant to select for public role.
The problem is the domain users can select data from all tables except one database.
I compared setting for all databases. They are the same.
Is it a way to check security setting like I can check a database consistency using DBCC CHECKDB or something?
After using "SELECT * FROM fn_my_permissions ('Accounts', 'OBJECT') ORDER BY subentity_name, permission_name;" the problem disappeared. I used a lot of other functions to check the security settings (e.g. sp_helpuser, sp_helprolemember), so I am not sure which one really helped.
Anyway somehow the problem got fixed.