Migrating Logins from One SQL Server to Another

Whenever you'll migrate SQL Server, 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; you can migrate logins manually (if you know the password) or using this tricky method...

The logic is really simple: read the values from sysxlogins on the source server and generate a script that should be run on the destination server.


PRINT 'exec master..sp_configure ''allow updates'',1'
print 'go'
PRINT 'reconfigure with override'
print 'go'

set nocount on

SELECT 'INSERT INTO sysxlogins( sid, xstatus, xdate1, xdate2, name, password, dbid, language) VALUES' + char(13) + char(10)
+ '(',sid,','
+ convert(varchar(20),xstatus) + ',''' + convert(varchar(25),xdate1) + ''',''' + convert (varchar(25),xdate2)
+ ''',''' + rtrim(name) + ''',',password,',5,''us_english'')'
from master..sysxlogins
where name not in (
'sa','distributor_admin','distributor_admin')
and name not like '%\%'
and name is not null

print 'GO'
PRINT 'exec master..sp_configure ''allow updates'',0'
print 'go'
PRINT 'reconfigure with override'
print 'go'

Share on Google Plus

About Vittorio Pavesi

    Blogger Comment
    Facebook Comment

1 comments:

Vittorio Pavesi said...

After publishing this post, I found sp_help_revlogin, a Microsoft provided utility that generates a TSQL script 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.

Source: http://www.databasejournal.com/features/mssql/article.php/2228611