DBA Scripts: Get User Permissions Audit

---

The One Login You Actually Care About Right Now

Most permissions checks answer a broad question: who has sysadmin, what roles exist, what’s granted where. Useful for a periodic audit, but not what you reach for when someone asks “why does Sarah in Finance have access to the Payroll database” or “what does this service account actually touch.” That question is about one login, right now, and the honest answer has to include Active Directory group membership, not just what’s explicitly granted in SQL Server.

This script answers exactly that. Point it at one login, SQL or Windows, and it returns everything that login can actually reach: server-level roles and connection principals, and for every database it can access, its role membership there, resolved through SQL Server’s own security token so nested AD group membership shows up automatically.

---

Why This Matters

• A domain user’s SQL access is frequently granted through an AD group, not a direct login, and that group can be nested two or three levels deep. Nothing in sys.database_permissions or sys.database_role_members shows that chain directly; you’re reading the login’s face value, not what actually resolved
• Off-boarding and access reviews need a per-person answer, not a per-database dump. “What does this specific login have, everywhere” is the actual question being asked, and building that by hand means checking every database one at a time
• Service accounts accumulate access silently as new databases get added to a server. A single login-focused audit catches scope creep that a role-by-role review misses because nobody’s looking from the login’s point of view
• sys.login_token and sys.user_token return the same resolved security token SQL Server itself uses to make access decisions, so this reflects reality, not intent. Explicit grants can drift from actual effective access; the token can’t

---

When to Run This Script

• Someone asks “why does this login have access to X” — the fastest way to get a real answer, AD groups included
• Access reviews and off-boarding, checked one login at a time as people join, move, or leave
• Investigating a service account before reusing or retiring it, to see the full footprint before touching it
• After a migration, to confirm a specific login’s access carried over the way it was supposed to, not just that a mapping exists

---

The Script

/*
Script Name : Get-UserPermissionsAudit
Category    : security
Purpose     : Audit one login's effective access across the whole instance in a single pass:
              server-level roles/connection principals, plus, per database, every database
              role and Windows/AD group actually granting it access — resolved through the
              real security token (sys.login_token / sys.user_token), so nested AD group
              membership shows up automatically instead of requiring a manual AD lookup.
              EDIT @LoginName below before running.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-user-permissions-audit/)
Requires    : sysadmin, or IMPERSONATE permission on the target login plus VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-user-permissions-audit/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- EDIT THIS: the login to investigate. Works for SQL logins and Windows users/groups.
DECLARE @LoginName SYSNAME = N'DOMAIN\username';   -- e.g. N'CONTOSO\jsmith' or a SQL login name
DECLARE @IncludeDatabasesWithoutAccess BIT = 0;    -- 1 = also list databases this login can't reach

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @LoginName)
BEGIN
    RAISERROR('Login "%s" not found on this server. Check spelling (DOMAIN\name for Windows logins).', 16, 1, @LoginName);
    RETURN;
END;

IF OBJECT_ID('tempdb..#myuser') IS NOT NULL DROP TABLE #myuser;

CREATE TABLE #myuser (
    server_name  NVARCHAR(128),
    database_name SYSNAME,
    principal_id INT,
    sid          VARBINARY(85),
    name         NVARCHAR(128),
    type         NVARCHAR(128),
    usage_desc   NVARCHAR(128)
);

DECLARE @impersonating BIT = 0;
DECLARE @sql NVARCHAR(MAX);

BEGIN TRY
    EXECUTE AS LOGIN = @LoginName;
    SET @impersonating = 1;

    -- Server-level token, captured before REVERT so it reflects the target login, not the caller.
    INSERT INTO #myuser (server_name, database_name, principal_id, sid, name, type, usage_desc)
    SELECT @@SERVERNAME, N'[CONNECTION]', lt.principal_id, lt.sid, lt.name,
           CASE WHEN lt.type = 'SERVER ROLE' THEN 'ROLE'
                WHEN lt.type = 'WINDOWS GROUP' THEN 'WINDOWS GROUP'
                ELSE 'SQL USER' END,
           lt.usage
    FROM sys.login_token AS lt
    WHERE lt.sid IN (SELECT sid FROM sys.server_principals);

    -- Only databases this login can actually reach (HAS_DBACCESS under impersonation) —
    -- otherwise USE on an inaccessible database raises Msg 916 and aborts the whole script.
    SET @sql = N'';
    SELECT @sql = @sql + N'
    USE ' + QUOTENAME(name) + N';
    INSERT INTO #myuser (server_name, database_name, principal_id, sid, name, type, usage_desc)
    SELECT DISTINCT @@SERVERNAME, DB_NAME(), principal_id, sid, name, type, usage
    FROM sys.user_token
    WHERE sid IN (SELECT sid FROM sys.database_principals)
      AND name <> ''public'';'
    FROM sys.databases
    WHERE state_desc = 'ONLINE'
      AND database_id > 4   -- skip system databases
      AND HAS_DBACCESS(name) = 1;

    IF LEN(@sql) > 0
        EXEC (@sql);

    IF @IncludeDatabasesWithoutAccess = 1
    BEGIN
        INSERT INTO #myuser (server_name, database_name, principal_id, sid, name, type, usage_desc)
        SELECT
            @@SERVERNAME,
            d.name,
            NULL,
            NULL,
            CASE WHEN d.state_desc = 'ONLINE' THEN 'MISSING' ELSE d.state_desc END,
            'ROLE',
            'MISSING'
        FROM sys.databases AS d
        WHERE d.database_id > 4
          AND (d.state_desc <> 'ONLINE' OR HAS_DBACCESS(d.name) = 0);
    END;

    REVERT;
    SET @impersonating = 0;
END TRY
BEGIN CATCH
    IF @impersonating = 1
    BEGIN
        REVERT;
    END;
    THROW;
END CATCH;

-- One row per scope: [CONNECTION] (server-level) plus one row per database.
SELECT
    x.server_name,
    x.database_name,
    ISNULL(STUFF((
        SELECT ',' + t.name
        FROM #myuser AS t
        WHERE t.type = 'ROLE'
          AND t.server_name = x.server_name
          AND t.database_name = x.database_name
        ORDER BY t.name
        FOR XML PATH('')), 1, 1, ''), '')                              AS [ROLE],
    ISNULL(STUFF((
        SELECT ',' + t.name
        FROM #myuser AS t
        WHERE t.type = 'WINDOWS GROUP'
          AND t.server_name = x.server_name
          AND t.database_name = x.database_name
        ORDER BY t.name
        FOR XML PATH('')), 1, 1, ''), '')                              AS [WINDOWS GROUP],
    ISNULL(STUFF((
        SELECT ',' + t.name
        FROM #myuser AS t
        WHERE t.type = 'SQL USER'
          AND t.server_name = x.server_name
          AND t.database_name = x.database_name
        ORDER BY t.name
        FOR XML PATH('')), 1, 1, ''), '')                              AS [SQL USER]
FROM #myuser AS x
GROUP BY x.server_name, x.database_name
ORDER BY
    CASE WHEN x.database_name = N'[CONNECTION]' THEN 0 ELSE 1 END,
    x.database_name;

DROP TABLE #myuser;

Edit @LoginName at the top before running. This is a copy/paste-first script, no CLI parameters needed. Set @IncludeDatabasesWithoutAccess = 1 to also list every database the login can’t reach, useful for confirming access was actually removed after an off-boarding change.

---

How To Run From The Repo

Clone DBA Tools, initialize, then edit the login name at the top of the script before running:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Audit one login's effective access (edit @LoginName in the script first):
.\run.ps1 Get-UserPermissionsAudit

# Against a remote SQL Server:
.\run.ps1 Get-UserPermissionsAudit -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/security/access/Get-UserPermissionsAudit.sql
• powershell/wrappers/security/access/Get-UserPermissionsAudit.ps1

---

Example Output

The [CONNECTION] row is the server-level picture: the login itself, any server roles it’s in, and any Windows/AD group in its token that’s also a registered SQL Server login. Every row after that is one database, with role, Windows-group, and SQL-user membership pivoted into readable columns rather than one row per grant.

---

Understanding the Results

• [CONNECTION] with a WINDOWS GROUP entry — this is the answer to “what group is actually granting SQL access.” That group is registered as a SQL Server login, and the investigated login reaches it through AD group membership, nested or direct
• ROLE column showing sysadmin — the login (or a group it belongs to) has unrestricted access; treat this the same way you’d treat any Get-SysadminMembers finding
• A database missing entirely from the results (with @IncludeDatabasesWithoutAccess = 0) — the login has no access there at all; re-run with the flag set to 1 to confirm that explicitly rather than inferring it from absence
• dbo showing up as the SQL USER for a database — usually means the login is a member of a role with implied ownership, or is itself mapped as dbo; worth a second look if that wasn’t the intent

---

Best Practices

• Run this whenever anyone asks “why does X have access to Y” — it’s faster and more reliable than tracing AD group membership by hand
• Use it as the last step of an off-boarding checklist, with @IncludeDatabasesWithoutAccess = 1, to confirm access was actually removed everywhere, not just from the databases someone remembered to check
• For service accounts specifically, run this before any migration or credential rotation to capture the full footprint first — nothing worse than breaking something whose access you didn’t know existed
• Pair with Sysadmin Members for the reverse direction: that script tells you who has the top privilege server-wide, this one tells you everything about one specific login

---

Related Scripts

You may also find these scripts useful:

• Security (hub)
• Sysadmin Members
• Orphaned Users
• Permissions and Role Membership
• Login Security Audit
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Common Questions

Summary

Most permissions tooling answers “what exists”: every grant, every role, every login, all at once. This script answers the question a DBA actually gets asked mid-conversation: what does this login have, right now, including the AD group membership that’s usually the real reason behind it. Run it whenever that question comes up, and as the closing check on any off-boarding or service-account change, so “access removed” means something you’ve actually verified rather than assumed.