DBA Scripts: Get Permissions and Role Membership

April 14, 2026

–

DBA Scripts, Security (Encryption & Permissions)

Who Can Actually Do What, at Every Level

Sysadmin Members answers the single highest-stakes question: who has unrestricted access. These 4 scripts answer everything underneath that: explicit object and schema grants inside a database, explicit server-level grants and denies on logins, database role memberships across every online database, and membership in every fixed and user-defined server role, not just sysadmin.

Together they’re the difference between “I checked sysadmin” and “I actually know who can do what, everywhere, at every level.”

That includes the DBA. DBAs are naturally sysadmins, but that is becoming controlled: read-only roles for the day to day, with write rights activated against a change order. These 4 scripts are how you show the reality matches that policy.

Why Permissions and Role Membership Matter

Explicit DENY and GRANT_WITH_GRANT_OPTION at the server level are easy to miss entirely: they don’t show up in a sysadmin check and rarely get documented
Database role membership (db_owner, db_datawriter, custom roles) drifts over time as people join, leave, and change jobs. A stale db_owner grant is a common, quietly-accumulating risk
Server roles beyond sysadmin (securityadmin, dbcreator, custom server roles) carry real privilege and are frequently overlooked since attention defaults to sysadmin alone
Object and schema-level grants can quietly bypass role-based access entirely: a login with no role membership at all can still have direct SELECT/EXECUTE grants on sensitive objects

When to Run These Scripts

Security reviews and access audits, alongside Sysadmin Members and Orphaned Users
Before and after a migration, to confirm permissions carried over correctly and nothing extra came with them
When reviewing a server you’ve just inherited, to build a complete picture of who can do what
Periodically, since role membership and explicit grants both drift silently over time without any generating event to notice

The Scripts

1. Get-DatabasePermissions — Explicit Object and Schema Grants

✓ Verified

Tested on: SQL Server 2025 (RTM CU8), Windows lab instance
Last verified: 2026-10-02 (all 4 scripts on this page run, saved outputs from real runs)
Permissions: VIEW ANY DEFINITION at the server level for Get-LoginPermissions and Get-ServerRoleMembers, which sysadmin and securityadmin hold implicitly · VIEW DEFINITION or db_securityadmin in whichever database you point Get-DatabasePermissions at (VIEW DATABASE STATE alone returns only your own rows, with no error) · VIEW ANY DATABASE plus VIEW DEFINITION per database for Get-DatabaseRoleMembers
Safety: read-only, impact low

/*
Script Name : Get-DatabasePermissions
Category    : security
Purpose     : Returns all explicit object- and schema-level GRANT/DENY permissions in the
              current database. Shows grantee, permission, object, and grantor. Run in the
              context of each user database — does not iterate across databases.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-permissions-and-role-membership/)
Requires    : VIEW DEFINITION or membership in db_securityadmin. VIEW DATABASE STATE is not
              enough: it returns only your own permissions, silently, with no error.
              Run against each target database: -Database YourDatabase
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/*
  DESIGN: sys.database_permissions covers three major classes here:
    DATABASE — server-level db-scoped permissions (CONNECT, etc.)
    OBJECT_OR_COLUMN — explicit table/view/proc/function grants
    SCHEMA — schema-level grants that cascade to all objects in the schema
  Column-level permissions (minor_id > 0) are included with the column name resolved.
  Built-in principals (public, sys, INFORMATION_SCHEMA, guest) are excluded.
*/

SELECT
    grantee.name AS grantee_name,
    grantee.type_desc AS grantee_type,
    dp.permission_name,
    dp.state_desc, -- GRANT | GRANT_WITH_GRANT_OPTION | DENY
    dp.class_desc, -- DATABASE | OBJECT_OR_COLUMN | SCHEMA
    CASE dp.class_desc
        WHEN 'SCHEMA' THEN SCHEMA_NAME(dp.major_id)
        WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_SCHEMA_NAME(dp.major_id)
        ELSE NULL
    END AS schema_name,
    CASE dp.class_desc
        WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(dp.major_id)
        ELSE NULL
    END AS object_name,
    CASE dp.class_desc
        WHEN 'OBJECT_OR_COLUMN' THEN o.type_desc
        ELSE NULL
    END AS object_type,
    CASE
        WHEN dp.minor_id > 0 THEN COL_NAME(dp.major_id, dp.minor_id)
        ELSE NULL
    END AS column_name,
    SUSER_SNAME(grantor.sid) AS grantor_name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee
    ON grantee.principal_id = dp.grantee_principal_id
JOIN sys.database_principals grantor
    ON grantor.principal_id = dp.grantor_principal_id
LEFT JOIN sys.objects o
    ON o.object_id = dp.major_id
WHERE dp.class_desc IN ('OBJECT_OR_COLUMN', 'SCHEMA', 'DATABASE')
  AND grantee.name NOT IN ('public', 'sys', 'INFORMATION_SCHEMA', 'guest', 'dbo')
  AND grantee.type NOT IN ('R') -- roles shown separately via Get-DatabaseRoleMembers
ORDER BY
    grantee.name,
    dp.class_desc,
    schema_name,
    object_name,
    dp.permission_name;

2. Get-LoginPermissions — Explicit Server-Level Grants and Denies

/*
Script Name : Get-LoginPermissions
Category    : security-and-permissions
Purpose     : Show explicit server-level permissions granted or denied to logins.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-permissions-and-role-membership/)
Requires    : VIEW ANY DEFINITION (sysadmin and securityadmin have it implicitly)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    sp.name AS grantee,
    sp.type_desc AS grantee_type,
    perm.state_desc AS grant_state,
    perm.permission_name,
    perm.class_desc AS object_class,
    ISNULL(obj.name, 'SERVER') AS object_name
FROM sys.server_permissions AS perm
JOIN sys.server_principals AS sp ON perm.grantee_principal_id = sp.principal_id
LEFT JOIN sys.server_principals AS obj ON perm.major_id = obj.principal_id
WHERE sp.name NOT LIKE '##%'
  AND sp.name NOT LIKE 'NT AUTHORITY%'
  AND sp.name NOT LIKE 'NT SERVICE%'
  AND perm.state_desc <> 'GRANT' -- keep GRANT_WITH_GRANT_OPTION, DENY; exclude plain inherited GRANTs
ORDER BY sp.name, perm.permission_name;

3. Get-DatabaseRoleMembers — Database Role Membership Across All Databases

/*
Script Name : Get-DatabaseRoleMembers
Category    : security-and-permissions
Purpose     : List database role memberships across all online user databases.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-permissions-and-role-membership/)
Requires    : VIEW ANY DATABASE, VIEW DEFINITION on each target database
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

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

CREATE TABLE #role_members (
    database_name NVARCHAR(128),
    role_name NVARCHAR(128),
    is_fixed_role BIT,
    member_name NVARCHAR(128),
    member_type NVARCHAR(60),
    create_date DATETIME
);

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
USE ' + QUOTENAME(name) + N';
INSERT INTO #role_members
SELECT
    DB_NAME() AS database_name,
    dr.name AS role_name,
    dr.is_fixed_role,
    dp.name AS member_name,
    dp.type_desc AS member_type,
    dp.create_date
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS dr ON drm.role_principal_id = dr.principal_id
JOIN sys.database_principals AS dp ON drm.member_principal_id = dp.principal_id
WHERE dp.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'');
'
FROM sys.databases
WHERE database_id > 4
  AND state_desc = 'ONLINE';

EXEC sys.sp_executesql @sql;

SELECT
    database_name,
    role_name,
    is_fixed_role,
    member_name,
    member_type,
    create_date
FROM #role_members
ORDER BY database_name, role_name, member_name;

DROP TABLE #role_members;

4. Get-ServerRoleMembers — Every Fixed and User-Defined Server Role

/*
Script Name : Get-ServerRoleMembers
Category    : security-and-permissions
Purpose     : List members of every fixed and user-defined server role — the comprehensive server-privilege audit.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-permissions-and-role-membership/)
Requires    : VIEW ANY DEFINITION (sysadmin and securityadmin have it implicitly)
Related     : Get-SysadminMembers — the sysadmin-only focused check (health-check member)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    sr.name AS server_role,
    sp.name AS member_login,
    sp.type_desc AS login_type,
    sp.is_disabled,
    sp.create_date,
    sp.modify_date
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS sr ON srm.role_principal_id = sr.principal_id
JOIN sys.server_principals AS sp ON srm.member_principal_id = sp.principal_id
WHERE sp.name NOT LIKE '##%'
ORDER BY sr.name, sp.name;

How To Run From The Repo

Clone DBA Tools, initialize and run whichever script answers your question:

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

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

# Explicit object/schema grants in the current database:
.\run.ps1 Get-DatabasePermissions

# Explicit server-level grants and denies on logins:
.\run.ps1 Get-LoginPermissions

# Database role membership across all online databases:
.\run.ps1 Get-DatabaseRoleMembers

# Every fixed and user-defined server role's members:
.\run.ps1 Get-ServerRoleMembers

# Any of these against a remote sql server:
.\run.ps1 Get-ServerRoleMembers -ServerInstance SQLSERVER01

These scripts live in the repo at:

Example Output

4 scripts, run in the order below. Between them they answer who holds what at the server level and inside each database, and which of it was granted explicitly rather than inherited from a role.

Get-DatabasePermissions, run inside a database that has explicit grants on it. 8 rows, and between them every class the script can report:

SSMS results grid from the Get-DatabasePermissions script in the pass_perm database: 8 explicit permission rows for two SQL users. app_reader has CONNECT, SELECT on dbo.Customer, a column-level DENY on Customer.NationalId and SELECT on the reporting schema. etl_service has CONNECT, EXECUTE WITH GRANT OPTION on dbo.usp_RebuildSalesSummary, and INSERT and UPDATE on reporting.SalesSummary. Every grantor is HPAI01\Peter.

The row to look at is the third one. app_reader holds SELECT on dbo.Customer and a DENY on one column of it, so the grant and the exception to the grant sit on 2 separate rows and neither row means anything without the other. Row 4 is the other easy miss: a SCHEMA grant on reporting, which covers every object in that schema including ones created after the grant was made.

Get-LoginPermissions: 0 rows, and it is worth knowing why. The lab holds 45 explicit server-level permission rows and every one of them is a plain GRANT, which this script filters out by design. An empty result is therefore the healthy answer, not a failed run, and any row that does appear is something somebody set deliberately.

Get-DatabaseRoleMembers: 6 rows across 4 user databases. The row that earns its place is a membership of a role called report_readers with is_fixed_role of 0, a role somebody created rather than one of the built-in db_* set, so its actual grants have to be read separately before you know what the membership gives away.

Get-ServerRoleMembers: 15 rows, spread across 3 server roles out of the 19 that exist on the instance:

server_role    member_login               login_type     is_disabled  create_date              modify_date
-------------  -------------------------  -------------  -----------  -----------------------  -----------------------
bulkadmin      DemoAuditUser              SQL_LOGIN      0            2026-09-07 23:09:30.830  2026-09-07 23:12:25.737
securityadmin  pete                       SQL_LOGIN      0            2026-02-11 17:09:08.467  2026-02-11 17:09:08.503
securityadmin  zz_a_src_login             SQL_LOGIN      1            2026-09-09 19:09:27.770  2026-09-09 19:09:27.790
securityadmin  zz_a_tgt_login             SQL_LOGIN      0            2026-09-09 19:09:27.953  2026-09-09 19:09:27.970
sysadmin       DBA_Junior                 SQL_LOGIN      0            2026-02-15 16:46:43.390  2026-02-15 16:46:43.560
sysadmin       DBA_ServiceAccount         SQL_LOGIN      0            2026-02-15 16:46:43.207  2026-02-15 16:46:43.557
sysadmin       DemoAdminUser              SQL_LOGIN      0            2026-02-15 16:45:57.523  2026-02-15 16:45:57.537
sysadmin       DemoDisabledAdmin          SQL_LOGIN      1            2026-02-15 16:45:57.767  2026-02-15 16:45:57.783
sysadmin       DemoOpsUser                SQL_LOGIN      0            2026-02-15 16:45:57.310  2026-02-15 16:45:57.563
sysadmin       HPAI01\Peter               WINDOWS_LOGIN  0            2026-01-30 00:05:40.280  2026-01-30 00:05:40.290
sysadmin       NT Service\MSSQLSERVER     WINDOWS_LOGIN  0            2026-01-30 00:05:40.310  2026-01-30 00:05:40.320
sysadmin       NT SERVICE\SQLSERVERAGENT  WINDOWS_LOGIN  0            2026-01-30 00:05:40.873  2026-01-30 00:05:40.883
sysadmin       NT SERVICE\SQLWriter       WINDOWS_LOGIN  0            2026-01-30 00:05:40.290  2026-01-30 00:05:40.300
sysadmin       NT SERVICE\Winmgmt         WINDOWS_LOGIN  0            2026-01-30 00:05:40.307  2026-01-30 00:05:40.317
sysadmin       sa                         SQL_LOGIN      0            2003-04-08 09:10:35.460  2026-01-30 00:05:40.460

(15 rows affected)

DemoDisabledAdmin is the finding. It sits in sysadmin with is_disabled of 1, which is a disabled account holding the highest privilege the instance has. Disabling is not removing, and re-enabling the login hands all of it straight back. The NT SERVICE rows are expected and the script keeps them on purpose, because a service account in sysadmin is still a route in.

Understanding the Results

Get-DatabasePermissions

Scoped to the database you are connected to, not to the instance. The same script returned 8 rows in one database, 4 in master, 2 in another and 0 in a fourth, all on the same box within a minute, so name the database before you quote a number from it.

grantee_name
grantee_type

The principal holding the permission and what kind of principal it is. Database roles are excluded on purpose, so every row here is a user holding access directly rather than through a role.

permission_name

The permission itself, for example SELECT, EXECUTE, CONNECT, VIEW DEFINITION.

state_desc

GRANT, GRANT_WITH_GRANT_OPTION or DENY.Act when you see GRANT_WITH_GRANT_OPTION. That principal can hand the same permission to somebody else, so the list of people who hold it is no longer only the list you are reading. A DENY row beats every grant on the same securable.

class_desc

DATABASE, SCHEMA or OBJECT_OR_COLUMN, the level the permission was granted at.Act when the row reads SCHEMA. That grant covers every object in the schema, including objects created after the grant was made, so it will not appear on any per-object audit.

schema_name
object_name
object_type

The securable the row points at. All 3 are NULL on a DATABASE row and object_name is NULL on a SCHEMA row, because there is no single object to name.

column_name

Populated only where the permission was granted or denied on one column rather than the whole object.Act when this is not NULL. Column-level permissions are the easiest kind to lose track of: nothing at the table level shows that one column behaves differently.

grantor_name

The login that granted it, resolved from the grantor principal. Useful when you are trying to find out who to ask about a grant nobody remembers making.

Get-LoginPermissions

Server-wide, and deliberately filtered to the permissions that are unusual enough to need explaining.

grantee
grantee_type

The login the server-level permission is attached to, and its type. Built-in ##, NT AUTHORITY and NT SERVICE principals are filtered out, so what is left is logins somebody created.

grant_state

Plain GRANT rows are deliberately excluded by the script, which leaves only DENY and GRANT_WITH_GRANT_OPTION.Act when it returns nothing. An empty result is the healthy answer here, not a failed run. Because of that filter, every row that does come back is unusual by definition and should have a reason somebody can give you.

permission_name

The server-level permission, for example ALTER ANY LOGIN, CONNECT SQL, VIEW SERVER STATE.

object_class
object_name

What the permission is scoped to. object_name reads SERVER when the permission applies instance-wide, and carries a login name when the permission is on another principal, which is how one login ends up able to alter another.

Get-DatabaseRoleMembers

Every online user database in one result set, which is the only way to see this: role membership is per database and there is no server-wide view of it.

database_name

One row per membership per database. The script loops only ONLINE user databases, and system databases are excluded.Act when a database you expected is missing. Absent does not mean clean here: a database that is offline, restoring or in recovery is skipped silently, and so is anything the running account cannot see into.

role_name
is_fixed_role

is_fixed_role is a bit. 1 is one of the built-in db_* roles and 0 is a role somebody created.Act when you see db_owner, or a 0. db_owner is full control of that database. A custom role tells you nothing by its name, so its own grants have to be read before you know what the membership gives away.

member_name
member_type

The database user in the role, and whether it maps to a login, a Windows principal, a certificate, or nothing at all.

create_date

When the user was created, not when it was put in the role.Act when you are tempted to read it as “when did they get this”. It cannot answer that. sys.database_role_members records no date at all, so nothing on the instance knows when a membership was granted. Measured on the lab: a user added to a second role 12 minutes after it was created still reported its original create_date, and modify_date did not move either.

Get-ServerRoleMembers

The instance-wide equivalent, and the one to read first during an access review.

server_role

The server role the member belongs to.Act when you treat this as a list of roles. Only roles that have members appear: 15 rows came back across 3 roles on a lab instance that has 19 server roles defined. An empty role is absent from the result, not reported as empty.

member_login
login_type

The login and its type. Only ## certificate principals are filtered here, so the NT SERVICE accounts show up, deliberately: a service account in sysadmin is still a route in.

is_disabled

A bit, so the grid shows 1 or 0 rather than true or false.Act when a login shows sysadmin together with is_disabled of 1. A disabled account sitting in the highest-privilege server role is worth a second look: disabling is not removing, and re-enabling the login hands everything back.

create_date
modify_date

When the login was created and last changed, not when it joined the role. As with the database-level script, no membership date exists to report.

Best Practices

Run all 4 together for a genuinely complete picture: sysadmin alone misses server roles, database roles, and explicit object-level grants entirely
Treat any row from Get-LoginPermissions (explicit DENY or GRANT_WITH_GRANT_OPTION) as worth a specific, documented reason, not a default
Review db_owner and other high-privilege database role memberships on the same cadence as sysadmin reviews, not as an afterthought
Re-run after any migration or bulk permission change to confirm exactly what carried over, rather than assuming a script did what it intended

Microsoft’s reference covers sys.database_principals, sys.server_principals and sys.database_permissions in full.

Frequently Asked Questions

How is this different from the User Permissions Audit script?

User Permissions Audit consolidates server logins, database users, and role memberships into one summary view for a fast access review. These 4 scripts go deeper on each specific angle, explicit object-level grants, explicit server-level denies, and a full server-role breakdown beyond sysadmin, useful when the summary view flags something worth a closer look.

Why does Get-DatabaseRoleMembers need dynamic SQL?

Database role membership is scoped per database, and there’s no server-wide DMV for it. The script loops every online user database and runs the same query in each one via sp_executesql, consolidating the results into one temp table so you get a single cross-database result set instead of running it manually in each database.

Get-LoginPermissions came back empty. Does nobody have server-level permissions?

No, and that is the usual confusion with this one. The script deliberately drops plain GRANT rows and keeps only DENY and GRANT_WITH_GRANT_OPTION, because those are the 2 states that need a reason. On the lab instance there are 45 explicit server-level permission rows and all 45 are plain grants, so the script correctly returns nothing. If you want the full picture of what a login can do, read its server role membership with Get-ServerRoleMembers as well.

I get fewer rows than a colleague running the same script, why?

Visibility of the server principal catalog views is permission-gated, and the shortfall is silent: no error, just fewer rows. The 2 server-level scripts on this page need VIEW ANY DEFINITION, which sysadmin and securityadmin hold implicitly. Running the Get-ServerRoleMembers shape as an unprivileged login returned 1 row where sysadmin saw 12 on the same instance. If a permissions audit looks suspiciously clean, check what the account running it can actually see before you trust the result.

Summary

Sysadmin membership is the headline question, but it’s not the whole picture. Explicit grants, database role membership, and the other server roles all carry real privilege and all drift silently over time. These 4 scripts together answer “who can actually do what” comprehensively, not just at the top.

Run them alongside Sysadmin Members and Orphaned Users as a complete access-review pass, and re-run after any migration or bulk permission change to confirm the result matches intent.