x

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

askmlx121 gravatar image

askmlx121
2.5k 69 74 77

(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

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

(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

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(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

basit 1 gravatar image

basit 1
499 52 63 85

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x103
x62

asked: Aug 17, 2012 at 12:41 PM

Seen: 1073 times

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