This is the script I use to genereate the CREATE LOGIN code:

select

sp.name

, sp.type_desc

, 'CREATE LOGIN [' + sp.name + '] '

+ case when sp.type in ('U', 'G')

then 'FROM WINDOWS '

else ''

end

+ 'WITH '

+ case when sl.password_hash IS NOT NULL

then 'PASSWORD = ' + convert(nvarchar(max), password_hash, 1) + ' HASHED, '

else ''

end

+ 'DEFAULT_DATABASE = [' + ISNULL(sp.default_database_name, 'master') + '] '

+ ISNULL(', DEFAULT_LANGUAGE = [' + sp.default_language_name + '] ', '')

+ CASE WHEN sp.type_desc = 'SQL_LOGIN'

THEN ', CHECK_EXPIRATION = ' + case is_expiration_checked when 0 then 'OFF, ' else 'ON, ' END

+ 'CHECK_POLICY = ' + case is_policy_checked when 0 then 'OFF, ' else 'ON, ' END

+ 'SID = ' + convert(nvarchar(max), sp.sid, 1)

ELSE ''

END

+ case when sp.is_disabled = 'TRUE'

then ';ALTER LOGIN [' + sp.name + '] DISABLE'

else ''

end

as create_stmt

from master.sys.server_principals sp-- get all logins from [server_principals]

left outer join master.sys.sql_logins sl-- and get some additional information from [sql_logins]

on sp.principal_id = sl.principal_id

and sp.type = sl.type