SQL Server 2008 user and group permissions behavior weirdness I have a strange situation:  

An AD user account has a login to a SQL Server 2008 database without any roles assigned to that user.

That same database has a login for an AD group with datareader/datawrites roles.

The AD user is a member of the AD group.

It looks like if ONLY the user-permissions are used. He is not able to do anything with the database.  

This doesn't make sense to me at all.  

I would presume that the sum of all roles (from user and group) would be in effect.  

As I'm not really a SQL Server guy I am at a loss to explain this. Can someone clarify how user and group derived permissions interact ?

P.S. The user has been a member of that AD group for years. This is not a case of a recent addition to an AD-group not being synchronized in AD.
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', '&lt;DATABASE USER&gt;', '&lt;SQL LOGIN&gt;'

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.