question

JohnsonMichael-4593 avatar image
0 Votes"
JohnsonMichael-4593 asked AmeliaGu-msft commented

AD Group permissions not working after restore to a different server

I have a production server, SQLServer1, with a database Database1.
On the SQLServer1has an AD group, Contoso\ADGroup1.
Contoso\ADGroup1 is disabled on SQLServer1.
In Database1, Contoso\ADGroup1 has the database role of db_datareader and a custom database role of db_executeSP (this allows the execution of stored procedures AND view the definition).

I have a non-production server, SQLServer2. Database1 is often restored on SQLServer2
On the SQLServer2 has the Contoso\ADGroup1.
Contoso\ADGroup1 is enabled on SQLServer1.
Within SSMS, I can look at the properties of Contoso\ADGroup1 and see that it has permissions to Database1 and database roles of db_datareader and sp_executeSP.

When Contoso\User1, who is in the Contoso\ADGroup1, tries to look at tables or stored procedures on SQLServer2.Database1 they are not presented in SSMS.
I can open the properties of Contoso\User1, uncheck the Database1, save, open properties to Contoso\User1 and check Database1 and add roles db_datareader and sp_executeSP. It works fine.

Does anyone know why the disconnect after the restore? These are NOT SQL accounts with SIDs, they are AD groups and AD accounts... should be seamless unless.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi JohnsonMichael-4593,

How are things going on?
Did the answer help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

To start somewhere, do this on the production box:

EXECUTE AS USER = 'Contoso\User1'
go
SELECT * FROM sys.tables
go
REVERT

What come closest at hand is that there is DENY in the mix. If you drop the user from the database and add it back, that DENY will no longer be there.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.