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.

FrameworkWhat it says about accessHow often
SOC 2, criteria CC6.2 and CC6.3Access 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 controlsAccess 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.4All 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.
  • sa enabled (is_disabled = 0), and very old password_last_set dates 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_reader can read the rpt schema 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 or EXECUTE 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 with REVOKE CONNECT FROM guest.
  • public rows: rights everyone in the database has. Grants to public on 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

  1. 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.
  2. 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.
  3. 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.
  4. 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).
  5. 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

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).

An unhandled error has occurred. 🗙