T-SQL to see which login has write access on database

496 views 17:18 0 Comments 6 July 2023
T-SQL to see which login has write access on database

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;
SQL

Output

Leave a Reply

Your email address will not be published. Required fields are marked *