Permission Errors AD Group after Restore, Permissions missing in SSMS

Question

I have ran into this a couple times in the past, and always been at a bad time and want to figure out really what is happening, anyone else hit this? It seems that the sid's get out of sync on AD groups, for instance yesterday a restore was done to a test server a user in an AD group reported no permissions or an error. Yesterday the user was getting the Database inaccessible message when she tried to connect to through SSMS. I used SSMS to give the ad group db_datareader permission and closed the window. No luck, she still could not access. I ran a script I use to apply the permissions, went to the Security icon in SSMS, looked at the AD Group under user mapping, and they did not have connect again to the database. What I do to fix this is drop the user from the database, and re-add them which is what makes me thing the sid's are out of whack, but they are an AD group (we have 1 domain), and should not be the case....anyone?

Answer 1

Hallo Abeljda,

basically it can not be what you describe. The SId of a user or an AD-group is always UNIQUE in AD and will be used with intergrated authentication as you suppose. We are doing such workflows as you describe nearby every day and did never run in such a probem.

I assume that you may have two separate AD's in your network (e.g. DEV and PROD / BUSINESS)?
Otherwise I do not really have an idea what will happen with your databases.

Just an idea to confirm my assumption:

Next time you run into these problems please run the following statement in SSMS:

USE [YourDatabase]
GO

EXEC sp_change_users_login 'report'

The list you will get display all database users which do not have a corresponding login. If your AD-Group will be listed it is exactly what I assumed :)

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 2

Hello, I appreciate your reply. I agree it should not happen but I have seen this in multiple environments at multiple sites I have worked at and wondered if others have encountered.

Answer 3

Hello, I appreciate your reply. I agree it should not happen but I have seen this in multiple environments at multiple sites I have worked at and wondered if others have encountered.

Abeljda,

i think nobody as made the experiences you've described. We transfer so often databases from PROD to UAT-Environments and back and cross to other SQL Systems (nearby daily) and had never such a strange behaviour.

This will only occure with SQL Users and ... as I mentioned ... with separated AD (e.g. DEV-AD and PROD-AD)..
Post the result of my suggested solution - than we can think about the next step :)

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de