Introduction
In this blog post, we will explore how to use T-SQL (Transact-SQL) to identify which logins have write access to a particular database. Let’s dive into the world of T-SQL and unlock the secrets of access control!
Purpose
Here with the below T-SQL script we can find the logins having database role ‘db_owner’, ‘db_datawriter’, ‘db_ddladmin’ and server role ‘sysadmin’.
SELECT
p.name AS LoginName,
r.name AS DatabaseRole,
NULL AS ServerRole,
CASE WHEN sp.is_disabled = 1 THEN 'Disabled' ELSE 'Enabled' END AS LoginStatus,
sp.create_date AS LoginCreationDate,
sp.modify_date AS LoginModifiedDate
FROM
sys.database_principals AS p
INNER JOIN sys.database_role_members AS rm ON p.principal_id = rm.member_principal_id
INNER JOIN sys.database_principals AS r ON rm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals AS sp ON p.sid = sp.sid
WHERE
r.name IN ('db_owner', 'db_datawriter', 'db_ddladmin')
UNION
SELECT
P1.name AS LoginName,
NULL AS DatabaseRole,
P2.name AS ServerRole,
CASE WHEN P2.is_disabled = 1 THEN 'Disabled' ELSE 'Enabled' END AS LoginStatus,
P2.create_date AS LoginCreationDate,
P2.modify_date AS LoginModifiedDate
FROM
sys.server_principals AS P1
INNER JOIN sys.server_role_members AS RM ON RM.member_principal_id = P1.principal_id
INNER JOIN sys.server_principals AS P2 ON P2.principal_id = RM.role_principal_id
WHERE
P2.name = 'sysadmin'
ORDER BY
LoginName;Output
