Update SID in SQL Server

Question

My user was deleted from the Active Directory and recreated. When running the select suser_sid, I notice that it shows the old SID.

Is there a way to update it to my new SID? I am DBA Admin on SQL Server

Answer 1

You can't ALTER the SID of a LOGIN, no (notice there is no SID option in ALTER LOGIN (Transact-SQL)). You'll need to recreate the LOGIN by dropping the old one and recreating it and giving it the old permissions.

This will orphan any associated USERs, so you will then need to remap them to the new LOGIN using ALTER USER in the databases it has a login in:

ALTER USER [Domain\User] WITH LOGIN = [Domain\User];