Cannot drop old domain user in SQL Server 2008 R2

Question

I would like to ask your help to re-sync old AD user (let's say AD\username) with new AD user (AD\username - the same name but different SIDs) in SQL Server 2008 R2.

I looked at the question "SQL Server Windows Login - Same Name Different User (SID)" but in my case the old user owns schema and tables in multiple DBs and cannot DROP the old username.

Can anyone help, please?

Thank you in advance CI

Answer 1

You can use a few methods:

• sp_help_revlogin was created for SQL Server 2005 and is still used quite often. This support article explains it in-depth and shows how to use it.
• dbatools.io is an open source community tool suite created in PowerShell by some very generous folks. There are a few blogs showing how to use their scripts, but here is one to handle the logins.
• Find other scripts which do the same thing, like this one which is for AGs but still applies.