A SQL Server permission report can list explicit grants, show role memberships or check what a particular login can do. Each result answers a different question. A grant might come through a role, group or higher scope. Fixed database role permissions are implicit, and ownership or execution context can change what a principal can do at runtime.

Native catalog views and scripts are useful for collecting a transparent inventory. Dedicated tools can keep snapshots, compare states and help show how access was granted. For a disputed permission, testing the applicable SQL context is still useful. The report should make its scope, collection identity and treatment of inherited permissions clear.

SQL Server permission reporting tools at a glance

Separate permission inventory, access paths and runtime verification. Use the trial to see whether each output explains a grant through a role and an object-level deny.

Eight SQL Server permissions reporting options
ToolStarting evidenceBest use to evaluateReconciliation check
SQL Server catalog views and permission functionsDatabase principals, roles and permission rowsIndependent evidence and context-specific verificationCheck visibility, fixed roles and actual statement access
dbatools Get-DbaUserPermissionPowerShell report of SQL access evidenceRepeatable inventory across multiple instancesCompare object grant, deny and role membership fields
SQL Server Permissions Scripts by Kenneth FisherSQL permission inventory and scriptsReadable, reusable DBA investigationReview generated output and whether nested role paths are included
IDERA SQL SecureCollected security snapshot of SQL ServerCentral assessment and historical access reviewReconcile latest snapshot with both users' statement access
Netwrix Access AnalyzerPermissions job and access analysisCentral permissions reviewsTest which data sources determine a reported access path
Quest Enterprise Reporter for SQL ServerInventory and permissions reportsReport-driven collection across selected instancesConfirm collector scope and queryable membership detail
Netwrix Auditor State-In-Time ReportsHistorical permissions and means granted reportsBaseline review and privilege attributionReconcile report time and scan coverage
IDERA SQL Permissions ExtractorExtracted permissions and related definitionsPoint-in-time permissions exportCheck grant, deny and membership completeness before reuse

SQL Server catalog views and permission functions

SQL Server catalog views expose principals, role memberships and explicitly recorded permissions. Functions such as fn_my_permissions can report what is available in a particular execution context. They are useful when a DBA needs to trace a permission directly on the SQL Server that enforces it.

The useful distinction is inventory versus effective access. Catalog visibility depends on the collector’s permissions, and some rights from fixed roles are implicit. Effective access can also depend on role membership and execution context, so a raw list of granted rows only covers part of the answer.

Use sys.database_permissions alongside sys.database_role_members and the principal views. Microsoft documents that fixed database role privileges do not appear as ordinary grant rows in sys.database_permissions, so capture the relevant memberships too.

Use the actual requester context or a strictly scoped lab impersonation to check whether a statement succeeds. Record database, object, principal and UTC time together for reproducibility.

Microsoft Learn query joining SQL Server database role memberships to database principals.
Microsoft’s SQL example reads members of the fixed database roles. Documentation screenshot
Compatibility
Supported SQL Server catalog views and permission functions with appropriate visibility.
License and trial
Built-in SQL Server features.

dbatools Get-DbaUserPermission

Get-DbaUserPermission collects items such as server logins, database users, roles and object permissions across SQL Server targets. The PowerShell output is useful when a DBA wants comparable inventories across instances or needs to export them to an existing review workflow.

The command’s scope and defaults affect the report. It can include public and guest permissions or system objects when those subjects matter. The inventory helps find grants and memberships, while testing an effective permission still depends on the relevant SQL execution context.

Run Get-DbaUserPermission against one exact lab instance and save the module version and collection identity. Compare rows for the shared role and the principal receiving the direct deny.

Review the report before flattening permissions into a matrix. Preserve inheritance and deny details in exports. Use the native statement to settle the question if output does not fully explain the effective result.

dbatools documentation showing example Get-DbaUserPermission PowerShell commands.
The dbatools examples show how to limit Get-DbaUserPermission to selected SQL Server databases. Documentation screenshot
Compatibility
PowerShell with dbatools and appropriate SQL Server metadata access.
License and trial
Free, open-source module.

SQL Server Permissions Scripts by Kenneth Fisher

Kenneth Fisher’s SQL Server permissions scripts report on individual databases and servers. They are useful for a DBA who wants the collection logic in plain T-SQL and needs to inspect what is counted or filter the output for an investigation.

Their strength is visibility into the SQL that produces the inventory. Running and retaining results across a whole estate requires the team’s own automation. Catalog visibility, external group membership and implicit rights still need consideration when interpreting the report.

Check Kenneth Fisher's script version and return modes before running a lab inventory. Keep the role-membership output alongside explicit object permissions rather than interpreting each row in isolation.

Review generated scripts before using them elsewhere. Compare source and target database mappings and any manually scoped access in a test environment, then check actual access under a selected principal.

The sp_DBPermissions SQL script in its published GitHub repository.
Kenneth Fisher’s sp_DBPermissions source contains the parameters and permission inventory logic. Documentation screenshot
Compatibility
Supported SQL Server version and sufficient rights to inspect the relevant principals and objects.
License and trial
Public source scripts. Maintenance and execution remain with the team.

IDERA SQL Secure

IDERA SQL Secure collects SQL Server security information and uses snapshots and reports to analyze permissions. This is useful when a DBA or auditor wants to see access over time and review security settings across multiple servers through a managed product.

Its advantage is retained evidence and an established reporting workflow. Each snapshot covers the SQL Server and identity information the product could collect. Directory group expansion and the representation of effective access depend on the supported configuration and privileges.

Collect a SQL Secure snapshot after the disposable users and role exist. Open the report for a named object and account and check how the product distinguishes membership, direct assignment and effective access evidence.

Repeat through a separate snapshot after the lab change if permitted. Confirm which data the product retains and which findings relate to a collector permission, a missing identity mapping or a permission change.

IDERA SQL Secure interface with example security reports and report options.
SQL Secure’s publisher example shows the security reporting interface and report selection. Publisher example
Compatibility
SQL Secure with supported SQL Server targets and applicable collection identities.
License and trial
Commercial IDERA licensing.

Netwrix Access Analyzer

Netwrix Access Analyzer uses SQL permissions jobs to inventory rights on SQL Server. It is useful when database permissions should be part of a wider access review that includes other sources and when reviewers want to trace permissions to relevant principals or groups.

The benefit is bringing permission paths into a repeatable review. SQL and directory collection must cover the identities needed for that analysis. Results can be affected by collection schedules and the scope of the jobs, especially when membership changes between reviews.

Use the documented permissions job with the SQL Server scope and supporting identity data appropriate for the lab. Record the exact collector account so any metadata visibility gap can be investigated.

Trace the accessible and denied users back to their roles and securables. Compare the exported access path to the baseline query and make sure a restricted report reviewer can see enough evidence to explain it.

Netwrix Access Analyzer SQL Server permissions job selection panel.
Netwrix’s analysis selection screen shows the options for the SQL Server permissions job. Publisher example
Compatibility
Netwrix Access Analyzer with configured SQL permission jobs and required access.
License and trial
Commercial Netwrix licensing.

Quest Enterprise Reporter for SQL Server

Quest Enterprise Reporter can collect information from SQL Server and produce reports on database objects and permissions, including documented direct or indirect permission views. It fits organizations that already use Enterprise Reporter to allocate periodic discovery and reporting across infrastructure.

Its strength is reporting on a defined and retained discovery scope. The collection identity needs access to the server and databases included, and its permissions determine what can be reported. Effective operations still depend on SQL Server context at the time they are attempted.

Use the current SQL Server report library to select an inventory that includes both principals and relevant role or permission evidence. Confirm which targets are successfully discovered by the collector.

Save a report snapshot with its collection time, then compare both test users. Test focused reporting on one database and how the tool represents an inaccessible instance or missing permissions.

Quest Enterprise Reporter documentation showing SQL Server permissions and role-membership reports.
Quest’s report library lists SQL Server database object, role and account permission reports. Documentation screenshot
Compatibility
Quest Enterprise Reporter with SQL Server discoveries, supported targets and reporting permissions.
License and trial
Commercial Quest license.

Netwrix Auditor State-In-Time Reports

Netwrix Auditor State-In-Time reports can show the collected SQL Server permission state at a point in time. A Means Granted report helps review how access was assigned. This is useful when an audit asks what access existed or how a principal came to have it.

The useful boundary is the snapshot date and what it collected. Permission changes between snapshots and credentials outside the collected scope require other evidence. The report is strongest when reviewers know which servers and databases were included in that monitoring plan.

Use the SQL Server Means Granted report for the relevant point-in-time evidence and verify the collected SQL Server, principal and permission. Keep the report time distinct from the timestamp of the native query.

Check whether the table-level deny and shared role both appear, and which report gives the analyst access to each fact. Validate the user with a direct SQL statement after reviewing the report.

Netwrix Auditor SQL Server Means Granted report with permission details.
Netwrix’s SQL Server Means Granted report shows the source of the permission it found. Publisher example
Compatibility
Netwrix Auditor with a supported SQL Server monitoring plan and State-In-Time data.
License and trial
Commercial Netwrix licensing.

IDERA SQL Permissions Extractor

IDERA SQL Permissions Extractor can extract SQL Server permissions into a readable or scriptable form. It is useful when a DBA needs a focused look at assigned permissions or wants an artifact to review before reproducing selected grants on another instance.

Its scope is practical for export and review. An extracted grant script records the permissions the tool found, while effective access can also depend on role memberships and ownership. Applying any exported grant is a separate operation with its own target and review.

Scope the tool to the disposable database and export the fixture. Check whether the artifact includes the user, role membership, grant and deny required to reproduce the lab policy.

Keep the extraction separate from execution. If you evaluate migration, review the output first, apply approved changes in an independent disposable target and rerun the same effective read check.

IDERA SQL Permissions Extractor with the selected logins and generated script output.
IDERA’s example shows login selection and the output of a SQL permissions extraction. Publisher example
Compatibility
Supported Windows utility and SQL Server connection.
License and trial
Free IDERA utility under applicable publisher terms.

Choose the report your access reviewer needs

What you needTools to shortlistWhat to verify
Verify current effective access in a specific databaseExport explicit permissions and memberships. Test the action separately under the intended security context.
Collect recurring reports across SQL Server instancesCompare which principals, roles and objects are included and how exceptions or unresolved groups are reported.
Review existing permission snapshots or extract a portable inventoryRecord the snapshot timestamp or extraction scope. Retain the source and selected principals with the review.

Trace recorded grants to an actual SQL Server access decision

Role memberships, grants, denies and metadata visibility affect what a permissions inventory can show. An object-level deny can block an otherwise inherited grant. SQL Server also documents a column-level exception to the table-level deny behavior, so record column grants when they are in scope.

Use a native query alongside a report for one narrowly scoped test. Keep evidence of the collector's rights and the intended requester's session. Database-only impersonation in the fixture tests one local object and does not reproduce server login or external group behavior.

What does the permission review need to explain?
  • How current users and roles are configured

    Compare inventory and membership output

    Keep separate principal, role and permission evidence.

  • Why a named user can or cannot read a table

    Trace the access path and check the statement

    Inspect grants, denies, and actual execution under the intended context.

  • What changed between two points in time

    Use an attributable series of snapshots

    Keep collector identity, UTC time and scope with each finding.

Test two users with one shared SQL Server role

The fixture creates two users without logins inside an empty PermissionsTrial database, one user-defined role and one synthetic table. Both users inherit SELECT on the table. One also receives a direct object-level DENY. Execute the check as the authorized lab administrator and use the two specified impersonation steps.

The expected report shows a grant for the role and a deny for the restricted user. The effective access check should return the row for the permitted user and report error 229 for the restricted user. Verify that a reporting product exposes the path even if it has no built-in runtime check.

Expected SQL Server permission evidence for one table
EvidenceExpectedComparison
Role membershipBoth trial users are in TrialSharedReadExport the link from each user to the role
Object permissionsGRANT to role and DENY to restricted userRetain both permission states in reports
Permitted user checkThe SELECT returns the markerMatch access to the role grant
Restricted user checkSELECT raises permission error 229Match denial to the direct object permission

How to compare these tools

Compare reports with an access check run as the user. Include direct grants, group membership, nested roles and a deny. Confirm how the report identifies which grant made the access possible. Sources checked .

Trace a nested role grant and confirm a direct deny

Create an empty disposable PermissionsTrial database and use an administrator authorized to create test users, roles and a table. Run Setup once. Run Allow and Deny separately after the scanner collects the inventory. Both test users are local to the database and have no logins.

SQL

Reconcile catalog grants and an object deny with a live SELECT

T-SQL · 52 lines

SET NOCOUNT ON;IF DB_NAME() <> N'PermissionsTrial' OR @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2    THROW 51000, 'Use the empty disposable PermissionsTrial database and autocommit.', 1; DECLARE @Step varchar(5) = 'Allow'; -- Setup, Allow or DenyIF @Step NOT IN ('Setup','Allow','Deny')    THROW 51001, 'Choose Setup, Allow or Deny.', 1; IF @Step = 'Setup'BEGIN    IF OBJECT_ID(N'dbo.PermissionTrialRows', N'U') IS NOT NULL       OR USER_ID(N'PermitTrialReader') IS NOT NULL       OR USER_ID(N'DenyTrialReader') IS NOT NULL       OR DATABASE_PRINCIPAL_ID(N'TrialSharedRead') IS NOT NULL        THROW 51002, 'Use an unused fixture table and principal names.', 1;    SET XACT_ABORT ON;    BEGIN TRAN;    CREATE TABLE dbo.PermissionTrialRows (RowId int PRIMARY KEY, Marker int NOT NULL);    INSERT dbo.PermissionTrialRows VALUES (1,42);    CREATE USER PermitTrialReader WITHOUT LOGIN;    CREATE USER DenyTrialReader WITHOUT LOGIN;    CREATE ROLE TrialSharedRead;    ALTER ROLE TrialSharedRead ADD MEMBER PermitTrialReader;    ALTER ROLE TrialSharedRead ADD MEMBER DenyTrialReader;    GRANT SELECT ON OBJECT::dbo.PermissionTrialRows TO TrialSharedRead;    DENY SELECT ON OBJECT::dbo.PermissionTrialRows TO DenyTrialReader;    COMMIT;    RETURN;END; SELECT dp.name AS Grantee, p.state_desc AS PermissionState, p.permission_nameFROM sys.database_permissions AS pJOIN sys.database_principals AS dp ON dp.principal_id = p.grantee_principal_idWHERE p.class = 1 AND p.major_id = OBJECT_ID(N'dbo.PermissionTrialRows')  AND p.minor_id = 0 AND p.permission_name = N'SELECT'; IF @Step = 'Allow'    EXECUTE AS USER = 'PermitTrialReader';ELSE    EXECUTE AS USER = 'DenyTrialReader'; BEGIN TRY    SELECT USER_NAME() AS TrialUser, SYSUTCDATETIME() AS ObservedUtc;    EXEC sys.sp_executesql N'SELECT Marker FROM dbo.PermissionTrialRows WHERE RowId = 1;';END TRYBEGIN CATCH    REVERT;    IF ERROR_NUMBER() <> 229 THROW;    SELECT ERROR_NUMBER() AS SqlError, ERROR_MESSAGE() AS ErrorMessage;    RETURN;END CATCH;REVERT;
Review before runningT-SQLUTF-852 lines
  1. Use Setup to create the users and grant path. Compare the product output for the role, two members and direct deny under an authorized report collector.
  2. Run Allow and Deny to check runtime access. Expect marker 42 for Allow and error 229 for Deny. Record SQL Server version, database, tool collection time and known query results.
  3. Compare whether the selected permission report can explain both outcomes. Also verify what it shows when a scanner account cannot see the relevant object or membership.

Conclusion

Choose a report that shows enough detail to explain an access decision. Keep the explicit permission, role path, principal, target and collection time together. When the decision depends on a real operation or current group membership, verify it from the user’s actual SQL context.

Frequently asked questions

What is the difference between an explicit SQL Server permission and effective access?

An explicit permission is a grant, deny or related permission entry assigned to a principal. Effective access also depends on the applicable role memberships, identity, securable and SQL Server’s permission rules. Verify the action under the intended security context.

Why can a user read a SQL Server table without a direct SELECT grant?

The user may inherit SELECT through a role, a group, or permissions on a containing schema or database. Trace the membership and grant path. Check the actual object under the user’s SQL context.

Does sys.database_permissions show every SQL Server permission?

It reports database permission entries and has metadata visibility rules. Built-in permissions from fixed database roles can require separate membership and role analysis. Use role catalogs and effective permission checks for the required scope.

Does DENY always override GRANT in SQL Server?

SQL Server generally applies an explicit deny over a grant in the relevant context. Exceptions include certain owner and sysadmin contexts and the documented column-level GRANT behavior for a table-level DENY. Verify the exact securable and action.

What should a SQL Server access review export include?

Record the principal, database or instance, object, operation, grant source, role path or group source, collection time and review decision. Keep the relevant effective-access test when the report is used to decide whether an action is allowed.

Why can a role grant and a direct deny appear for the same SQL table?

The records apply to different principals. This fixture gives both users a role grant and applies an object-level deny to one. Check the full permission path and actual access before drawing a conclusion.

Can database impersonation verify a Windows group login?

Use a fresh connection as the intended Windows principal. The fixture checks permissions for a local database user. Verify server authentication and group mappings separately.

Related guides

Guide~12 min readUpdated 16 Nov 2026
LinkedInXEmail