commands

^

Get-DbaLogin

Author Mitchell Hamann (@SirCaptainMitch) , Rob Sewell (@SQLDBaWithBeard)
Availability Windows, Linux, macOS

 

Synopsis

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.

Description

The Get-DbaLogin function returns an SMO Login object for the logins passed, if there are no users passed it will return all logins.

Syntax

Get-DbaLogin
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Login] <String[]>]
    [[-IncludeFilter] <String[]>]
    [[-ExcludeLogin] <String[]>]
    [[-ExcludeFilter] <String[]>]
    [-ExcludeSystemLogin]
    [[-Type] <String>]
    [-HasAccess]
    [-Locked]
    [-Disabled]
    [-MustChangePassword]
    [-Detailed]
    [-EnableException]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Get-DbaLogin -SqlInstance sql2016

Gets all the logins from server sql2016 using NT authentication and returns the SMO login objects

Example: 2
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

Example: 3
PS C:\> Get-DbaLogin -SqlInstance sql2016 -SqlCredential $sqlcred -Login dbatoolsuser,TheCaptain

Get specific logins from server sql2016 returned as SMO login objects.

Example: 4
PS C:\> Get-DbaLogin -SqlInstance sql2016 -IncludeFilter '##*','NT *'

Get all user objects from server sql2016 beginning with '##' or 'NT ', returned as SMO login objects.

Example: 5
PS C:\> Get-DbaLogin -SqlInstance sql2016 -ExcludeLogin dbatoolsuser

Get all user objects from server sql2016 except the login dbatoolsuser, returned as SMO login objects.

Example: 6
PS C:\> Get-DbaLogin -SqlInstance sql2016 -Type Windows

Get all user objects from server sql2016 that are Windows Logins

Example: 7
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

Example: 8
PS C:\> Get-DbaLogin -SqlInstance sql2016 -Type SQL

Get all user objects from server sql2016 that are SQL Logins

Example: 9
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

Example: 10
PS C:\> Get-DbaLogin -SqlInstance sql2016 -ExcludeSystemLogin

Get all user objects from server sql2016 that are not system objects

Example: 11
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.

Example: 12
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.

Example: 13
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.

Example: 14
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.

Example: 15
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

Example: 16
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.

Required Parameters

-SqlInstance

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

Optional Parameters

-SqlCredential

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
-Login

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
-IncludeFilter

A list of logins to include - accepts wildcard patterns

Alias
Required False
Pipeline false
Default Value
-ExcludeLogin

The login(s) to exclude. Options for this list are auto-populated from the server.

Alias
Required False
Pipeline false
Default Value
-ExcludeFilter

A list of logins to exclude - accepts wildcard patterns

Alias
Required False
Pipeline false
Default Value
-ExcludeSystemLogin

A Switch to remove System Logins from the output.

Alias ExcludeSystemLogins
Required False
Pipeline false
Default Value False
-Type

Filters logins by their type. Valid options are Windows and SQL.

Alias
Required False
Pipeline false
Default Value
Accepted Values Windows,SQL
-HasAccess

A Switch to return Logins that have access to the instance of SQL Server.

Alias
Required False
Pipeline false
Default Value False
-Locked

A Switch to return locked Logins.

Alias
Required False
Pipeline false
Default Value False
-Disabled

A Switch to return disabled Logins.

Alias
Required False
Pipeline false
Default Value False
-MustChangePassword

A Switch to return Logins that need to change password.

Alias
Required False
Pipeline false
Default Value False
-Detailed

A Switch to return additional information available from the LoginProperty function

Alias
Required False
Pipeline false
Default Value False
-EnableException

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

 

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.