1

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.

  • You need to take care: a login is on the server-level - a SQL Server instance has logins. Each database then has users that are linked to those logins. You cannot have a database has a login..... the server has a login, and the database has a user connected to that login..... – marc_s Nov 9 '14 at 9:07
  • @marc_s You are right. I really meant db-users tied to the AD account and to the AD group via the login definition at server level. I struggle to get the terminology right, because I only get to deal with SQL Server once in a blue moon. – Tonny Nov 9 '14 at 10:34
1

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.

  • The problem disappeared when we migrated the DB to SQL2012 some months ago. It must have been some kind of account/role linkage issue that got corrected when permissions where reset in the new db. I have been reading up on MS-SQL a lot lately and came to the same trouble-shooting steps and corrections as you suggest. I'm therefore accepting your answer even though I can't verify this myself anymore (original DB/server are gone). – Tonny Sep 14 '15 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.