When you rename a domain group in Active Directory, you are going to have some issues related to the login in SQL Server

Few days back I came across an interesting scenario related to a login in the SQL Server replication.

The Active Directory group used to grant access to a snapshot publication does not show up in the Publication access list (PAL) anymore when viewing the properties of the publication. However, the AD group does show up in the MSPublication_Access table for the snapshot publication in the Distribution database.

There is a disparity between what the Replication property settings and what is actually in the MSPublication_Access table. When I try to add the group to the PAL in the GUI, it doesn't give any error message and completes. But when I again check the PAL, it doesn't show up. Does it sound weird?

Now the question is what could have caused such a weird scenario ?

On investigating in a real life customer scanrio, what I found something very interesting.

1) The login was changed in the AD sometime back. The change was that the login was renamed but not deleted/recreated. So the SID remained same for the changed login in the AD. But because of this change in the AD, SQL Server was never aware of the change of the name. The login with the old name still exist in the server and mapped to the same SID.

2) Another interesting finding, somehow the login was deleted and recreated at the SQL Server level. So when the login was created again at the SQL Server, the new name was created on the SQL Server but the same Old SID was associated with it.

But the entry for the login remained there in the mspublication_access table. This login was never removed during the deletion of the login from the SQL Server. Afterwards whenever they used GUI to check the PAL, it was unable to find the login name from the SQL Server logins and hence didn't show up in the PAL list. But whenever you try to add the login in the PAL, it was able to find an existing record with the same SID and hence failed to add the login. But it never gave any error message.

To workaround the above weird scenario, go ahead and Delete the record manually from the MSPublication_Access table in distribution database. Then again add the login to the SQL Server and Publication Access List (PAL).