Home / Guides / SQL Server quarterly access review
SQL Server quarterly access review in 30 minutes
Every quarter the auditor asks the same question: who can reach which SQL Server database, and is each of them still supposed to? This guide gives you the queries that answer it (logins, sysadmins, effective database access through nested roles, the people behind Windows groups, orphaned and disabled accounts) and the steps that turn the answer into the evidence auditors ask for.
Short answer.
Run the five queries below on each instance and save the results, expand the Windows groups to people with
Get-ADGroupMember -Recursive, and send each data owner the rows for their databases to mark Keep or Remove.
Remove what they reject, run the queries again to prove it is gone, and file the before and after with the sign-offs. Gathering
the data takes about 30 minutes for a handful of instances; the sign-off is what takes calendar time.
What the auditor asks for
The wording differs per framework, but the request is the same: a list of everyone with access, a sign-off by someone who knows whether they should have it, and proof that the access that was not approved was removed.
| Framework | What it says about access | How often |
|---|---|---|
| SOC 2, criteria CC6.2 and CC6.3 | Access is authorized before it is granted, removed when it is no longer authorized, and based on roles with least privilege. Auditors test it with periodic access reviews. | Set by your own controls; quarterly is common |
| SOX IT general controls | Access to financially relevant systems and data is reviewed by the people responsible for them, and inappropriate access is removed. | Set by your own controls; quarterly is common |
| PCI DSS v4.0, requirement 7.2.4 | All user accounts and their privileges, third-party accounts included, are reviewed, management confirms the access is still appropriate, and anything inappropriate is fixed. | At least once every six months |
For SQL Server, "access" means more than the logins. A login reaches a database through a user, through a role (and roles inside
roles), through a Windows group, through sysadmin, as the database owner, or through guest. The queries
below follow every one of those paths.
Step 1: every login
Run as a member of sysadmin (or with VIEW ANY DEFINITION and VIEW SERVER STATE) on each instance.
USE [master];
SELECT sp.name AS login_name,
sp.type_desc AS login_type,
sp.is_disabled,
sp.create_date,
sl.is_policy_checked,
sl.is_expiration_checked,
LOGINPROPERTY(sp.name, 'PasswordLastSetTime') AS password_last_set
FROM sys.server_principals AS sp
LEFT JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id
WHERE sp.type IN ('S', 'U', 'G', 'E', 'X') -- SQL, Windows user, Windows group, Entra user, Entra group
AND sp.name NOT LIKE N'##%'
ORDER BY sp.type_desc, sp.name;
What to look at:
- Shared SQL logins (
SQL_LOGIN) that several people know the password of. Each one needs a named owner. is_policy_checked = 0: a SQL login that ignores the Windows password policy.saenabled (is_disabled = 0), and very oldpassword_last_setdates on SQL logins.- Disabled logins still in the list. A disabled login cannot sign in, but its rights come back the day someone enables it, so remove what nobody needs.
Step 2: sysadmins and server-level permissions
USE [master];
-- server role members, sysadmin first
SELECT r.name AS server_role,
m.name AS member,
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 CASE WHEN r.name = N'sysadmin' THEN 0 ELSE 1 END, r.name, m.name;
-- server-level permissions, without the ones every login has
SELECT p.name AS grantee,
p.type_desc AS grantee_type,
perm.state_desc,
perm.permission_name,
perm.class_desc
FROM sys.server_permissions AS perm
JOIN sys.server_principals AS p ON p.principal_id = perm.grantee_principal_id
WHERE p.name NOT LIKE N'##%'
AND perm.permission_name <> N'CONNECT SQL'
AND NOT (p.name = N'public' AND perm.permission_name IN (N'VIEW ANY DATABASE', N'CONNECT'))
ORDER BY p.name, perm.permission_name;
Members of sysadmin can read and change every database, whatever the database permissions say, so they head the
review. Server permissions such as CONTROL SERVER, ALTER ANY LOGIN or VIEW SERVER STATE
come next; the query leaves out CONNECT SQL (every login has it) and the default grants of public.
Step 3: who can do what in every database
This is the part a spreadsheet gets wrong: rights through roles inside roles. The script walks every online user database, follows role membership to any depth, and writes one row per user and role or permission, with the path it comes through. Run it in one go in SSMS; it reads only the catalog views. Databases that are offline, or that the login cannot enter (such as a non-readable availability group secondary), are skipped, so run it on the primary and check those separately.
USE [master];
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#access') IS NOT NULL DROP TABLE #access;
CREATE TABLE #access (
database_name sysname, user_name sysname, user_type nvarchar(60), login_name sysname NULL,
user_status nvarchar(30), access_via nvarchar(400), access nvarchar(400));
DECLARE @db sysname, @run nvarchar(300), @sql nvarchar(max) = N'
WITH members AS ( -- the user itself, then every role it is in, nested roles included
SELECT u.principal_id AS user_id, u.principal_id AS grantee_id, CAST(N''(direct)'' AS nvarchar(400)) AS via
FROM sys.database_principals AS u
WHERE (u.type NOT IN (''R'', ''A'') AND u.name NOT IN (N''sys'', N''INFORMATION_SCHEMA'')) OR u.name = N''public''
UNION ALL
SELECT m.user_id, rm.role_principal_id,
CAST(CASE WHEN m.via = N''(direct)'' THEN N'''' ELSE m.via + N'' > '' END + r.name AS nvarchar(400))
FROM members AS m
JOIN sys.database_role_members AS rm ON rm.member_principal_id = m.grantee_id
JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
)
INSERT #access (database_name, user_name, user_type, login_name, user_status, access_via, access)
SELECT DB_NAME(), u.name, u.type_desc, sp.name,
CASE WHEN u.name = N''public'' THEN N''everyone in the database''
WHEN u.name = N''guest'' THEN N''GUEST ENABLED (any login)''
WHEN u.authentication_type = 0 THEN N''WITHOUT LOGIN''
WHEN u.authentication_type = 2 THEN N''CONTAINED''
WHEN u.authentication_type = 1 AND sp.sid IS NULL THEN N''ORPHANED (no login)''
WHEN sp.is_disabled = 1 THEN N''LOGIN DISABLED''
ELSE N'''' END,
x.via, ISNULL(x.access, N''(connect only)'')
FROM sys.database_principals AS u
LEFT JOIN sys.server_principals AS sp ON sp.sid = u.sid
OUTER APPLY (
SELECT m.via, (N''member of role '' + r.name) COLLATE DATABASE_DEFAULT AS access -- role memberships
FROM members AS m JOIN sys.database_principals AS r ON r.principal_id = m.grantee_id
WHERE m.user_id = u.principal_id AND m.grantee_id <> m.user_id
UNION ALL
SELECT m.via, p.state_desc + N'' '' + p.permission_name + N'' ON '' -- grants and denies
+ CASE p.class WHEN 0 THEN N''DATABASE''
WHEN 1 THEN QUOTENAME(OBJECT_SCHEMA_NAME(p.major_id)) + N''.'' + QUOTENAME(OBJECT_NAME(p.major_id))
WHEN 3 THEN N''SCHEMA::'' + QUOTENAME(SCHEMA_NAME(p.major_id))
ELSE p.class_desc END COLLATE DATABASE_DEFAULT
FROM members AS m JOIN sys.database_permissions AS p ON p.grantee_principal_id = m.grantee_id
WHERE m.user_id = u.principal_id
AND (p.permission_name <> N''CONNECT'' OR u.name = N''guest'') -- CONNECT is normal, except for guest
AND NOT (p.class = 1 AND OBJECT_SCHEMA_NAME(p.major_id) IN (N''sys'', N''INFORMATION_SCHEMA'')) -- system views
AND NOT (u.name = N''public'' AND p.permission_name IN (N''VIEW ANY COLUMN ENCRYPTION KEY DEFINITION'', N''VIEW ANY COLUMN MASTER KEY DEFINITION''))
) AS x
WHERE ((u.type NOT IN (''R'', ''A'') AND u.name NOT IN (N''sys'', N''INFORMATION_SCHEMA'')) OR u.name = N''public'')
AND NOT (u.name IN (N''guest'', N''public'') AND x.access IS NULL);';
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE database_id > 4 AND state_desc = N'ONLINE' AND HAS_DBACCESS(name) = 1; -- user databases
OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @run = QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @run @sql;
FETCH NEXT FROM dbs INTO @db;
END;
CLOSE dbs; DEALLOCATE dbs;
SELECT * FROM #access ORDER BY database_name, user_name, access_via, access;
Part of the output from our test instance:
database_name user_name user_status access_via access
------------- ------------- -------------------------- ---------------------- --------------------------------
HR report_reader db_owner member of role db_owner
Sales app_user db_datawriter member of role db_datawriter
Sales etl_proxy WITHOUT LOGIN (direct) GRANT EXECUTE ON DATABASE
Sales gone_user ORPHANED (no login) NULL (connect only)
Sales guest GUEST ENABLED (any login) (direct) GRANT CONNECT ON DATABASE
Sales public everyone in the database (direct) GRANT SELECT ON [dbo].[Orders]
Sales report_reader reporting > readers_all GRANT SELECT ON SCHEMA::[rpt]
reporting > readers_all:report_readercan read therptschema because its role is inside another role. That is the row people miss by hand.ORPHANED (no login): a user whose login was dropped. Nobody can sign in as it today, but it should be removed, or a new login with the same SID would inherit its rights.WITHOUT LOGIN: a user with no login at all (used for signing, impersonation orEXECUTE AS). It needs an owner who can say what it is for.GUEST ENABLED: every login on the server can enter this database. Turn it off in user databases withREVOKE CONNECT FROM guest.publicrows: rights everyone in the database has. Grants topublicon your own tables are almost always a mistake.LOGIN DISABLED: the user's login is disabled on the server.
The query lists the members of the fixed roles (db_owner, db_datareader, db_datawriter ...)
as such; the role name says what they can do. dbo is the database owner's login, and a sysadmin enters
every database as dbo too, whether or not it has a user there.
Step 4: the people behind Windows groups
A Windows group login such as CORP\SQL-Readers is one row in SQL Server and fifty people in the auditor's eyes. SQL
Server can list a group's members itself:
USE [master];
SET NOCOUNT ON;
DECLARE @members TABLE (account_name sysname, type char(8), privilege char(9), mapped_login_name sysname, permission_path sysname NULL);
DECLARE @group sysname;
DECLARE group_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.server_principals
WHERE type = 'G' AND name NOT LIKE N'NT SERVICE\%' AND name NOT LIKE N'BUILTIN\%';
OPEN group_cur;
FETCH NEXT FROM group_cur INTO @group;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
INSERT @members EXEC master.sys.xp_logininfo @acctname = @group, @option = 'members';
END TRY
BEGIN CATCH -- a group the server cannot look up is listed, not skipped
INSERT @members VALUES (N'(could not list: ' + LEFT(ERROR_MESSAGE(), 100) + N')', 'error', '', @group, @group);
END CATCH;
FETCH NEXT FROM group_cur INTO @group;
END;
CLOSE group_cur; DEALLOCATE group_cur;
SELECT permission_path AS group_login, account_name, type
FROM @members
ORDER BY permission_path, account_name;
xp_logininfo lists one level only: a group inside the group shows as a group, not as its people, and accounts whose
primary group it is (usually everyone in Domain Users) are not listed. For the full list, ask Active Directory
with the RSAT ActiveDirectory module:
# Every person in a group, nested groups included (RSAT ActiveDirectory module)
Import-Module ActiveDirectory
Get-ADGroupMember -Identity 'SQL-Readers' -Recursive |
Where-Object objectClass -eq 'user' |
Get-ADUser -Properties Enabled, LastLogonDate |
Select-Object SamAccountName, Name, Enabled, LastLogonDate
Step 5: accounts that should not be there any more
Windows logins whose account was deleted from Active Directory:
USE [master];
EXEC sys.sp_validatelogins; -- Windows logins and groups that no longer exist in Windows
Windows logins whose account still exists but is disabled (the usual state of a leaver) are not reported by SQL Server at all. This PowerShell checks each one against Active Directory:
# Windows logins whose Active Directory account is disabled or gone (RSAT ActiveDirectory module)
Import-Module ActiveDirectory
$server = 'SQL01.corp.local'
$domain = 'CORP'
$logins = sqlcmd -S $server -E -h -1 -W -Q "SET NOCOUNT ON; SELECT name FROM sys.server_principals WHERE type = 'U' AND name LIKE N'$domain\%';"
foreach ($login in $logins | Where-Object { $_ }) {
$sam = $login.Split('\')[1]
$user = Get-ADUser -Filter "SamAccountName -eq '$sam'" -Properties Enabled, LastLogonDate
if (-not $user) { "{0}: not found in Active Directory" -f $login }
elseif (-not $user.Enabled) { "{0}: DISABLED in Active Directory (last logon {1:yyyy-MM-dd})" -f $login, $user.LastLogonDate }
else { "{0}: ok" -f $login }
}
Add the orphaned users and disabled logins from steps 1 and 3, and you have the list of access to remove whatever the data owners say.
Step 6: sign-off, removal and proof
- Split the rows by data owner. The owner of the Sales database reviews the Sales rows, not the whole server.
Expand groups to people first, so the owner sees names, not
CORP\SQL-Readers. - Get a Keep or Remove on every row, with a comment and the reviewer's name and date. Rows that are new since last quarter deserve the closest look; compare with last quarter's saved output.
- Remove what was rejected through your normal change process:
ALTER ROLE ... DROP MEMBER,REVOKE,DROP USER,DROP LOGIN, or removing a person from an AD group. - Run the queries again and check that every Remove is gone, and that the person does not still reach the same
database another way (a second group, a role,
guest). - File the evidence: the first output, the sign-offs, the change records and the second output, with dates. Keep the files read-only, or hash them, so nobody can say they were edited afterwards.
Where the manual way stops
- It multiplies. Every instance and every database is another script run and another tab in the spreadsheet.
- The quarter-to-quarter comparison is by hand. "What is new since last time" is the question auditors like most and the hardest to answer from two spreadsheets.
- Groups. Expanding nested AD groups and joining the result back to the database rows is fiddly every time.
- Proof. Showing that a removal really happened, and that the files were not changed later, is extra work.
Questions
How often do I need to review SQL Server access?
As often as your own controls say. PCI DSS v4.0 requirement 7.2.4 asks for at least once every six months; SOC 2 and SOX leave the frequency to your controls, and quarterly is the common choice. Whatever you pick, do it on time every time: a missed review is a finding in itself.
Does a sysadmin need a database user to read a database?
No. A member of the sysadmin server role enters every database as dbo and can read and change everything, with or without a user in it. That is why the sysadmins are the first rows of any access review.
What is an orphaned user and why does it matter?
A database user whose login was dropped. Nobody can sign in as it today, but a new login created later with the same SID would get its rights at once, so remove orphaned users unless you are about to recreate the login. See transferring logins with their SID for the case where they belong to a moved database.
Why do I need to expand Windows groups?
Because the auditor reviews people, not groups. CORP\SQL-Readers is one login in SQL Server, but every member of it, and of the groups inside it, can read what the group can read. xp_logininfo lists one level; Get-ADGroupMember -Recursive lists everyone.
Can SQL Server Audit do the access review for me?
No. SQL Server Audit records what happens (logins, permission changes, queries) from now on. An access review is a snapshot of who could do what, signed off by a person; the queries in this guide give you the snapshot.
Is there a tool that runs the review every quarter?
SQLParity has an Access review add-on that reads every instance in its server list, expands Windows groups through Active Directory, flags the accounts auditors ask about, records Keep or Remove decisions, proves removals with a follow-up scan, and exports a signed evidence pack. It is included in SQLParity's free trial.
Related
- Transfer SQL Server logins with their SID and password
- Sync SQL Server logins and Agent jobs across availability group replicas
- Find failed SQL Server logins (error 18456)
- SQLParity: keep your SQL Server instances the same
- All guides
Sources (Microsoft Learn): sys.server_principals, sys.database_principals, sys.database_permissions, sys.database_role_members, xp_logininfo, sp_validatelogins, LOGINPROPERTY, Get-ADGroupMember, Get-ADUser. Frameworks: AICPA Trust Services Criteria, PCI DSS v4.0 (PCI SSC document library).