Wednesday, October 15, 2008

Transfer login to different server

IN Sql server 2000, To transfer the login with password to another sql server , you need to run the below sql

print ''
print '-- Script for restoring standard sql logins.'
print '-- As found on ' + @@servername + ' per ' + cast(getdate() as char)
select cast('exec sp_addlogin ''' + upper(a.loginname) + ''', ' as varchar(40)),
convert(varbinary(64),b.password),
cast(',''' + 'YOURDBSNAME' + ''', ' +
'''' + 'us_english' + ''', ' as varchar(35)),
convert(varbinary(64),b.sid),
',''skip_encryption'' '
from master.dbo.syslogins a,
master.dbo.sysxlogins b
where a.isntname = 0
and a.sid = b.sid
and b.name not in ('sa','repl_publisher','repl_subscriber')
order by b.name
Print ''

IN SQL server 2005, you use this SP_HELP_REVLOGIN

to create this sp, follow this microsoft link http://support.microsoft.com/kb/918992/