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