3

Trying to get a script that will identify which AD accounts/Windows authentication on a SQL Server are no longer active. Say perhaps an employee was released and the login would be removed from AD, but SQL Server still shows it in the logins.

Any assistance would be great.

closed as unclear what you're asking by hot2use, Tom V, Md Haidar Ali Khan, McNets, Max Vernon Oct 11 '18 at 18:44

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Hi, Welcome to the site.You need to clearly define your specifications of "no longer active". Is removing them from AD the only requirement? What about disabling in active directory? What about leaving them in AD but they don't need access to the SQL Server anymore (perhaps removed from a group?) etc. – Tom V Oct 10 '18 at 7:05
3

I'd highly recommend the dbachecks PS module which makes this (and pretty much every other important check) quite easy to run.

Running it is as simple as:

Invoke-DbcCheck -Checks ADUser -SqlInstance localhost 
1

If the user has actually been deleted then you can use xp_logininfo to see if they are really gone or not. But if the user is still in AD (but disabled), then you will have to either use Powershell or just send a list of users to your systems team to confirm.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logininfo-transact-sql?view=sql-server-2017

1

The method in use where I currently work is to have a small database that contains a list of AD logins (not their passwords, just to clarify), their status, as well as group memberships, refreshed every 12 hours using a Powershell script that queries AD for the data. All our SQL servers access this database via a linked server with a read-only login that is restricted to the service account in almost all cases.

We check this database daily via a SQL Agent job whether an AD login has been disabled, and if so, we are sent a list of the login(s), which we then go and manually remove. We don't do it automatically because the alerts are infrequent, and we confirm whether the login is supposed to be disabled before removing them.

An additional benefit of this approach is when you're writing reports, we can query this database, check which groups a login is a member of, and restrict the data returned to only that which is applicable to a given team/department/division/company.

Edit: Here's an example on StackOverflow of querying AD, and you can change the output to be a database. You can then schedule that job to run as often as you'd like to refresh the data.

Not the answer you're looking for? Browse other questions tagged or ask your own question.