Copy logins with their password

Question

Copy logins with their password

SQL_Surfer

SSCrazy Eights

Points: 8316
More actions
January 17, 2014 at 2:45 pm

#146453

I need to copy some databases from one server to another. What is the best way to copy logins with their passwords? Each login has access to multiple databases and I want that permissions to be preserved in the new database.

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

SQL_Surfer (1/17/2014)
I need to copy some databases from one server to another. What is the best way to copy logins with their passwords? Each login has access to multiple databases and I want that permissions to be preserved in the new database.

Use "Windows Authentication" instead of "SQL Server Authentication" in the future. I suppose one could write a script to grab the hash codes for the user passwords but, IIRC, even that won't work because the PW's are server senstive (and, yeah... I could be wrong but that's my recollection).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323504 More actions · Answer 2

microsoft supplies a proc named sp_help_revlogin here that scripts out sql users with hashed passwords, for importation onto other servers.

you'll need to grab the right version off of the MS site, as there's a version for 2005 vs 2008 (i think)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

george sibbald SSC Guru Points: 104210 More actions · Answer 3

the version for 2005 and 2008 is the same.(but different from the 2000 version)

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Lowell (1/17/2014)
microsoft supplies a proc named sp_help_revlogin here that scripts out sql users with hashed passwords, for importation onto other servers.
you'll need to grab the right version off of the MS site, as there's a version for 2005 vs 2008 (i think)

Ah, dang. I forgot all about that. Thanks, Lowell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233821 More actions · Answer 5

SQL_Surfer (1/17/2014)
I need to copy some databases from one server to another. What is the best way to copy logins with their passwords? Each login has access to multiple databases and I want that permissions to be preserved in the new database.

Are these logins to be moved from a legacy version of SQL Server or between instances that are SQL Server 2005 onwards?

To get a login and retain its password and SID use the following to script the SQL login out.

note: this is not necessary with Windows logins as the SID is pulled from the domain controller

SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +

sys.fn_varbintohexstr(password_hash) +

' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +

', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +

', DEFAULT_LANGUAGE = ' + default_language_name +

', CHECK_EXPIRATION = ' +

CASE

WHEN is_expiration_checked = 0 THEN 'OFF'

ELSE 'ON'

END +

', CHECK_POLICY = ' +

CASE

WHEN is_policy_checked = 0 THEN 'OFF'

ELSE 'ON'

END +

CASE is_disabled

WHEN 0 THEN ''

ELSE '; ALTER LOGIN [' + name + '] DISABLE;'

END

FROM master.sys.sql_logins

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Also consider the SSIS Transfer Logins task.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233821 More actions · Answer 7

GilaMonster (1/20/2014)
Also consider the SSIS Transfer Logins task.

Oh jesus, not that festering steamy pile of doggy doo doo 😀

My script is a lot cleaner and easier 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

george sibbald SSC Guru Points: 104210 More actions · Answer 8

Perry Whittle (1/20/2014)
SQL_Surfer (1/17/2014)
I need to copy some databases from one server to another. What is the best way to copy logins with their passwords? Each login has access to multiple databases and I want that permissions to be preserved in the new database.
Are these logins to be moved from a legacy version of SQL Server or between instances that are SQL Server 2005 onwards?
To get a login and retain its password and SID use the following to script the SQL login out.
note: this is not necessary with Windows logins as the SID is pulled from the domain controller
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
, CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '] DISABLE;'
END
FROM master.sys.sql_logins

Perry, syntax error on line , CHECK_POLICY = ' +

comma should be a quote.

this also captures 'sa' login which you would want to omit when transferring to a new server.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 9

Perry Whittle (1/20/2014)
GilaMonster (1/20/2014)
Also consider the SSIS Transfer Logins task.
Oh jesus, not that festering steamy pile of doggy doo doo 😀

as perry says - doggy doo doo. For sql authenticated accounts it disables the id AND changes its password to a random value on the destination server, so worse than useless for most purposes.

---------------------------------------------------------------------

Perry Whittle SSC Guru Points: 233821 More actions · Answer 10

george sibbald (1/20/2014)
Perry Whittle (1/20/2014)
SQL_Surfer (1/17/2014)
I need to copy some databases from one server to another. What is the best way to copy logins with their passwords? Each login has access to multiple databases and I want that permissions to be preserved in the new database.
Are these logins to be moved from a legacy version of SQL Server or between instances that are SQL Server 2005 onwards?
To get a login and retain its password and SID use the following to script the SQL login out.
note: this is not necessary with Windows logins as the SID is pulled from the domain controller
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
, CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '] DISABLE;'
END
FROM master.sys.sql_logins
Perry, syntax error on line , CHECK_POLICY = ' +
comma should be a quote.
this also captures 'sa' login which you would want to omit when transferring to a new server.

yes, it also scripts the default cert logins too, but you just need to apply the ones you require.

I've corrected my original script above

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Nice Script Perry. When transferring logins, I will always go for the script approach similar to what Perry has shared.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 12

Does this also carry over the appropriate permissions on the dbs?

george sibbald SSC Guru Points: 104210 More actions · Answer 13

database permissions are held within the database, so will be taken over with the database restore.

Scripts above only deal with the logins, but as they take the sids with them, the logins will automatically marry up to the users within the database.

---------------------------------------------------------------------

Perry Whittle SSC Guru Points: 233821 More actions · Answer 14

The script doesn't carry server permissions, but with a little imagination and querying the correct catalogs its easy to accomplish.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉