Database User Access Reviews Without the Spreadsheet Cycle

Every quarter the same cycle runs: the database team exports the user list to a spreadsheet, sends it to managers, and managers write "approved" on every row and send it back. The auditor picks a sample, finds the account of an employee who left two years ago, and the whole cycle becomes a finding. The problem is not careless managers; it is a list that is hard to read, incomplete and asked from scratch every time. This article uses queries to show how a database access review starts from a complete list, how each round looks only at the difference, and how the decision becomes evidence.

Why the Spreadsheet Cycle Does Not Work

There are three reasons. The list is incomplete: it usually holds only logins; database role memberships, permissions granted directly on objects and server roles (such as sysadmin) sit elsewhere. The list is unreadable: a manager reviewing an 800 row table cannot tell which rows are risky and approves them all. The loop never closes: there is no next step showing that a permission marked "remove" was actually removed.

What the Auditor Looks At

1. Completeness of the list: was it pulled from the system, with which query, does it cover every kind of permission?
2. Suitability of the reviewer: is the person who granted the permission not the one approving it, and does the reviewer know the business?
3. Record of the decision: for each row, who decided what, and when?
4. Follow-through: was the permission marked for removal removed, and does the next list show it?

A Complete List: SQL Server, PostgreSQL, Oracle

SQL Server. Four parts are taken together: server roles, role memberships in each database, permissions granted directly on objects or schemas, and users whose login was removed but who remain in the database. The database queries run in each database separately.

-- 1) Server role members (sysadmin, securityadmin, ...)
SELECT r.name AS ServerRole, m.name AS Member, m.type_desc, m.is_disabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
ORDER BY r.name, m.name;

-- 2) Database role members (run in each database)
SELECT DB_NAME() AS DatabaseName, r.name AS DbRole, m.name AS Member, m.type_desc
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_id
ORDER BY r.name, m.name;

-- 3) Permissions granted directly (including those granted to public)
SELECT DB_NAME() AS DatabaseName, pr.name AS Principal, pe.state_desc, pe.permission_name,
       CASE pe.class WHEN 0 THEN N'DATABASE'
                     WHEN 1 THEN OBJECT_SCHEMA_NAME(pe.major_id) + N'.' + OBJECT_NAME(pe.major_id)
                     WHEN 3 THEN N'SCHEMA::' + SCHEMA_NAME(pe.major_id)
                     ELSE pe.class_desc END AS Securable
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
ORDER BY pr.name;

-- 4) Orphaned users: the login is gone, the user is still here
SELECT dp.name, dp.type_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE sp.sid IS NULL AND dp.authentication_type IN (1, 3) AND dp.principal_id > 4;

PostgreSQL. Roles and their memberships come from the pg_roles and pg_auth_members catalogs, table permissions from the information_schema.role_table_grants view. Do not filter out PUBLIC rows; a permission granted to everyone is the widest permission there is.

SELECT r.rolname, r.rolsuper, r.rolcanlogin,
       ARRAY(SELECT b.rolname FROM pg_auth_members AS m
             JOIN pg_roles AS b ON b.oid = m.roleid
             WHERE m.member = r.oid) AS member_of
FROM pg_roles AS r
WHERE r.rolname NOT LIKE 'pg\_%'
ORDER BY r.rolname;

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_schema, table_name;

Oracle. Role, system and object privileges sit in three separate views. Accounts maintained by Oracle itself (12c and later) are left out using the oracle_maintained column.

SELECT grantee, granted_role, admin_option FROM dba_role_privs
WHERE grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y');

SELECT grantee, privilege, admin_option FROM dba_sys_privs
WHERE grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y');

SELECT grantee, owner, table_name, privilege FROM dba_tab_privs
WHERE grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y');

Review the Difference, Not Everything

Save each review's list to a table with its date; the simplest way is to add INTO dbo.AccessReview_2026Q3 to the third query above. In the next round the reviewer does not get 800 rows but the difference between the two lists: permissions added and permissions gone. Alongside the difference, the highest privileges (sysadmin, db_owner, CONTROL, the DBA role in Oracle) are shown again every round, because even if their risk has not changed, their holders must be confirmed as still the right people.

-- Added since the last review
SELECT Principal, Securable, permission_name FROM dbo.AccessReview_2026Q3
EXCEPT
SELECT Principal, Securable, permission_name FROM dbo.AccessReview_2026Q2;

-- Gone since the last review (removals to be confirmed)
SELECT Principal, Securable, permission_name FROM dbo.AccessReview_2026Q2
EXCEPT
SELECT Principal, Securable, permission_name FROM dbo.AccessReview_2026Q3;

Recording the Decision and Closing the Loop

Four fields are recorded for each row: the reviewer, the date, the decision (keep or remove), and the reason when removing. A "remove" decision becomes a change request and is applied. The loop closes when that row shows up in the "gone" part of the next list. A permission marked for removal that is still on the next list is the first finding of the next audit.

What the Product Adds, and Its Limit

The limit first: SQL Change Guard does not extract the current permission list of target databases and does not compute the difference between two lists; that is done with the queries above. The product contributes in three places:

• The source of the difference. GRANT, REVOKE, DENY and account or role management statements in requests opened through the product are listed in the "Privileged Account Operations" report with the target principal, the privilege, the request status, the requester, the executor and the execution time. If a new permission on the difference list ties to an executed (Executed) request in this report, the reviewer sees when it was granted and who asked for it; the approval detail is in the request's evidence file. If it does not, the permission was granted outside the product and needs its own question.
• A record of the removal. If a "remove" decision is run as a change request through the product, the request, the approval and the REVOKE text that ran sit in the evidence file.
• The product's own access. The product is in scope of the review too. The "User Permission Matrix" report gives every product account with its role, status, authentication type, last corporate sign-in and permission set in one table; the "User Access Changes" report shows who opened accounts and who changed their permissions. The "Expected Access Principals" report lists the SQL account the product uses on each target server; that account's permissions on the target belong on the list being reviewed.

Frequently Asked Questions

How often should the review run?

The frequency is set by your policy and the rules you are subject to. A common practice is to review privileged accounts every three months and the rest every six months. Whichever you choose, the auditor tests whether you kept to it.

Who should do the review?

The business owner of the data together with the technical team. The database administrator who granted a permission should not be its only reviewer; otherwise the control approves itself.

Do application and service accounts belong on the list?

Yes, and they often hold the widest permissions. Every service account should have an assigned owner, who does its review. A service account with no owner to be found is the first row to ask about.

How are directory (Active Directory) groups handled?

If a permission is granted to a Windows group in the database, the database list shows only the group; its members live in the directory. The review has two steps: the group's permission is confirmed on the database list, the group's members in the directory's own review. The two should reference each other.