Check User Logins in SQL Server

Instance Or Server Level :
Logins: In this define SQL Server User and integrity mode user

Database Levels
USERS: SQL Server define separate Database user at database security tab.

Note: Login name is mapped with Database user

Check all the roles and user at database level in SQL Server

select name, type_desc , authentication_type_desc from sys.database_principals;

Check roles and user at Server level in SQL Server

select name , type_desc , is_disabled, default_database_name from sys.server_principals;

Check the database roles in SQL Server

select dp.type_desc , dp.name ,p.name
from sys.database_role_members r
JOIN
sys.database_principals dp on r.role_principal_id = dp.principal_id
JOIN
sys.database_principals p on r.member_principal_id = p.principal_id;

Check the Server Roles in SQL Server

Select dp.type_desc, dp.name, p.name
from sys.server_role_members r
join
sys.server_principals dp on r.role_principal_id = dp.principal_id
JOIN
sys.server_principals p on r.member_pricipal_id = p.principal_id
GO

Check the Database Permission in SQL Server

select class_desc , permission_name,p.name
from
sys.database_permissions dp
JOIN
sys.database_Principals p on dp.grantee_principal_id = p.principal_id


Check the Server Permissions in SQL Server

select class_desc , permission_name,p.name
from
sys.server_permissions dp
JOIN
sys.Server_principals p on dp.grantee_principal_id = p.principal_id;