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