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 Monday, January 20, 2014 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,958, Visits: 12,839
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"
Post #1532630
Posted Monday, January 20, 2014 8:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 20,460, Visits: 14,086
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1532691
Posted Monday, January 20, 2014 10:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809
Does this also carry over the appropriate permissions on the dbs?
Post #1532752
Posted Monday, January 20, 2014 11:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 5,849, Visits: 12,584
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.


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

Post #1532755
Posted Monday, January 20, 2014 11:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,958, Visits: 12,839
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"
Post #1532759
Posted Tuesday, January 21, 2014 8:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 845, Visits: 2,331
I'll join in - nice script, Perry! It's got more of the options than the one I use.

SQL 2005, 2008, and 2008R2 all use 2005-vintage "0x0100" prefix passwords - they are SHA-1(UCS-2(password)+salt), so hashed passwords are portable between all three, in any direction.

SQL 2012 uses "0x0200" prefix passwords - they are SHA-512(UCS-2(password)+salt). You'll need to add the plaintext passwords yourself, unfortunately.

I haven't looked at SQL 2014 yet.

P.S. If you're upgrading, I would recommend trying to take the opportunity to change what you can to Windows authentication, as Jeff said, or at least upgrade and change the passwords on those you don't. Too often I see passwords like "P@ssw0rd" (Upper, lower, number, symbol, 8 character... worthless) or "Myc0mpanyMyapp" - something easily guessable. Much better are passwords like
"ZVDKAgmcAtrBVdjBMF117OSZjbHsbW" (upper, lower, number, 30 characters, random)
or, even better,
"4HÑßäCÖ{@ÂrÆÑlë¼{ýq†vJGÙiÇxQPOÍ7¯†{èþ®GòÌA@0¿ôøÂÕ;ÒÂÊxaJÛïLYj‡ÑofKŠ{­­YXÊziÌ«ó&,nO·ÓÓÿuë­úøCæКA,7Òêz=ñòd?ø|b2‰Ag™Ð1.õpu­ÓÜ" (upper, lower, number, symbol, extended ASCII, 126 characters, random - SQL Server likes it just fine, though many apps freak out at various symbols - each platform has a different set of reserved symbols).

*UCS-2 is the actual implementation of what SQL Server calls "Unicode" or "NVARCHAR" and "NCHAR".

**SQL 2000 logins can have the same "0x0100" prefix, but they're in a different format.

***http://keepass.info/ - KeePassor other local software can help you create and keep track of good passwords.
Post #1533142
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse