Migration of sql logins

Hi, Recently i have moved the database from one machine to another machine(using backup file(.bak) in source and restore method from destination), but my issue was some users logins and passwords are not working or hide it....may i know why is it happening? Is it compulsory to migrate the logins using sp_help_revlogin or EXEC sp_change_users_login one by one.....to migrate it the logins.. Is any other method to avoid...while we move the database from one to another? What are the methods we have to follow? can anyone tell me...........

more ▼

asked Aug 17, 2012 at 12:41 PM in Default

avatar image

2.5k 72 76 83

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

This scenario is known as orphaned users : http://msdn.microsoft.com/en-us/library/ms175475.aspx

You can find which users are affected by using the sp_change_users_login procedure, and fix them with the same proc, using the update_one action , or ideally ALTER USER as there are plans to remove the functionality of sp_change_users_login in future versions.

See : http://ask.sqlservercentral.com/questions/1302/resolving-orphaned-users.html

more ▼

answered Aug 17, 2012 at 01:03 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left

When you do backup\restore, logins are not transferred and the users become orphan users, because the login information are stored in master database. There are few method to fix this -

SSIS Transfer Login Task and others you already mentioned, sp_change_user_login and sp_help_revlogin.

Usually, I use SSIS to transfer the logins and run sp_change_user_login with update_one option to fix my orphan users.

EDIT: another KB Article

more ▼

answered Aug 17, 2012 at 01:03 PM

avatar image

10.8k 37 58 51

(comments are locked)
10|1200 characters needed characters left

You can use the below Query to Move login from one server to other

 declare @databasename varchar(50)
 declare @cmd varchar(1000)
 set @databasename = 'pmscust' -- set database name here
 set @cmd = 'select ''exec master..sp_help_revlogin_2000_to_2005 @login_name='' + '''''''' + l.loginname' + ' + ''''''''' + '+' + ''',@include_db=1, @include_role=1 ''' +
 ' from master..syslogins l' +
 ' inner join ' + @DatabaseName + '.dbo.sysusers u' +
 ' on l.sid = u.sid' +
 ' where u.name != ''dbo''' +
 ' and u.name != ''guest''' +
 ' and u.islogin = 1' +
 ' order by l.loginname'
 print @cmd 


When you run this Query it will give one other Query it will give the user wise deteil and then run those query to get desried query that need to be run on new created or migrated database..

more ▼

answered Aug 22, 2012 at 05:39 PM

avatar image

basit 1
509 57 65 91

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 17, 2012 at 12:41 PM

Seen: 1303 times

Last Updated: Aug 22, 2012 at 05:39 PM

Copyright 2018 Redgate Software. Privacy Policy