--- title: "Get-DbaUnusedLogin" description: "Finds logins that no database user maps to and that belong to no server role" url: "https://dbatools.io/Get-DbaUnusedLogin/" availability: "Windows, Linux, macOS" tags: ["Login", "Security", "Audit"] author: "the dbatools team + Claude" source: "https://github.com/dataplat/dbatools/blob/master/public/Get-DbaUnusedLogin.ps1" last_updated: "2024-01-01" --- # Get-DbaUnusedLogin ## Synopsis Finds logins that no database user maps to and that belong to no server role ## Description Returns the logins on an instance that nothing appears to be using, so you can review them before dropping them. A login is reported as unused when both of these are true: - No database user in any accessible database maps to the SID of the login. Users are matched on SID rather than name, so the dbo user of a database the login owns counts as a mapping and that login is not reported. - The login belongs to no server role. Membership of the public role is not counted, because every login belongs to public. SQL logins, Windows logins and groups, and the Entra ID (Azure AD) logins on SQL Server 2022 and Azure are all checked. Logins that SQL Server creates for itself are never reported, because they cannot be dropped: sa, the ## internal logins, and logins mapped to a certificate or an asymmetric key. Two things this command deliberately does not do, so read the results before acting on them: - It does not look at object ownership or at permissions granted straight to the login. A login can hold a server-level GRANT such as VIEW SERVER STATE, or own a job, an endpoint, a linked server or a credential, and still show up here. SQL Server itself refuses to drop a login that owns a database, an endpoint or a job, so Remove-DbaLogin surfaces that rather than silently breaking something. - It cannot see inside a database it cannot open. Databases that are offline, restoring or otherwise inaccessible are skipped with a warning, and a login mapped only in one of those is reported as unused. LastLogin comes from sys.dm_exec_sessions, which lists the sessions connected at this moment and nothing older. A login with a LastLogin value has a session open right now and is in use whatever the rest of the check concluded. An empty LastLogin only means the login is not connected right now, so it is not evidence that a login has never been used. ## Syntax ```powershell Get-DbaUnusedLogin [-SqlInstance] [[-SqlCredential] ] [[-Login] ] [[-ExcludeLogin] ] [-ExcludeSystemLogin] [[-Database] ] [[-ExcludeDatabase] ] [-EnableException] [] ``` ## Examples ### Example 1: Returns every login on sql2016 that no database user maps to and that belongs to no server role ```powershell PS C:\> Get-DbaUnusedLogin -SqlInstance sql2016 ``` ### Example 2: Same as above, but leaves out the NT AUTHORITY, NT SERVICE and BUILTIN logins that SQL Server creates during... ```powershell PS C:\> Get-DbaUnusedLogin -SqlInstance sql2016 -ExcludeSystemLogin ``` Same as above, but leaves out the NT AUTHORITY, NT SERVICE and BUILTIN logins that SQL Server creates during installation. ### Example 3: Reviews the unused logins on two instances, showing when each was created and whether it holds a session... ```powershell PS C:\> Get-DbaUnusedLogin -SqlInstance sql2016, sql2017 | Select-Object SqlInstance, Name, CreateDate, LastLogin ``` Reviews the unused logins on two instances, showing when each was created and whether it holds a session right now. ### Example 4: Checks only olduser1 and olduser2, returning the ones that are unused ```powershell PS C:\> Get-DbaUnusedLogin -SqlInstance sql2016 -Login olduser1, olduser2 ``` Checks only olduser1 and olduser2, returning the ones that are unused. A login that is in use returns nothing. ### Example 5: Drops every unused login on sql2016 ```powershell PS C:\> Get-DbaUnusedLogin -SqlInstance sql2016 -ExcludeSystemLogin | Remove-DbaLogin -Confirm:$false ``` Drops every unused login on sql2016. Review the list first, because ownership and direct permission grants are not part of the check. ### Example 6: Skips the ReportingArchive database when looking for database users ```powershell PS C:\> Get-DbaUnusedLogin -SqlInstance sql2016 -ExcludeDatabase ReportingArchive ``` Skips the ReportingArchive database when looking for database users. A login mapped only in that database is reported as unused. ### Required Parameters ##### -SqlInstance The target SQL Server instance or instances. | Property | Value | | --- | --- | | 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -Login Limits the check to the specified login names instead of every login on the instance. Use this to confirm whether particular accounts are still in use before you decommission them. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -ExcludeLogin Skips the specified login names. Useful for service accounts you already know are in use but that hold no role membership or database mapping, such as a monitoring account that connects and reads DMVs only. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -ExcludeSystemLogin Excludes the built-in Windows principals that SQL Server sets up during installation, meaning the NT AUTHORITY, NT SERVICE and BUILTIN accounts. Use this when auditing user accounts, and leave it off when you want to spot a leftover BUILTIN\Administrators login. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -Database Limits the search for database users to the specified databases. Accepts database names or arrays. Narrowing the databases also narrows what unused means, because a login mapped only in a database you left out is reported as unused. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -ExcludeDatabase Skips the specified databases when looking for database users. Commonly used to leave out a large database that is slow to enumerate. The same caution applies as for Database, because a login mapped only in a database you excluded is reported as unused. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ## Outputs **Microsoft.SqlServer.Management.Smo.Login** Returns one Login object per unused login found on the specified SQL Server instance(s). The object is the SMO login itself, so it pipes straight into Remove-DbaLogin, Set-DbaLogin and Export-DbaLogin. **Default display properties (via Select-DefaultView):** - ComputerName: The computer name of the SQL Server instance - InstanceName: The SQL Server instance name - SqlInstance: The full SQL Server instance name (computer\instance) - Name: The login account name - LoginType: The type of login (SqlLogin, WindowsUser, WindowsGroup, ExternalUser or ExternalGroup) - CreateDate: DateTime when the login was created - LastLogin: DbaDateTime the login opened the session it currently holds, from sys.dm_exec_sessions (null when the login is not connected right now, or on SQL Server 2000) - IsDisabled: Boolean indicating if the login is disabled - HasAccess: Boolean indicating if the login has permission to connect **Additional properties available:** - SidString: Hexadecimal string representation of the Security Identifier (SID) of the login - UncheckedDatabase: Names of databases that could not be opened and so were not searched for database users All properties from the base SMO Login object are accessible using Select-Object *. --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/