DBA Scripts: Get Permissions and Role Membership

---

Who Can Actually Do What, at Every Level

Sysadmin Members answers the single highest-stakes question: who has unrestricted access. These four 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.”

---

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

/*
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 DATABASE STATE or membership in db_securityadmin
              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 DATABASE
*/
-- 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 DATABASE
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:

• sql/security/access/Get-DatabasePermissions.sql
• sql/security/access/Get-LoginPermissions.sql
• sql/security/access/Get-DatabaseRoleMembers.sql
• sql/security/access/Get-ServerRoleMembers.sql

---

Example Output

Get-DatabasePermissions (4 rows, all system-generated principals, no custom explicit grants on this lab box):

grantee_name	grantee_type	permission_name	state_desc	class_desc
##MS_AgentSigningCertificate##	CERTIFICATE_MAPPED_USER	CONNECT	GRANT	DATABASE
##MS_PolicyEventProcessingLogin##	SQL_USER	EXECUTE	GRANT	OBJECT_OR_COLUMN

Get-LoginPermissions: 0 rows, no explicit DENY or GRANT_WITH_GRANT_OPTION set on this instance, a genuinely clean result.

Get-DatabaseRoleMembers (4 rows across 3 user databases):

database_name	role_name	member_name	member_type
GrowthLab	db_datareader	pete	SQL_USER
RandomLab	db_datareader	pete	SQL_USER
RandomLab	db_datawriter	pete	SQL_USER
WatchtowerMetrics	db_owner	pete	SQL_USER

Get-ServerRoleMembers (12 rows, sysadmin plus a few service accounts):

server_role	member_login	login_type	is_disabled
sysadmin	sa	SQL_LOGIN	False
sysadmin	PWSQL01\Peter	WINDOWS_LOGIN	False
sysadmin	DBA_ServiceAccount	SQL_LOGIN	False
sysadmin	DemoDisabledAdmin	SQL_LOGIN	True

---

Understanding the Results

• Get-DatabasePermissions with only system principals — a genuinely clean result on a small lab box; on a real production database, look for explicit grants on non-role principals, that’s access bypassing the normal role-based model
• Get-LoginPermissions returning 0 rows — a healthy sign; explicit DENY or GRANT_WITH_GRANT_OPTION at the server level are unusual enough that any row here deserves a specific reason
• db_owner membership (WatchtowerMetrics/pete above), the highest-privilege database role; confirm every db_owner member genuinely needs full control of that specific database, not just broad convenience
• DemoDisabledAdmin showing sysadmin + is_disabled=True — exactly the kind of finding worth a second look during any access review, a disabled account sitting in the highest-privilege server role

---

Best Practices

• Run all four 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

---

Related Scripts

You may also find these scripts useful:

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

---

Frequently Asked Questions

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