SELECT p.name,'create login ['+p.name+'] '+
       CASE
            WHEN p.type IN ('U' ,'G') THEN 'from windows '
            ELSE ''
       END+
       'with '+
       CASE
            WHEN p.type='S' THEN 'password = '+MASTER.sys.fn_varbintohexstr(l.password_hash)
                +' hashed, '+
                 'sid = '+MASTER.sys.fn_varbintohexstr(l.sid)+
                 ', check_expiration = '+
                 CASE
                      WHEN l.is_policy_checked>0 THEN 'ON, '
                      ELSE 'OFF, '
                 END+'check_policy = '+CASE
                                            WHEN l.is_expiration_checked>0 THEN
                                                 'ON, '
                                            ELSE 'OFF, '
                                       END+
                 CASE
                      WHEN l.credential_id>0 THEN 'credential = '+c.name+', '
                      ELSE ''
                 END
            ELSE ''
       END+
       'default_database = '+p.default_database_name+
       CASE
            WHEN LEN(p.default_language_name)>0 THEN ', default_language = '+p.default_language_name
            ELSE ''
       END AS CreateScript
FROM   sys.server_principals p
       LEFT JOIN sys.sql_logins l
            ON  p.principal_id = l.principal_id
       LEFT JOIN sys.credentials c
            ON  l.credential_id = c.credential_id
WHERE  p.type IN ('S' ,'U' ,'G')
       AND p.name<>'sa'
ORDER BY 1