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.