sqmSQLTool › Commands › Get-sqmLoginSettings

Get-sqmLoginSettings Security

Queries sys.server_principals for all SQL and Windows logins with their default database, default language, enabled state, and timestamps. Supports multi-instance pipeline input, optional system-login exclusion, and CSV/HTML export.

Execution Flow

START begin: build $typeFilter ('S' | 'U','G' | 'S','U','G') from -LoginType; build SQL query SELECT name, type_desc, default_database_name, default_language_name, is_disabled, create_date, modify_date FROM sys.server_principals WHERE type IN ($typeFilter) AND name NOT LIKE '##%##' dbatools available? throw ERROR process: foreach ($instance in $SqlInstance) Invoke-DbaQuery -SqlInstance $instance -Query $query → $rows foreach ($row in $rows) if (-ExcludeSystemLogins): skip rows matching NT SERVICE\*, NT AUTHORITY\*, ##MS_*## if ($DefaultDatabase): skip rows where DefaultDatabase ≠ filter value if ($DefaultLanguage): skip rows where DefaultLanguage ≠ filter value $allResults.Add([PSCustomObject]@{ SqlInstance, LoginName, LoginType, DefaultDatabase, DefaultLanguage, IsDisabled, CreateDate, ModifyDate }) throw / continue end: optional export (if -OutputPath) Export-Csv → LoginSettings_yyyy-MM-dd.csv ConvertTo-sqmHtmlReport → LoginSettings_yyyy-MM-dd.html; Invoke-sqmOpenReport Return $allResults [List<PSCustomObject>] DONE

Synopsis

Supports pipeline input for -SqlInstance and iterates all specified instances in the process block. On each instance, a single Invoke-DbaQuery retrieves all logins from sys.server_principals. Results are filtered client-side by -ExcludeSystemLogins, -DefaultDatabase, and -DefaultLanguage. An optional CSV + HTML export is written in the end block.

Requires dbatools. The SQL query excludes double-hash accounts (##%##) server-side. -ExcludeSystemLogins additionally removes NT SERVICE\*, NT AUTHORITY\*, and ##MS_*## patterns client-side.

Syntax

Get-sqmLoginSettings
    [-SqlInstance <String[]>]          # pipeline; default $env:COMPUTERNAME
    [-SqlCredential <PSCredential>]
    [-LoginType <All|SQL|Windows>]     # default All
    [-ExcludeSystemLogins]
    [-DefaultDatabase <String>]
    [-DefaultLanguage <String>]
    [-OutputPath <String>]
    [-ContinueOnError]
    [-EnableException]
    [-NoOpen]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMEOne or more SQL Server instances. Accepts pipeline input.
-SqlCredentialPSCredential—Optional SQL connection credential.
-LoginTypeStringAllAll = SQL + Windows users + Windows groups; SQL = type S only; Windows = types U and G only.
-ExcludeSystemLoginsSwitchfalseSkip NT SERVICE\*, NT AUTHORITY\*, and ##MS_*## logins.
-DefaultDatabaseString—Filter: only return logins with this default database.
-DefaultLanguageString—Filter: only return logins with this default language (e.g. us_english).
-OutputPathString—Directory to write CSV and HTML report. No export when omitted.
-ContinueOnErrorSwitchfalseContinue to next instance on error instead of throwing.
-EnableExceptionSwitchfalseThrow terminating exceptions immediately.
-NoOpenSwitchfalseSuppress automatic opening of the generated HTML report.

Return Value

PropertyDescription
SqlInstanceSource instance name.
LoginNameLogin name from sys.server_principals.name.
LoginTypeSQL_LOGIN, WINDOWS_LOGIN, or WINDOWS_GROUP.
DefaultDatabaseDefault database for the login.
DefaultLanguageDefault language for the login.
IsDisabled$true when the login is disabled.
CreateDateLogin creation timestamp.
ModifyDateLogin last-modified timestamp.

Examples

# All logins on local instance
Get-sqmLoginSettings

# Windows logins only, exclude system accounts
Get-sqmLoginSettings -SqlInstance "SQL01" -LoginType Windows -ExcludeSystemLogins

# Find all logins pointing at master with German language
Get-sqmLoginSettings -SqlInstance "SQL01" -DefaultDatabase "master" -DefaultLanguage "Deutsch"

# Multi-instance export
Get-sqmLoginSettings -SqlInstance "SQL01","SQL02" -OutputPath "C:\Reports" -ContinueOnError