< Back
Get-DbaLogin
Post
NAME Get-DbaLogin
SYNOPSIS
Function to get an SMO login object of the logins for a given SQL Server instance. Takes a server object from the
pipeline.
SYNTAX
Get-DbaLogin [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Login] <System.Object[]>] [[-IncludeFilter] <System.Object[]>] [[-ExcludeLogin]
<System.Object[]>] [[-ExcludeFilter] <System.Object[]>] [-ExcludeSystemLogin <Switch>] [[-Type] <String>]
[-HasAccess <Switch>] [-Locked <Switch>] [-Disabled <Switch>] [-Detailed <Switch>] [-EnableException <Switch>]
[<CommonParameters>]
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.
PARAMETERS
-Detailed [<Switch>]
A Switch to return additional information available from the LoginProperty function
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Disabled [<Switch>]
A Switch to return disabled Logins.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeFilter [<System.Object[]>]
A list of logins to exclude - accepts wildcard patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeLogin [<System.Object[]>]
The login(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystemLogin [<Switch>]
A Switch to remove System Logins from the output.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-HasAccess [<Switch>]
A Switch to return Logins that have access to the instance of SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeFilter [<System.Object[]>]
A list of logins to include - accepts wildcard patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Locked [<Switch>]
A Switch to return locked Logins.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Login [<System.Object[]>]
The login(s) to process - this list is auto-populated from the server. If unspecified, all logins will be
processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.You must have sysadmin access and server version must be SQL
Server version 2000 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<String>]
Filters logins by their type. Valid options are Windows and SQL.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Login, Security
Author: Mitchell Hamann (@SirCaptainMitch) | Rob Sewell (@SQLDBaWithBeard)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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
RELATED LINKS
https://dbatools.io/Get-DbaLogin
SYNOPSIS
Function to get an SMO login object of the logins for a given SQL Server instance. Takes a server object from the
pipeline.
SYNTAX
Get-DbaLogin [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Login] <System.Object[]>] [[-IncludeFilter] <System.Object[]>] [[-ExcludeLogin]
<System.Object[]>] [[-ExcludeFilter] <System.Object[]>] [-ExcludeSystemLogin <Switch>] [[-Type] <String>]
[-HasAccess <Switch>] [-Locked <Switch>] [-Disabled <Switch>] [-Detailed <Switch>] [-EnableException <Switch>]
[<CommonParameters>]
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.
PARAMETERS
-Detailed [<Switch>]
A Switch to return additional information available from the LoginProperty function
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Disabled [<Switch>]
A Switch to return disabled Logins.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeFilter [<System.Object[]>]
A list of logins to exclude - accepts wildcard patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeLogin [<System.Object[]>]
The login(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystemLogin [<Switch>]
A Switch to remove System Logins from the output.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-HasAccess [<Switch>]
A Switch to return Logins that have access to the instance of SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IncludeFilter [<System.Object[]>]
A list of logins to include - accepts wildcard patterns
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Locked [<Switch>]
A Switch to return locked Logins.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Login [<System.Object[]>]
The login(s) to process - this list is auto-populated from the server. If unspecified, all logins will be
processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances.You must have sysadmin access and server version must be SQL
Server version 2000 or higher.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<String>]
Filters logins by their type. Valid options are Windows and SQL.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Login, Security
Author: Mitchell Hamann (@SirCaptainMitch) | Rob Sewell (@SQLDBaWithBeard)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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
RELATED LINKS
https://dbatools.io/Get-DbaLogin