A Quarterly Permission Review

Permissions accumulate quietly while applications and teams change. A quarterly permission review gives owners a regular chance to confirm who still needs access and who no longer does.

Set the Quarterly Permission Review Scope

List instances, databases, applications, and privileged paths covered by the quarter. Include SQL logins, Windows groups, server roles, database roles, direct grants, and contained users. State what the review does not cover, such as directory nested membership, unless another team supplies it. A review that silently omits one access path gives false confidence.

I begin with the highest-impact permissions: sysadmin, securityadmin, db_owner, and broad schema grants. Then I work through application and analyst access. The quarterly permission review is a decision process, not a dump of every metadata row. Who can approve access for each database? Find that person before sending a report.

Snapshot Server Principals

Capture server principal names, types, disabled state, and role membership. Compare the new snapshot with the previous approved one. New members and newly enabled logins deserve priority. A login that was present last quarter still needs an owner, but change detection helps focus attention.

The query below lists direct fixed-server-role memberships. Windows group members are not expanded here. Ask the directory team for current nested membership and group ownership. I keep the two sources together so the reviewer sees the actual access path, not only the SQL principal name.

SELECT r.name AS server_role,
       m.name AS member_name,
       m.type_desc AS member_type,
       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;

Review Database Roles and Grants

Within each database, capture role memberships and explicit permissions. A user can receive access through a role, direct GRANT, schema permission, or ownership chain. A report of db_datareader membership alone is incomplete. Include custom roles and explain what each role permits. Review permissions at the schema level because a single grant can cover future objects.

I send owners a readable summary with exceptions, not raw catalog exports alone. The catalog output is evidence. The decision is whether each access path matches a current job. The next query lists role membership in the current database. Repeat it for the databases in scope.

SELECT r.name AS database_role,
       m.name AS member_name,
       m.type_desc AS member_type
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS r
  ON r.principal_id = drm.role_principal_id
JOIN sys.database_principals AS m
  ON m.principal_id = drm.member_principal_id
ORDER BY r.name, m.name;

Find Orphaned and Unmapped Users

After restores or migrations, a database user can lack a matching server login. Some users are intentionally contained or use other authentication paths, so do not label every unmatched SID an error. Identify authentication type, compare SIDs where appropriate, and ask the application owner whether the user is still needed.

I separate broken mappings from unused access. A user can be orphaned and still be expected to work after a planned migration. A user can be correctly mapped and no longer justified. The review should answer both technical function and business need. Fix or remove through a tested change rather than dropping users based on a single join result.

Look for Changes Since Last Quarter

Compare grants, role memberships, login state, and group membership with the prior snapshot. Note temporary access that exceeded its end date. A change ticket can explain why access was added, but it does not prove the need remains. Ask the owner to reconfirm the current operation.

I put additions and privilege increases at the top of the report. They are easier to miss in a long alphabetic list. Removals matter too: verify that the expected account was removed from every relevant path. An account can leave one database role while remaining sysadmin through a group. The comparison needs to follow effective routes, not just matching names.

Check Service and Job Accounts in the Quarterly Permission Review

Application services, SQL Agent job owners, proxies, and integration accounts can retain broad rights after a project ends. Match each account to a current service and owner. A quarterly job can use an account that appears idle in daily logs. Review schedules before proposing disablement. Test a narrower replacement in a safe environment.

I ask for the exact task a privileged service login performs. If the answer is simply that the application needs it, the review is unfinished. Identify tables, procedures, or server operations. That detail supports a least-privilege change later. A service account without an owner is an urgent governance gap, not a harmless technicality.

Ask Owners for a Real Decision

Give the manager or application owner a concise list of people and service identities, their access level, and the reason recorded last time. Ask them to approve, remove, or investigate each one. Record the decision, date, and approver. A blanket reply saying looks fine is weak when the list contains unknown accounts.

I make the review easy to act on. Group by application and highlight new or privileged access. Keep technical detail available for questions. The owner should understand what db_owner or sysadmin enables in plain language. Signoff is meaningful only when the reviewer can see the consequence of keeping access.

Remove Access With a Test Plan

Revocation can break jobs or infrequent tasks. Plan changes with the application owner, verify backups or rollback scripts as needed, and monitor failures after removal. For uncertain accounts, a controlled disable period can be safer than immediate deletion. Do not treat a lack of tickets in one day as proof of no dependency.

I record the exact change and evidence that it took effect. Then I update the approved baseline. Access review should reduce unexplained privileges while keeping the service working. If a dependency appears, repair the permission narrowly and revise the owner record. A reversible process encourages teams to clean up rather than keep every account forever.

Close the Quarterly Permission Review With Evidence

Store snapshots, owner decisions, directory group exports, change records, unresolved exceptions, and next review dates in the approved secure location. Protect the report because it maps privileged access. Carry open questions forward with named owners. The next quarter should begin from the last signed baseline, not from scratch.

Which permission on the list would surprise the application owner? Surface that one first. A quarterly permission review earns its time when it turns hidden access into explicit, accountable decisions. The query results are the starting evidence. The approved changes and exceptions are the outcome.

Related reading on this blog: Auditing Who Has sysadmin and Understanding Grant, Deny, and Revoke Permissions.

A permission export is not a review, it is evidence for owners to approve or remove access.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.