Show Users (generic)

select * from sys.database_principals
WHERE [type] IN ('S','U')
ORDER BY [type]

SELECT name, is_disabled,
is_policy_checked, is_expiration_checked
FROM sys.sql_logins;