New command to get unused logins

[ddraillantclark]

Summarize Command's Functionality

This command would return logins that are not associated to a user, and which do not have permissions or ownership of any server-level object such as an endpoint.

The problem it would solve is finding extraneous logins which may for example have only been associated to a user for a database that no longer exists, so that the unused logins can be deleted to harden security.

Note: I am a DBA with a background in development, but have never contributed before, and if this gets the green light I propose to create the command.

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

The concept would be based on this TSQL query I found on stackexchange, but implemented as PowerShell.

https://dba.stackexchange.com/questions/250844/how-to-find-unused-logins-in-sql-server

[ddraillantclark]

I forgot to mention, I think that implementing this as a new command rather than adding it as a parameter in Get-DbaLogin would mirror what exists for users, as there is a separate Get-DbaDbOrphanUser command, which similar to the one I am proposing serves a distinct purpose as compared to Get-DbaDbUser.

[potatoqualitee]

Thank you for the report, @ddraillantclark! @ClaudioESSilva what do you think?

[ClaudioESSilva]

I think this would make sense @potatoqualitee. Maybe a Test command. Test-DbaLoginUnused ? or something like that.

@ddraillantclark do you want to give it a try? 😃

Also, I think the #8497 open by @berki7867 is a similar request. Do you confirm Berki? If yes maybe we can close the other issue and keep just this one.

[potatoqualitee]

hmmm @wsmelton you're better with naming, what do you think?

[wsmelton]

Name ideas:

• Get-DbaOrphanLogin
• Get-DbaInactiveLogin (my preference)
• Get-DbaUnusedLogin

I wouldn't necessarily use the exact columns returned by that query referenced but base it on the concept. As well determine whether our current commands already pull this data or if we need to really use T-SQL for it. Initial properties I'd recommend:

• ComputerName
• InstanceName
• SqlInstance
• Login
• Type
• CreateDate (dbadate)
• LastLoginDate (dbadate)
• IsAssignedRole (boolean)
• IsMappedToUser (boolean)

Naming of that last one could be better.

[potatoqualitee]

I prefer the look and sound of Get-DbaInactiveLogin but it doesn't exactly describe what the command is about - i think most of us tend to think of inactive as time where as unused is usage. So I think it should be Get-DbaUnusedLogin 🤔

[wsmelton]

Yeah, naming on this one will depend on the area the user works in. InfoSec tend to refer to them as "inactive" in policies but unused has the same meaning: it ain't used no mo'

A login that has no role or database access may still have a purpose (testing connectivity or something) and in those cases they will have to depend on the last time it logged in more over what role or access it has.

[ddraillantclark]

Just back from vacation. Going to have a go at implementing this.

My use case, as well as the one described in #8497, is limited to determining which logins have no permissions granted to them via a db user or server role. I'm going to add last login date per sys.dm_exec_sessions in the results for information only. I'm not going to add any parameter or logic to filter logins based on how far back the latest login goes, because the dynamic management only persists login information until the next service restart.

With that in mind, "inactive" doesn't accurately describe what the command returns. I'm going to go with Get-DbaUnusedLogin unless there are objections.

[sqlarticles]

It's been a while. Hope this isn't resolved yet. The inactive logins meaning might be different for each one of us. Get-DbaUnusedLogin sounds matching to this requirement.

I was thinking of doing this in two parts

• A parent function which returns the status of all the login. The output will be something like below. I couldn't think what other things a login can own at the server level. If anyone knew this, then the output can be ammended

SqlInstance         = DBATools2022
LoginName           = app_auto
DatabaseOwner       = 1
IsUserInDb          = 0
EndpointOwner       = 0
LinkedServerLogin   = 1
OwnsJobs            = 0
HasProxyAccess      = 1
UsedInCredential    = 0
LastLoginDate       = '2020-05-01 25:12:11'

• Another function which actually covers this use case and that then uses the parent function with different rules.

[wsmelton]

Your parent function doesn't cover the details being asked for in this request. I see no reason to try to identify what a given login might own, that is way out of scope for this.

[sqlarticles]

What I was trying to say is that the inactive definition changes per team/company/security team. Since dbatools is used widely, it should provide a parent function which exposes all the possible ownership of a login. We can write a wrapper function which then uses the output of the parent function and returns the output. By this way we also provide lot of other people to write their own rules on defining what is an inactive login.

[wsmelton]

Our commands have a single purpose when they gave a Get verb associated to it. This request is scoped to a command that only returns the last login value to indicate when it last authenticated. Details around ownership, role membership, database mapping...those are out of scope for this command.

If you want to propose a new command for reporting on ownership chain, please open a new request to discuss those details and the name of the command.