Get-sqmLoginPermissions
Security & Audit
sqmSQLTool v1.9.x · Get
sys.server_role_members) and explicit server permissions, plus, per database, the mapped database user, its database roles and its explicit GRANT/DENY permissions. One flat row per permission, ready for CSV/HTML export and filtering.
Mapped by SID, not by name. A login and its database user can have different names (or the same name but different identities). Every join in this function goes through the SID, so the report reflects what SQL Server itself considers the same principal, not what the names suggest.
Orphaned users (a database user with no matching server login) are excluded by default. Add
-IncludeOrphanedUsers to include them, they get LoginName = '' and IsOrphaned = $true. With an active -Login filter, orphans are never shown, they don't belong to any of the requested logins by definition.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string[] | Optional | $env:COMPUTERNAME | One or more instances. Pipeline-capable. |
| -SqlCredential | PSCredential | Optional | , | SQL login credentials. |
| -Login | string[] | Optional | @() (all) | Filter: only these logins. Wildcards allowed. |
| -Database | string[] | Optional | @() (all) | Filter: only these databases. Wildcards allowed. |
| -ExcludeDatabase | string[] | Optional | @() | Databases to skip. Wildcards allowed. |
| -ExcludeSystemDatabases | switch | Switch | $false | Skip master, model, msdb, tempdb. |
| -ExcludeSystemLogins | switch | Switch | $false | Hide NT SERVICE\*, NT AUTHORITY\* (incl. localized variants) and ##MS_*##. |
| -IncludeOrphanedUsers | switch | Switch | $false | Also report database users with no matching server login. |
| -ExcludeServerScope | switch | Switch | $false | Skip server-level roles/permissions, database scope only. |
| -OutputPath | string | Optional | , | If given, CSV and HTML reports are written and copied to the central path. |
| -ContinueOnError | switch | Switch | $false | Continue with the next instance on error instead of aborting the whole run. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately. |
| -NoOpen | switch | Switch | $false | Do not automatically open the generated report after creation. |
Execution Flow
Output object
| Field | Contents |
|---|---|
SqlInstance / LoginName / LoginType / IsDisabled | Which login, on which instance. |
Scope | Server or Database. |
DatabaseName / UserName | Set for database-scope rows only. |
IsOrphaned | Database user with no matching server login (only present when -IncludeOrphanedUsers was given). |
PermissionType | Role (membership) or Permission (explicit GRANT/DENY). |
Permission / State / Class | The role or permission name, its state (GRANT/DENY/MEMBER), and the securable class. |
SecurableSchema / Securable | Schema and name of the object the permission applies to, where applicable. |
Examples
Full permission report for one instance
Get-sqmLoginPermissions -SqlInstance "SQL01"
Application logins only, skip system databases
Get-sqmLoginPermissions -SqlInstance "SQL01" -Login "APP_*" -ExcludeSystemDatabases
Include orphaned database users, export evidence
Get-sqmLoginPermissions -SqlInstance "SQL01" -IncludeOrphanedUsers -OutputPath "C:\Reports"
Just the orphans, across two instances
"SQL01", "SQL02" | Get-sqmLoginPermissions -IncludeOrphanedUsers | Where-Object IsOrphaned
Requirements
Requires dbatools. Needs VIEW ANY DEFINITION plus CONNECT on each database to be reported. Databases that are offline or inaccessible are skipped with a warning, not a hard failure.
See also
Get-sqmLoginLastAccess, when a login was last provably used.
Get-sqmSysadminAccounts, who currently holds sysadmin.