Jake Shelton (4/9/2014)
Hi all,Is this feasible via the SSIS Transfer Logins task?? I've read a lot of articles talking about doing this scriptually instead, now I'd far rather have a simple solution that just works than go into the guts of the hashing/encryption between 2K5 and 2K8....
The tool certainly looks promising, but if it's not going to work, I'd far rather it fail entirely rather than leave a bunch of logins I'll have to clean up manually, and how messy THAT could get with SID's and whatnot...
Cheers,
Jake.
I tend to use my own script, it doesnt catch the logins roles but it will script the SID and hashed password
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
WHERE name not like '##%' and name <> 'sa'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉