Category Archives: MSSQLServer

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