| Author | Mitchell Hamann (@SirCaptainMitch) , Rob Sewell (@SQLDBaWithBeard) |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Get-DbaLogin on GitHub.
Want to see the Bill Of Health for this command? Check out Get-DbaLogin.
Retrieves SQL Server login accounts with filtering options for security audits and access management
Returns detailed information about SQL Server login accounts, including authentication type, security status, and last login times. This function helps DBAs perform security audits by identifying locked, disabled, or expired accounts, and distinguish between Windows and SQL authentication logins. Use it to troubleshoot access issues, generate compliance reports, or review login configurations across multiple instances.
Get-DbaLogin
[-SqlInstance] <DbaInstanceParameter[]>
[[-SqlCredential] <PSCredential>]
[[-Login] <String[]>]
[[-IncludeFilter] <String[]>]
[[-ExcludeLogin] <String[]>]
[[-ExcludeFilter] <String[]>]
[-ExcludeSystemLogin]
[[-Type] <String>]
[-HasAccess]
[-Locked]
[-Disabled]
[-MustChangePassword]
[-Detailed]
[-EnableException]
[<CommonParameters>]
PS C:\> Get-DbaLogin -SqlInstance sql2016
Gets all the logins from server sql2016 using NT authentication and returns the SMO login objects
PS C:\> Get-DbaLogin -SqlInstance sql2016 -SqlCredential $sqlcred
Gets all the logins for a given SQL Server using a passed credential object and returns the SMO login objects
PS C:\> Get-DbaLogin -SqlInstance sql2016 -SqlCredential $sqlcred -Login dbatoolsuser,TheCaptain
Get specific logins from server sql2016 returned as SMO login objects.
PS C:\> Get-DbaLogin -SqlInstance sql2016 -IncludeFilter '##*','NT *'
Get all user objects from server sql2016 beginning with '##' or 'NT ', returned as SMO login objects.
PS C:\> Get-DbaLogin -SqlInstance sql2016 -ExcludeLogin dbatoolsuser
Get all user objects from server sql2016 except the login dbatoolsuser, returned as SMO login objects.
PS C:\> Get-DbaLogin -SqlInstance sql2016 -Type Windows
Get all user objects from server sql2016 that are Windows Logins
PS C:\> Get-DbaLogin -SqlInstance sql2016 -Type Windows -IncludeFilter *Rob*
Get all user objects from server sql2016 that are Windows Logins and have Rob in the name
PS C:\> Get-DbaLogin -SqlInstance sql2016 -Type SQL
Get all user objects from server sql2016 that are SQL Logins
PS C:\> Get-DbaLogin -SqlInstance sql2016 -Type SQL -IncludeFilter *Rob*
Get all user objects from server sql2016 that are SQL Logins and have Rob in the name
PS C:\> Get-DbaLogin -SqlInstance sql2016 -ExcludeSystemLogin
Get all user objects from server sql2016 that are not system objects
PS C:\> Get-DbaLogin -SqlInstance sql2016 -ExcludeFilter '##*','NT *'
Get all user objects from server sql2016 except any beginning with '##' or 'NT ', returned as SMO login objects.
PS C:\> 'sql2016', 'sql2014' | Get-DbaLogin -SqlCredential $sqlcred
Using Get-DbaLogin on the pipeline, you can also specify which names you would like with -Login.
PS C:\> 'sql2016', 'sql2014' | Get-DbaLogin -SqlCredential $sqlcred -Locked
Using Get-DbaLogin on the pipeline to get all locked logins on servers sql2016 and sql2014.
PS C:\> 'sql2016', 'sql2014' | Get-DbaLogin -SqlCredential $sqlcred -HasAccess -Disabled
Using Get-DbaLogin on the pipeline to get all Disabled logins that have access on servers sql2016 or sql2014.
PS C:\> Get-DbaLogin -SqlInstance sql2016 -Type SQL -Detailed
Get all user objects from server sql2016 that are SQL Logins. Get additional info for login available from LoginProperty function
PS C:\> 'sql2016', 'sql2014' | Get-DbaLogin -SqlCredential $sqlcred -MustChangePassword
Using Get-DbaLogin on the pipeline to get all logins that must change password on servers sql2016 and sql2014.
The target SQL Server instance or instances.You must have sysadmin access and server version must be SQL Server version 2000 or higher.
| Alias | |
| Required | True |
| Pipeline | true (ByValue) |
| Default Value |
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies specific login names to retrieve instead of returning all logins from the instance.
Use this when you need information about particular accounts for troubleshooting access issues or security audits.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Includes only logins matching the specified wildcard patterns (supports * and ? wildcards).
Use this to find groups of related logins, such as all domain accounts from a specific organizational unit or service accounts with naming conventions.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Excludes specific login names from the results.
Useful when you want all logins except certain service accounts or system logins that you don't need to review.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Excludes logins matching the specified wildcard patterns (supports * and ? wildcards).
Commonly used to filter out system accounts or built-in logins when focusing on user accounts during security reviews.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Excludes built-in system logins like sa, BUILTIN\Administrators, and NT AUTHORITY accounts from results.
Use this when performing user access audits where you only want to see custom logins created for applications and users.
| Alias | ExcludeSystemLogins |
| Required | False |
| Pipeline | false |
| Default Value | False |
Filters results to show only Windows Authentication logins or SQL Server Authentication logins.
Use 'Windows' to review domain accounts and local Windows users, or 'SQL' to audit SQL Server native accounts that store passwords in the database.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | |
| Accepted Values | Windows,SQL |
Returns only logins that currently have permission to connect to the SQL Server instance.
Use this to verify which accounts can actually access the server, as some logins may exist but be denied connection rights.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Returns only login accounts that are currently locked due to failed authentication attempts.
Use this to identify accounts that may need to be unlocked or investigate potential security incidents.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Returns only login accounts that have been disabled but not dropped from the server.
Use this to identify inactive accounts that should be reviewed for cleanup or re-enabling for returning employees.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Returns only SQL Server logins that are flagged to change their password on next login.
Use this to identify accounts with temporary passwords or those requiring password updates due to security policies.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Includes additional security-related properties like bad password count, password age, and lockout times.
Use this for comprehensive security audits when you need detailed information about password policies and authentication failures.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |