2

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. :)

6
  • 6
    Use Windows groups rather than specific logins. Then you won't have to remove anything. – Jonathan Fite Sep 22 '16 at 12:15
  • 2
    You can write TSQL Scripts / Power Shell / .NET custom app, usually the biggest challenge whenever removing users from a database is to make sure that their login is not associated with any SQL objects. Before you remove a user, you want to make sure that user is not listed as Owner of any objects: Databases, SQL Jobs and etc. – Vladimir Oselsky Sep 22 '16 at 12:41
  • @JonathanFite If you are talking Active Directory Security Groups then I am not aware of it at all as I doesnot belong to AD/Windows team. I am looking issue as SQL Server DBA. – sachin-SQLServernewbiee Sep 22 '16 at 13:29
  • @VladimirOselsky Can you please share some links or provide me some rough idea on how we can achieve it. My end objective is If any of the user is disabled in AD, I want to first remove the dependency from his login on SQL Server and then delete the Login. Basically I want to automate it if not complete atleast partial. – sachin-SQLServernewbiee Sep 22 '16 at 13:32
  • 1
    @sachin-SQLServernewbiee you specify in the OP that "query AD" is an option. The long-term solution to this problem really is to have a relationship with your domain admins where you can start to use AD group-based authentication even if you need powershell trickery to identify & remediate any pre-existing credentials mess. – Peter Vandivier Sep 23 '16 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.