I recreated your situation. I created the following items:
- Database TEST
- SQL Login DOMAIN\USER
- SQL Login DOMAIN\GROUP
- Database User USER (linked to SQL Login DOMAIN\USER)
- Database User GROUP (linked to SQL Login DOMAIN\GROUP)
- Assigned Database User USER no database roles
- Assinged Database User GROUP the database roles db_datareader and db_datawriter
I then queried the TEST database with the Windows login DOMAIN\USER. Because the DOMAIN\USER is also memmber of DOMAIN\GROUP the user is allowed to SELECT from all tables in the TEST database.
You are writing that the DOMAIN\USER is unable to select any data. Correct?
Do you have any orphaned users on the database level? Follow these steps to check.
- Open SQL Server Query Window
- Change database to YOUR database
- Run the command
exec sp_change_users_login 'Report'
- Check the output. You might find that one of your database users is not properly linked to a SQL Server Login.
To fix:
In the same query window for YOUR database execute the following command
exec sp_change_users_login 'Update_one', '<DATABASE USER>', '<SQL LOGIN>'
E.g. if your database is YOUR_DB, the SQL Login (SQL Server level) is YOUR_SQL_LOGIN and the database user you are relinking is YOUR_DB_USR, then the command would be:
USE YOUR_DB
GO
exec sp_change_users_login 'Update_one', 'YOUR_DB_USR', 'YOUR_SQL_LOGIN'
If everything is ok and you don't have any orphaned users, then try checking the permissions of the database user in question.
- Does that user have an explicit DENY set somewhere?
- Is the account disabled at the SQL Server Login level?
- Do you have any error messages?
I hope this gives you some ideas on what to check.
