Permissions Matrix
Overview
Logins, Orphan Users, Security Posture and Server Permissions cover the server side of security. None of them answers the question an auditor starts with: what can this login do in every database?
The Permissions Matrix report answers it on one page, with three views:
- Matrix – a row for every login (Windows groups included) and a column for every database. Each cell names what the login’s user holds there, and is colored by how much that adds up to.
- Findings – the risky access, worst first.
- Permissions – every explicit GRANT and DENY in every database, with the principal, the login it maps to, the securable and the grantor.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Permissions Matrix |
| Instance reports navigator | Security group, after Logins |
| Related Links bar | From Logins, Orphan Users and Security Posture |
| Report arrows | Previous is Performance History, next is Problem Indexes |
Reading the matrix
| Cell | Meaning |
|---|---|
| SA | The login is sysadmin: it can do everything in every database, whatever the user says |
| DBO | The login owns the database, so it maps to dbo |
| O | db_owner |
| SEC, AA, DDL, BK | db_securityadmin, db_accessadmin, db_ddladmin, db_backupoperator |
| W, R | db_datawriter, db_datareader |
| -W, -R | db_denydatawriter, db_denydatareader |
| a role name | A custom role the user was added to directly |
| +N | N explicit permissions on the user itself (the default GRANT CONNECT is not counted) |
| public | A user with no roles and no explicit permissions |
| no connect | The user exists but CONNECT is not granted |
| guest | No user of its own, but the guest user is enabled, so the login gets in as guest |
| ? | The database could not be read (offline, or this login cannot open it) |
Nested roles are expanded: a user in a custom role that is itself in db_datareader shows R as well as the custom role’s name.
Cells are colored by the most the login can do there: red for owner level (SA, DBO, db_owner, CONTROL on the database), orange for administrative (db_securityadmin, db_accessadmin, db_ddladmin, CONTROL, IMPERSONATE, ALTER ANY), amber for changing data (db_datawriter, db_backupoperator, INSERT, UPDATE, DELETE, EXECUTE) and blue for reading. Disabled logins are grayed.
Click a database cell to open the pane on the right. The upper box lists every role the user is in (and the chain of roles that got it there), every explicit permission on the user, and every permission it gets through a role. The lower box is the T-SQL that grants the same role memberships and explicit permissions (ALTER ROLE … ADD MEMBER, GRANT, DENY), for the record or to copy to another user; nothing is run. Click a login’s own columns to see what it has in every database.
Findings
| Finding | Severity |
|---|---|
| Granted to public (user databases): a permission on the database or a schema, or CONTROL and the like | Problem |
| Granted to public on a user object | Warning |
| Guest enabled in a user database | Warning |
| db_owner member (directly or through a role) whose login is not sysadmin | Warning |
| CONTROL granted on the database | Problem |
| CONTROL or IMPERSONATE granted on anything else | Warning |
| TRUSTWORTHY is ON in a user database | Warning, Problem when the owner is sysadmin |
| Cross database ownership chaining on a user database | Warning |
| Owner has no login (the owner SID matches no login) | Warning |
| Not read, Partly visible | Info |
The grants every user database gives public by default (VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION), and the thousands of grants to public on SQL Server’s own system objects, are left out. Guest in master, msdb and tempdb, and TRUSTWORTHY in msdb, are the way SQL Server ships and are not findings.
Filters and export
- Login search box: type part of a login name and press Enter. In Findings and Permissions it matches the login or the database user.
- System databases and Disabled logins toggle those columns and rows on and off.
- Save Workbook writes one Excel file: a Matrix summary sheet (with the key), a Findings sheet, and one sheet per database listing every role membership and explicit permission. The filters on screen carry over.
- Every grid also has the usual CSV and Excel export from its right-click menu.
Windows groups: right-click a Windows group and choose Show Members to ask the domain, with xp_logininfo, who is in it. This is done one group at a time, only when asked, because it can be slow and needs the domain to be reachable from the server.
Permissions and versions
- Each database is read on its own, so one that is offline, restoring or closed to this login shows ? with the reason in the tooltip and a Not read finding, and the rest of the page is unaffected.
- Seeing every login needs VIEW ANY DEFINITION (or sysadmin). Without it the matrix shows only your own login.
- Seeing every user and permission in a database needs VIEW DEFINITION there. Without it the database is marked Partly visible, and cells for other logins show ? rather than a blank that would claim they have no access.
- Users are matched to logins by SID, as SQL Server does, so dbo maps to the database owner and a Windows group’s user maps to the group’s login. Users with no login (orphaned users) are on the Orphan Users report.
- The report reads only catalog views and works on every supported version. The T-SQL in the pane uses ALTER ROLE on SQL Server 2012 and later and sp_addrolemember before that.