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 seperate 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 , 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

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

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

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;

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s