Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Migrating Logins To Another Server

By Gregory Larsen,

As part of the normal work a Database Administrator (DBA) will be required to migrate databases between servers. One of the reasons for a migration might be caused, because you are moving an application from a quality assurance (QA) environment to a production environment. Another reason might be your current database server hardware has reached the replacement date and you need to migrate your databases from the current, out-dated server to a new server. Yet another reason might be you are migrating your application from SQL Server 7.0 to SQL Server 2000. For what ever the reason you will more than likely have to deal with migrating not only the data, but the SQL Server logins that access that data as well.

There are a number of different ways to migrate SQL Server logins. You can manually re-enter all the existing login on the new server. You can used DTS to transfer logins. There are probably a number of other ways to transfer logins. This article will discuss one of those other ways to streamline the migration of SQL Server logins by using the stored procedure (SP) sp_help_revlogin.

What does the sp_help_revlogin do?

The sp_help_revlogin SP is a Microsoft provided utility that generates a TSQL script for to migrate logins from one server to another. This SP will not only copy the existing logins, but it will also copy the passwords and Security Identification Numbers (SID) associated with SQL Server Authenticated users.

Why you might want to use sp_help_revlogin

When you move a database from one server to another the entire database and all the system tables associated with it are also moved. One of those system tables is the sysusers table. The sysusers table contains all the users, groups, and roles that have access to the database. In order for a person to be able to access the database they must have two things. The first thing is the must have is a SQL Server login. The second thing they need is to be defined as a user in the database. So basically if you copy a database from one server to another, and the users of the database don=t have SQL Server logins then these database users become orphan users. An orphan user is a user in a database with a SID that does not exist in the syslogins table in the master database. Also if the SID stored in the database sysusers table, differs from SID stored in the syslogin table for the matching database user, then the database users is also considered an orphan User. If you retain the original SID for logins, when a user databases is migrated you will not have a problem with orphan users.

By using the sp_help_revlogin SP, you can move logins from one server, and create the login entries in the new server and retain the sid. By making sure the all logins exist on the new server prior to copying a database, you will ensure there will be no orphan users after you complete the database copy The sp_help_revlogin SP helps DBA’s quickly move logins from one server to another.

How the sp_help_revlogin Works

The process of moving logins requires not only the sp_help_revlogin SP, but also the sp_hexidecimal SP. These scripts and more information about migrating logons can be found in the following Microsoft Knowledge Base article http://support.microsoft.com/default.aspx?scid=kb;en-us;246133. I have also included a script to create both of these SP’s at the bottom of this article. Let’s review the sp_help_revlogin process.

The easiest way to execute the sp_help_revlogin script is via Query Analyzer. The SP accepts a single optional parameter, @login_name. If you only want to generate a single login, then you can pass a valid login to sp_help_revlogin, and it will generate a script to create the specified login. When @login_name is not specified this SP generates a TSQL script to create all the logins on the server.

Based on whether the @login_name was specified, the sp_help_revlogin SP, builds a cursor called login_curs that contains information in the sysxlogins table in the master database for a single login or all the logins on the server, except ‘sa’. The columns placed in the cursor are sid, name, xstatus, and password. The xstatus column will be used to determine, if the login is a Windows or SQL Server Authenticated users, as well as whether the login has been denied access. The sid and password will be used to make sure the new login on the new server retains the sid and password as the original server.

Next the sp_help_revlogin SP processes through the login_curs cursor one login at a time in a while loop. Inside the while loop the SP determines if the login is a Windows authenticated user or group (xstatus = 4) or not. If the user is a Windows authenticated user then a sp_grantlogin statement is generated. For the SQL Server authenticated users a sp_addlogin statement, with the skip_encryption parameter is generated. The skip_encryption parameter is used on the sp_addlogin statement to tell SQL Sever that an encrypted password is being specified when the login is being created, so the password should not be encryted.

For SQL Server authenticated users, the SP needs to determine the sid and password of the login. Since the sid and the password as stored as binary numbers, the sid and password values need to be converted to a string that contains the hexadecimal representation of the binary number. The sp_hexadecimal SP is used to do this binary number to hex string conversion.

When the sp_help_revlogin execution is complete the script to create the logins should be displayed in Query Analyzer results pane. All you need to do to add these logins to a server would be just to copy the script from the results pane and run it against the new server where you want to add the logins.

Other uses for the sp_help_revlogin SP

Using the sp_help_revlogin as it comes from Microsoft will move all logins or a single login. Maybe youb only want move all the logins for a single database. It is easy to modify the code that creates the “login_curs” to identify only the users you want to move.

Often I am only moving a single database. When I do that I modify the creation of the “login_curs” cursor command to look like so:

SELECT sid, name, xstatus, password FROM master..sysxlogins a join
Your_DB..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> 'sa' 

By using this logic instead, the sp_help_revlogin will only generate sp_grantlogin, and sp_addlogins for users of the database Your_DB.

Another possible use might be to generate the script for all logins, then delete the logins you don’t want to move. This method allows you to be selective in the logins that will be created on the new server.

Conclusion

Here I have shown you a method to quickly generate a script that will move all logins from one server to other, as well as offered you ideas on how to move a sub set of users. I’m sure you can find other uses for this sp. Using sp_help_revlogin is much easier than re-entering all login on a new server. Plus re-entering logins would most likely establish new password for each login moved, where as this sp allows you to move the passwords. Moving the passwords from one server to another is more users friendly. SQL Server users will be grateful that they don’t have to remember a new password, plus it makes the migration a little more transparent to the SQL Server users.

Script to Create sp_hexidecimal and sp_help_revlogin

----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF' 
WHILE (@i <= @length) 
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin 
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR 
SELECT sid, name, xstatus, password FROM master..sysxlogins 
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR 
SELECT sid, name, xstatus, password FROM master..sysxlogins 
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr 
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr 
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr 
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN 
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
ELSE 
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr 
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO
----- End Script -----
Total article views: 16352 | Views in the last 30 days: 29
 
Related Articles
BLOG

Migrating SQL Server logins

Once upon a migration I would use sp_help_revlogin to migrate logins and remap the SQL server logins...

SCRIPT

Create_sp_help_revlogin_DB

Enhanced sp_help_revlogin

SCRIPT

Script Logins

Rewritten sp_help_revlogin. Why? Because sp_help_revlogin is annoying.

FORUM

moving databases,logins

moving database,logins

FORUM

Auditing login and Database Information on servers

login and database info

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones