Copying and Moving SQL Server Logins

Copying and Moving SQL Server Logins
10

- Details

The final step in moving and copying SQL Server Databases is to make sure that your Logins are successfully mapped to database users and roles. In this video you'll therefore see what makes this requirement necessary and watch a step-by-step walkthrough of exactly what you need to do to ensure proper mappings between SQL Server Logins and database users and roles.

This Video Covers

Database Management, Logins, Security, Roles and Users.

Details

Video Length: 09:56
Skill Level: 200 - Intermediate
Series: Copying and Moving Databases
Author/Presenter: Michael K. Campbell
Applies to SQL Server: 2000, 2005, and 2008

Tags: Tips and Tricks, Administration, Concepts, and Security

    Applicability

Analyst 0
DBA 50
Developer 40
Manager 10

 

- Downloads

+ Related Videos

+ Transcript

- Comments

sql15\WebApp still is on the DB SSV_Copy as a User when you run the script for checking the users on the db
after the migration even if you created the login on the server you didn't associated the login with the user
how will this work?

Gabriel - May 28, 2010.

The "Video Code" link above is correct, but the "Download Code" link to the right of the video when playing is wrong.

It linkes to "Code for Copying and Moving SQL Server Databases.zip" not "Code for Copying and Moving SQL Server Logins.zip".

Thanks


greg_burns - October 04, 2012.

Code download should be fixed now.

Michael K. Campbell - November 08, 2012.

the process does create the user but how do I make sure OLD passwords from 2005 is migrated over to 2008.

no matter what I do I receive the msg!
login faild for user 'xxx'
sername : nane
error number: 18456
sev: 14 state:1 line # 65536

Aramazd - March 04, 2013.

@Aramazd,

Unfortunately it looks like you're running in to some other kind of problem or issue.

Specifically, looks like the login in question is actually DISABLED - because that's what State 1 typically implies.

Give this excellent troubleshooting guide a look - it should help you figure this out:
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

Regards,

--Mike

Michael K. Campbell - March 04, 2013.

Is "disabled" not accurate since the status is "enable" however I disabled and enabled to make sure same result .
Are you certain moving DB from 2005 to 2008 and attaching it with your login script works? How does the HASH value synch up with recovered and attached databases? I also tried same password on Source DB 2005 and it works

Aramazd - March 05, 2013.

@Aramazd,

Yes - the whole point of the scripts in the downloads folder is to HELP synchronize the HASH (Security IDs, or SIDs) you're talking about.

Here's what you need to do. Either
a) Script logins from source server using the scripts provided. Then RUN those at the new/target server (one by one/etc - and checking to make sure the logins aren't defined as disabled).
IF you do that, then when you restore the database/attach it at the secondary and bring it on line, the recovery process will look for SIDs for users at the database level and then (if the SID exists as a viable login) it'll 100% map them.

On the other hand if you have NOT created the corresponding login on the new server, then the SIDs will NOT match up and the mapping will be busted and you'll have orphaned users.

At that point you can
b) Run spchangeusers_login as outlined in the sample scripts - also included in the download.

Long story short: either of those approaches WILL work. If you continue to run into problems AFTER checking for orphans and making sure there are none, then whatever problem you're bumping into is NOT a result of mapping logins to users and you're bumping into ANOTHER problem.

--Mike

Michael K. Campbell - March 05, 2013.

Mike, thank you for responses but after all your hard work & documentation i am not seeing any issue why this process should or should not work.
-- from source
CREATE LOGIN [CAMRB2] WITH PASSWORD = 0x0100BC45101F57FF05D80DC12C066AC552AF0255250557C2F831 HASHED, SID = 0xF7D46571357D514083320D89A51DAFA2,
DEFAULTDATABASE = [MASTER],
CHECK
POLICY = OFF, CHECKEXPIRATION = OFF
-- went okay on new Target system … ( existing SQLSERVER 2008 R2)
Issued EXEC sp
changeuserslogin 'Report' just in case
- no orphans found
- Attached DB and tried to login still same error !
- I may be able to change this particular user’s password however, not possible for 300-400 internal users.( I don’t know their passwords which the SSV reference page for this video if indicating to do next).
EXEC sp_changeuserslogin
@Action = 'Update_One',
@UserNamePattern = 'OrphanedUserHere',
@LoginName = 'LoginNameToMapToHere',
@Password = NULL

- OR optionally, you can also run:
EXEC sp_changeuserslogin
@Action = 'Auto_Fix',
@UserNamePattern = 'OrphanedUserHere',
@LoginName = NULL, -- create a new login based on user name
@Password = 'passwordHere!'

thank you anyway. i'll search some more

Aramazd - March 05, 2013.

Mike ... okay it seems like "permission issue", if I grant sysadmin server role I am able to login if I remove I am not,
Even though I have verified other grant / privileges on this user!


Aramazd - March 05, 2013.

Mike ... okay it seems like "permission issue", if I grant sysadmin server role I am able to login if I remove i am not!
Even though I have verified grants / privileges for this particular user... hope this helps

Aramazd - March 06, 2013.

Add A Comment

 

NOTE: Comments are moderated.

The following pseudo-markup is permitted:
      bold : text inside *stars* will be bold
      italic : text inside _underscores_ will be emphasized
      hyperlinks : [linktext|http://link.url.here]