Check User Logins in SQL Server

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

select name, type_desc , authentication_type_desc from sys.database_principals;

Check roles and user at Server level

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

Check the database roles

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

Check the Server Roles

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

Check the Database Permission

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

Check the Server Permissions

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.