Updating SQL Server 2008 LOGIN SID

Question

If an Active Directory SID doesn't match the SQL Server 2008 Login SID (sys.server_principal) there doesn’t appear to be a way to ALTER/UPDATE that value. ALTER USER will allow you to remap a USER to a LOGIN, but ALTER LOGIN will not allow you to remap a LOGIN to Active Directory so to my question…

Is there any way, documented or otherwise, to update the LOGIN SID with the AD SID besides dropping and recreating the login? I am using OPENROWSET to get the AD SID value, but I can't find a way to update the LOGIN SID (mostly because I don't think it can be done)

By the way, I am aware that ad-hoc queries to the system tables are not allowed (because I tried that already).

Answer 1

I'm certain there is not. You can use sp_change_users_login to re-map the SID between a 'user' and a 'login' - but reading your question it sounds like you already know that.

While you can use sp_validatelogins to find out if there are any sql login's that are orphaned (not mapped to valid windows account) the only process Micorsoft offers is what you've already guessed... dropping the orphaned login, and creating a new one. All documentation I have on resolving orphaned logins after they've been identified is to "drop the login".

The lack of this ability to me is probably more of a security concern than a missing "feature". I can imagine a variety of scenarios where changing out the login SIDs would be considered very shady behavior. Imagine I wanted to do something and make it look like it was another user. Swap out the SIDs... do my nefarious behavior... swap them back.