Transfer Logins . - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Transfer Logins .

Author

Topic

admin001
Posting Yak Master

166 Posts

Posted - 2003-07-18 : 06:40:16

Hello again ,

This time I got stuck with a new issue . I installed SQL server Enterprise Edition on a new server which had a clean install of OS . After the installation I restored 5 databases with their BAK files on the New server from production server to do testing .

After the restore i executed the commands like
sp_changedbowner and sp_change_users_login to fix the users.

After executing sp_change_users_login , it displays that login was fixed , but actually it did not fix the passwords . I checked through ODBC and SQL client .

I had to manually reset the passwords as the same in production . So my question is :

Is this problem due to master table not having the updated info. on the userID's and passwords of the DB's restored ?
How do i transfer them ?

Am i missing something that needs to be done when restoring the Databases on a new rebuilt server which has a new master table and system databases ?

Thanks once again .

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2003-07-18 : 08:45:21

Please refer to :
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133
HTH

Franco

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 09:15:47

I thought that was only to "re-synch" the IDENTIY values associate with the "natural key" which is your actual login id.

Which btw is why we have information_schema views...

ok, now I gotta

MOO

and a bovine moo for Mr. Mist

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-18 : 17:08:12

Here is a stored procedure that transfers the accounts and passwords to another server. It uses linked servers. After it has successfully run, you can then run sp_change_users_login to fix the accounts. Just create a linked server on the source server pointing to the destination server. Then modify the below stored procedure so that it references this linked server. I have added the default database option to sp_addlogin but it can be removed. Once modifications are done, just run the stored procedure on the source server after the database has been restored. Then run sp_change_users_login on the destination server.

This stored procedure also checks if the login account already exists. If it does, synchronize the password by dropping the account then recreating it using the one on the source server.


CREATE PROCEDURE isp_Transfer_Logins
AS

SET NOCOUNT ON

DECLARE @login sysname
DECLARE @pwd sysname
DECLARE @new_pwd varchar(255)

DECLARE cur_Users CURSOR FOR
SELECT l.name, l.password
FROM master.dbo.syslogins l
INNER JOIN DTS.dbo.sysusers u ON l.sid = u.sid
WHERE (l.isntname = 0) AND (u.islogin = 1 AND u.isaliased = 0 AND u.hasdbaccess = 1)
ORDER BY u.name

OPEN cur_Users

FETCH cur_Users INTO @login, @pwd

WHILE @@FETCH_STATUS = 0
BEGIN
	-- If the login does not exist on the destination server, then add it.
	IF ((SELECT count(*) FROM DestinationServer.master.dbo.syslogins WHERE name = @login) = 0)
	BEGIN
        	EXEC DestinationServer.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'DBName'
	END

	-- If the login does exist on the destination server, then synchronize the password.
	ELSE
	BEGIN
		EXEC DestinationServer.master.dbo.sp_droplogin @login
		EXEC DestinationServer.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'DBName'
	END


        FETCH cur_Users INTO @login, @pwd
END

CLOSE cur_Users
DEALLOCATE cur_Users

RETURN

Tara

admin001
Posting Yak Master

166 Posts

Posted - 2003-07-19 : 00:33:25

Hello Tara ,

Extermely thanks for your help . I will create a linked server on the source server and execute the script . I will try this option now .

Hi Franco ,

Thanks . But I did try executing that script first from Microsoft , but it did not work for some logins . I had to reset the passwords manually on the new server even after the fix users login script.

Somehow it did not transfer all the passwords correctly . Or maybe i made a mistake somewhere .

Anyways let me give a try to both the options .

Thank you all for your great support and help .

Admin001.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-19 : 00:56:52

Let me know if you have any problems with it or have any questions. I probably won't be checking SQLTeam until Monday, so I'll respond then if you do have any comments.

Tara

admin001
Posting Yak Master

166 Posts

Posted - 2003-07-24 : 07:31:36

Hello Tara ,

That's magnificent . I tried your concept of transferring the logins and passwords by creating linked servers and it worked .
I did modify the script as per my requirement .

I have a couple of new installations of SQL and i am definitely going to use this to get my logins to the new server .

Truly ,i owe you a big treat at the OktoberFest ..........

Thanks .

I keep trying to lose weight but it always finds me.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 10:59:01

Isn't Tara great...

my toolbox is getting a wee bit full...thank god...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources