I wanted to know is there any functionality already available in SQL Server which will automatically removes logins and Users in SQL Server, whenever anybody leaves a organization without any impact. Here leaving from organization means its Windows NT ID has been disabled (Later point of time deleted) in the Active Directory immediately.
If the functionality is not available/cant be automated, then how we can achieve it. Through Powershell Script or some TSQL Script or Need to query AD.
I would like to explain the exact scenario which is happening in my organization as I feel, expalining it will explain my requirement more accurately:
SCENARIO: When Somebody leaves a organization, there is an Access Management team which sends a CSV file with details of those Employees to Windows/AD team. AD team with the help of that CSV will run a powershell script which will disable those accounts from AD. Now what happens is as a DBA we will not come to know when a person is leaving organization. If somebody leaves from DBA team, we will know that because he is part of our team but for others we will not come to know. So what is happening is we still have lot of Logins in SQL Servers of people who have left the organization.
What I am thinking is if somebody leaves the organization/been disabled from AD, is there any existing functionality in SQL Server or in AD which will notify us/SQL Server or do any action if some account is disabled from AD. In that case we will get notified then and there itself and then we can do the needful like removing dependency from his login, deleting login and users etc.
If functionality is not present, then what is the best way to get identified of it. Should I ask my AM team to send that CSV to us as well and we can run a powershell script against that CSV over all the SQL Servers to find the dependency and automatically remove it. Or there is some other more sophisticated way to achieve it.
Any Help is appreciated. Thanks in advance. :)