]> SQL SIDs 🌐:aligrant.com

SQL SIDs

Alastair Grant | Tuesday 6 July 2010

I accidentally deleted a security group out of AD that was being used as a SQL 2005 login - oops. So I recreated it and added the members back in.

Problem is, this didn't fix my login issues. So I deleted the server login and recreated it. But then I couldn't link the new server login to the existing database logins. Nuts!

As we know from moving databases back in the old days, we can use "sp_change_users_login". Alas, this proc does not work with windows/domain principles/logins. Double drat!

Providing you have SP2 you can use the ALTER USER WITH LOGIN statement to repair.

You will need to delete the server login and re-add it, then run this statement against each database you want to re-link:

ALTER USER [MYDOMAIN\mygroup] WITH LOGIN=[MYDOMAIN\mygroup]

The other easy option is just to go home an ignore it. I found that the servers that I hadn't got round to fixing figured themselves out the following day - sitting around 15 minutes for replication and a restart didn't appear to be enough (being the first thing I tried).

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.