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.
Function to get an SMO login object of the logins for a given SQL Server instance. Takes a server object from the pipeline. SQL Azure DB is not supported.
The Get-DbaLogin function returns an SMO Login object for the logins passed, if there are no users passed it will return all logins.
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 |
The login(s) to process - this list is auto-populated from the server. If unspecified, all logins will be processed.
Alias | |
Required | False |
Pipeline | false |
Default Value |
A list of logins to include - accepts wildcard patterns
Alias | |
Required | False |
Pipeline | false |
Default Value |
The login(s) to exclude. Options for this list are auto-populated from the server.
Alias | |
Required | False |
Pipeline | false |
Default Value |
A list of logins to exclude - accepts wildcard patterns
Alias | |
Required | False |
Pipeline | false |
Default Value |
A Switch to remove System Logins from the output.
Alias | ExcludeSystemLogins |
Required | False |
Pipeline | false |
Default Value | False |
Filters logins by their type. Valid options are Windows and SQL.
Alias | |
Required | False |
Pipeline | false |
Default Value | |
Accepted Values | Windows,SQL |
A Switch to return Logins that have access to the instance of SQL Server.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
A Switch to return locked Logins.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
A Switch to return disabled Logins.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
A Switch to return Logins that need to change password.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
A Switch to return additional information available from the LoginProperty function
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 |