Friday, February 15, 2008

Script to fix SQL logins

When copying a database from one server to another server, the logins/users can get really screwed up. This is because users are database specific, and logins are server specific and each user must map to a login. Moving or copying a database brings the users with it, but not the logins.

An intelligent, well designed, database management system would take care of this for you. By default, it would map users to logins that have a login with the name, and create disabled logins for users that don't. What SQL Server does is create all the users that don't have logins as not mapping to anything. Sometimes it maps users that have a login, and sometimes it doesn't (I still haven't found the pattern).

This means after copying a database to a new server, typically people cannot log in, and a good deal of work needs to be done if you haven't seen the problem before. The most frustrating is seeing the user and login both being created correctly, but not mapped together. You can fix this easily with "EXEC sp_change_users_login 'AUTO_FIX', @name", but only if you understand the issue. Even if you know exactly what is wrong, fixing this manually takes too long.

I want to post the scripts I use to fix this, if only so I have a good reference. I hate copying/pasting SQL out of Outlook, and I need a quick way to get this script from any computer across the net. So feel free to use it if it helps you too. This should work in most version and environments with minimal changes.


DECLARE @name varchar(128)

DECLARE login_cursor CURSOR FOR

SELECT name FROM [ServerDBWasFrom].master.dbo.syslogins

WHERE name NOT IN (
SELECT name
FROM master.dbo.syslogins
WHERE name IS NOT NULL
) AND name IS NOT NULL

OPEN login_cursor

FETCH NEXT FROM login_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Creating login for ' + @name
EXEC sp_addlogin @name, 'password1'
EXEC sp_change_users_login 'AUTO_FIX', @name
PRINT ''
FETCH NEXT FROM login_cursor INTO @name
END

CLOSE login_cursor
DEALLOCATE login_cursor
GO



--This script will fix any orphaned logins (such as those not created using the first script)

DECLARE @name varchar(128)

DECLARE login_cursor CURSOR FOR
SELECT name FROM master.dbo.syslogins WHERE name IS NOT NULL

OPEN login_cursor

FETCH NEXT FROM login_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT 'Autofixing login for ' + @name
EXEC sp_change_users_login 'AUTO_FIX', @name
PRINT ''
FETCH NEXT FROM login_cursor INTO @name
END

CLOSE login_cursor
DEALLOCATE login_cursor

GO