David Truxall

Search

Go

• Blogs

Login

Subscriptions

• RSS 2.0
• Atom 0.3
• Contact

News

# re: Sql Server Fix Logins @ Wednesday, June 06, 2007 4:43 PM

Yeah, it's quite a pain to move/synch accounts between servers. In case you don't know, sp_change_users_login does not truly "synchronize" the logins and users between development and production (or between primary and standby in a DR scenario).

How sp_change_users_login is that it runs a CREATE LOGIN statement to create the new login (if you use ATUO_FIX, for example) and then it remaps the database user account to the new login. That is NOT synchronizing the logins. Why?

Well, when sp_change_users_login ran CREATE LOGIN, it created a login with a completely new, random SID. If you need to now backup the development database and restore it on the production server again (probably more likely in a DR scenario) then you've got to do this whole process all over again :(

I suggest you only use sp_change_users_login in one-way transfers that will neve be updated/restored-on-top-of again from the original server. sp_change_users_login is fine when you want to take a copy of production, move it to QA, and then throw it away (i.e., delete the QA database and never restore a production copy over it again).

For anyone who has to repeatedly take backups from ServerA and restore them to ServerB, I suggest you look to sp_help_rev_logins which is a proc from MSFT PSS designed to keep servers in sync. Search the MSFT KBs for it...

Scott Whigham

# re: Sql Server Fix Logins @ Thursday, June 07, 2007 2:33 PM

Alternatively, to fix all orphaned users in the current database:

DECLARE @UserName sysname
DECLARE OrphanedUsers CURSOR LOCAL FAST_FORWARD
FOR
SELECT
[Name]
FROM
dbo.sysusers
WHERE
IsSqlUser = 1
And
Sid Is Not Null
And
0x0 <> Sid
And
SUser_SName(sid) Is Null
ORDER BY
[Name]

OPEN OrphanedUsers
FETCH NEXT FROM OrphanedUsers INTO @UserName
WHILE 0 = @@Fetch_Status
BEGIN
PRINT 'Orphaned user: ' + @UserName
EXEC sp_change_users_login 'UPDATE_ONE', @UserName, @UserName
FETCH NEXT FROM OrphanedUsers INTO @UserName
END

CLOSE OrphanedUsers
DEALLOCATE OrphanedUsers

Richard