Transferring Logins to a new server

Question

Transferring Logins to a new server

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
June 26, 2003 at 3:29 am

#161817

We will shortly install our database on a brand new server. What is the most reliable method of moving the logins from the old to the new server?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Stefan,

quote:
We will shortly install our database on a brand new server. What is the most reliable method of moving the logins from the old to the new server?

what about using the Transfer Login Task in a DTS package?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 2

Does this mean that if I use the DTS wizard to export from a source database to a destination database on a new server, and select the "copy objects & data between SQL Server databases", this will also take the logins from the MASTER. Does this also mean I won't have any problem with orphaned logins?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Stefan,

quote:
Does this mean that if I use the DTS wizard to export from a source database to a destination database on a new server, and select the "copy objects & data between SQL Server databases", this will also take the logins from the MASTER. Does this also mean I won't have any problem with orphaned logins?

I've never had orphaned logins, but I guess you might have to handle this manually after transferring. If I remember right, there are scripts here on the site that could do this for you.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65138 More actions · Answer 4

Before I transfer/copy/restore any database to another server I always use the following to generate sql to create login(s). This makes sure that the sid is the same so that all the user permissions tie up.

select 'sp_addlogin '''+name+''',@sid=',sid from syslogins where name = 'username'

Far away is close at hand in the images of elsewhere.
Anon.

Greg Larsen SSC-Insane Points: 20966 More actions · Answer 5

I would use sp_help_revlogin. Here is a KB about that sp.

http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Shas3 Hall of Fame Points: 3671 More actions · Answer 6

I usually store the result of the following query in a table.

Select name, CONVERT (VARBINARY (32), password) from master..syslogins where name in (Select name from Dbname.sysusers where uid <> 0)

and use the following to create and fix the logins

Select name, CONVERT (VARBINARY (32), password) from master..syslogins where name in (Select name from Dbname.sysusers where uid <> 0)

DECLARE Login_Cur1 CURSOR FOR Select Col001,Col002 from <New_Table_Name >

Declare @User Varchar(255),

@Passwd Varchar(255)

Open Login_Cur1

FETCH NEXT FROM Login_Cur1 INTO @User,@Passwd

While @@FETCH_STATUS = 0

Begin

Print 'EXEC sp_addlogin ' + "'" + @User + "' " + ", " + @Passwd + " , " + '@encryptopt = ' + "'" + 'skip_encryption' + "'"

Print 'Go'

FETCH NEXT FROM Login_Cur1 INTO @User,@Passwd

End

Close Login_Cur1

Deallocate Login_Cur1

GO

DECLARE Login_Curr CURSOR FOR Select Col001,Col002 from

<New_Table_Name >

Declare @User Varchar(255),

@Passwd Varchar(255)

Open Login_Curr

FETCH NEXT FROM Login_Curr INTO @User,@Passwd

While @@FETCH_STATUS = 0

Begin

Print "sp_change_users_login @Action = 'Update_One', @UserNamePattern = " + "'" + @User + "', " + "@LoginName = " + "'" + @User + "'"

Print 'Go'

FETCH NEXT FROM Login_Curr INTO @User,@Passwd

End

Close Login_Curr

Deallocate Login_Curr

GO

Shas3

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 7

StefanJ

SSCarpal Tunnel

Points: 4713

June 26, 2003 at 9:23 am

#463283

Many thanks for the replies.

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 8

Just to make sure I understand, Frank says "I've never had orphaned logins". Does this mean the logins transfer OK if the SID does not already exist on the destination server?

gradpks Say Hey Kid Points: 697 More actions · Answer 9

Stefan,

I agree with Greg Larsen. I have used the sp_help_revlogin procedure several times in the past and it works well. The link he posted from MS (article Q246133) is very easy to follow and works well. Good luck!

Dave Gradoville, MCDBA

Kay_dba SSC-Addicted Points: 478 More actions · Answer 10

If your not doing anything with the old server. Stop the services and grab master mdf and ldf and then stop sql on the new server and swap out master. Just have to make sure the files are in the same locations.