|
A step by step artcile which describes how to transfer logins and the passwords between SQL Servers. This is a very good article for migrating the whole sqlserver and migrating databases between sql servers. Method 1 (Transer logins & Passwords between two SQLServer 2000 Versions) 1) Take the script(sp_help_revlogin) from the below link & execute the script in the source sql server. http://support.microsoft.com/kb/246133 (Opt for Method 1(servers that are running SQL Server 2000)) Modify some lines as shown in below only if you are moving/migrating only selected database(s) else go to step2 ie; 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 As DECLARE login_curs CURSOR FOR
SELECT a.sid, a.name, a.xstatus, a.password FROM master..sysxlogins a join Migrating_UserDB..sysusers b on a.sid=b.sid
WHERE srvid IS NULL AND a.name 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT a.sid, a.name, a.xstatus, a.password FROM master..sysxlogins a join Migrating_UserDB..sysusers b on a.sid=b.sid
WHERE srvid IS NULL AND a.name = @login_name
Note: Here we are joining the master with Migrating_UserDB to get only the logins, users specific to the unser database which we are migrating/moving. Make sure to change the necessary changes(As shown above) in the script
2) Copy the result of the above procedure & save it
3) Move the needed database(s) to destination server by backup/restore or attach/detach
4) Exec sp_change_users_login 'Report' in the restored user db to find the orphan users.
5) Exec the above saved result from step1 in the destination server, in the restored
user database.
6) Once again Exec sp_change_users_login 'Report' in the restored user db to find the orphan users.
7) If any more orphan users are existing run the below script to link the orphan users
to the newly created logins from the result script of step1
-------------- script to correct login IDs --------
declare @login sysname
declare log_curs cursor for
select name from sysusers where upper(name) 'DBO' and status=2 open log_curs
fetch next from log_curs into @login
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@@FETCH_STATUS -2)
BEGIN
exec sp_change_users_login 'Update_One', @login, @login
Print 'Login Updated: "' + @login + '".'
END
FETCH NEXT FROM log_curs INTO @login
END
CLOSE log_curs
DEALLOCATE log_curs
---------------------------------------------------
8) Once again Exec sp_change_users_login 'Report' in the restored user db to find the orphan users. Make sure there are no orphan users.
Method 2 (Transer logins & Passwords between SQLServer 2000 & SQLServer 2005) 1) Take the script(sp_help_revlogin) from the below link & execute the script in the source sql server. http://support.microsoft.com/kb/246133 (Opt for Method 2(from SQL Server 2000 to SQL Server 2005)) Modify some lines as shown in below only if you are moving/migrating only selected database(s) else go to step2 ie; DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND [name] 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND [name] = @login_name As DECLARE login_curs CURSOR STATIC FOR
SELECT a.sid, [a.name], a.xstatus, a.password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins a join Migrating_UserDB..sysusers b on a.sid=b.sid
WHERE srvid IS NULL AND [name] 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT a.sid, [a.name], a.xstatus, a.password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins a join Migrating_UserDB..sysusers b on a.sid=b.sid
WHERE srvid IS NULL AND [name] = @login_name
Note: Here we are joining the master with Migrating_UserDB to get only the logins, users specific to the unser database which we are migrating/moving. Make sure to change the necessary changes in the script
2) Copy the result of the above procedure & save it
3) Move the needed database to destination server by backup/restore or attach/detach
4) Exec sp_change_users_login 'Report' in the restored user db to find the orphan users.
5) Exec the above saved result from step1 in the destination server, in the restored
user database.
6) Once again Exec sp_change_users_login 'Report' in the restored user db to find the orphan users.
7) If any more orphan users are existing run the below script to link the orphan users
to the newly created logins from the result script of step1
-------------- script to correct login IDs --------
declare @login sysname
declare log_curs cursor for
select name from sysusers where upper(name) 'DBO' and status=2 open log_curs
fetch next from log_curs into @login
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@@FETCH_STATUS -2)
BEGIN
exec sp_change_users_login 'Update_One', @login, @login
Print 'Login Updated: "' + @login + '".'
END
FETCH NEXT FROM log_curs INTO @login
END
CLOSE log_curs
DEALLOCATE log_curs
--------------------------------------------------- 8) Once again Exec sp_change_users_login 'Report' in the restored user db to find the orphan users. Make sure there are no orphan users. Method 3 (Transer logins & Passwords between two SQLServer 2005 Versions) 1) Take the script(sp_help_revlogin) from the below link http://support.microsoft.com/kb/314546/ Modify some lines as shown in below only if you are moving/migrating only selected database(s) else go to step2 ie; DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
As DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
join Migrating_UserDB..sysusers u on u.sid = l.sid
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
join Migrating_UserDB..sysusers u on u.sid = l.sid
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
Note: Here we are joining the master/sys.syslogins with Migrating_UserDB to get only the logins, users specific to the user database which we are migrating/moving. Make sure to change the necessary changes(as shown below) in the script.
2) Copy the result of the above procedure & save it
3) Move the needed database to destination server by backup/restore or attach/detach
4) Exec sp_change_users_login 'Report' in the restored user db to find the orphan users.
5) Exec the above saved result from step1 in the destination server, in the restored
user database.
6) Once again Exec sp_change_users_login 'Report' in the restored user db to find the orphan users.
7) If any more orphan users are existing run the below script to link the orphan users
to the newly created logins from the result script of step1 -------------- script to correct login IDs --------
declare @login sysname
declare log_curs cursor for
select name from sysusers where upper(name) 'DBO' and status=2 open log_curs
fetch next from log_curs into @login
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@@FETCH_STATUS -2)
BEGIN
exec sp_change_users_login 'Update_One', @login, @login
Print 'Login Updated: "' + @login + '".'
END
FETCH NEXT FROM log_curs INTO @login
END
CLOSE log_curs
DEALLOCATE log_curs
---------------------------------------------------
8) Once again Exec sp_change_users_login 'Report' in the restored user db to find the orphan users. Make sure there are no orphan users.
Remarks: Review the script output carefully before execution in the destination server. Check the windows domain login accounts if the destination server is in a different domain.
|
0 on the 12th local time in Incheon