This will get all logins (Windows and SQL) that are relative to your particular database, it doesn't retrieve the server roles assigned but that's easy, see if you can work it out 😉

USE DATABASE

GO

select'CREATE LOGIN ' + sl.name +

' WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) +

' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) +

', DEFAULT_DATABASE = ' + quotename(sl.default_database_name) +

', DEFAULT_LANGUAGE = ' + sl.default_language_name +

', CHECK_EXPIRATION = ' +

case

when sl.is_expiration_checked = 0 then 'off'

else 'on'

end + ', CHECK_POLICY = ' +

case

when sl.is_policy_checked = 0 then 'off'

else 'on'

end

from sys.sql_logins sl

where exists (select sid from sys.database_principals dp

where dp.sid = sl.sid) and sl.principal_id > 4

UNION ALL

select'CREATE LOGIN ' + QUOTENAME(sp.name) +

' FROM WINDOWS WITH DEFAULT_DATABASE = ' +

quotename(sp.default_database_name) + ', DEFAULT_LANGUAGE = ' +

sp.default_language_name

from sys.server_principals sp

where exists (select sid from sys.database_principals dp

where dp.sid = sp.sid) AND sp.principal_id > 4 AND sp.type IN ('G','U')