AD Group permissions not working after restore to a different server

Johnson, Michael 1 Reputation point
2021-08-03T17:25:32.033+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,906 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2021-08-03T18:42:10.45+00:00

    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.

    0 comments No comments