Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Copy logins with their password Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 2:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 PM
Points: 342, Visits: 884
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.
Post #1532296
Posted Friday, January 17, 2014 3:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532302
Posted Friday, January 17, 2014 3:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1532305
Posted Friday, January 17, 2014 4:04 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 5,888, Visits: 13,062
the version for 2005 and 2008 is the same.(but different from the 2000 version)

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

Post #1532324
Posted Saturday, January 18, 2014 8:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532380
Posted Monday, January 20, 2014 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
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"
Post #1532578
Posted Monday, January 20, 2014 5:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
Also consider the SSIS Transfer Logins task.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1532579
Posted Monday, January 20, 2014 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
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"
Post #1532592
Posted Monday, January 20, 2014 6:37 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 5,888, Visits: 13,062
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.



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

Post #1532608
Posted Monday, January 20, 2014 6:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 5,888, Visits: 13,062
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.


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

Post #1532609
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse