I often need to replicate logins to another SQL Server where the SID and password (for SQL login types, not Windows based) need to be the same. The script below is executed against the source SQL Server and will generate the T-SQL to run on the target SQL Server:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Outputs the CREATE LOGIN script complete with password and SID. SELECT 'CREATE LOGIN [' + sp.name + '] ' + CASE WHEN sp.type IN ('U', 'G') THEN 'FROM WINDOWS ' ELSE '' END + 'WITH ' + + CASE WHEN sp.type = 'S' THEN 'SID = ' + master.sys.fn_varbintohexstr(sl.sid) + ', ' + 'PASSWORD = ' + master.sys.fn_varbintohexstr(sl.password_hash) + ' HASHED, ' + 'CHECK_EXPIRATION = ' + CASE WHEN sl.is_expiration_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + 'CHECK_POLICY = ' + CASE WHEN sl.is_policy_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END ELSE '' END + 'DEFAULT_DATABASE = [' + sp.default_database_name + CASE WHEN LEN(sp.default_language_name) > 0 THEN '], DEFAULT_LANGUAGE = [' + sp.default_language_name + ']' ELSE ']' END FROM sys.server_principals sp LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id WHERE sp.type IN ('S', 'U', 'G') ORDER BY 1; |
The result looks like this (actual login name, SID and password altered):
1 2 3 4 5 6 7 |
CREATE LOGIN [some_user] WITH PASSWORD = 0x010089152d75ad2g2fd9e12cad24c6f1e3a8d055c59eb2e74a99 HASHED, SID = 0x1d6b92e194a45c489ced85728fa46f91, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english] |
Here is a screenshot of the query results:
Views – 2470