June 12, 2012 at 1:50 pm
How do you handle offboarding of users from SQL when they leave the company?
We currently don't have a script or mechanism in place to take care of this therefore it is done manually, going through each SQL Server Instance. We don't have that many SQL Server Instances...but what if you do ?
There should be a better way of doing this. Can everyone share how this is being done in your organization, or if there is a script in place to do this. I might be able to use it as a starting point to come up with a solution.
If this post is in the incorrect section, kindly relocate it. I was not sure where this question should fall.
June 12, 2012 at 1:53 pm
My initial reaction is why aren't you using Active Directory groups via Windows to manage this? When a person leaves, their login is deactivated, and that means they can't get to anything.
If your users have independent SQL Logins, well, you're stuck with the pain. That's part of why we almost exclusively use WinNT logins (or groups) for access to the servers and only give out dedicated SQL Logins to things like websites and other internal system users.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2012 at 7:22 am
Totally agree with you, thats how it should be. But its not the way here. Users ( QA/Developers) are assigned to different database's based on their projects. Not everyone with the same role has access to same database with same level of permissions. Also there is 'authority' problem between the windows admin team and the rest of the IT team.
In simple words its a mess..... Thats the reason I was looking for a way to easily script out these users when offboarding happens.
June 14, 2012 at 4:28 am
hi good question..
i too have same doubt...
can any one plz give ur suggestions???
thnaks
June 14, 2012 at 8:04 am
Many homegrown and 3rd party products utilize SQL logins, schema, permissions, roles and rights. The good apps clean up their messes the bad ones leave that to DBA's. If you find your self in the driver seat of cleaning up these things then you have to automate it as you go throught it. Start small and continually look for more improvements. Maybe start with making HR or IT or Dev fill out a termination Form that has the term'd persons name or empID (the data you need stored to table) then work backwards from there creating stored procedures for each thing you do so that it can accept passed parameters from the form HR fills out. Once you have a procedure driven process you can hopefull make it as simple as they fill out a form and your SQL Agent listener job pick it up and processes it automatically dropping the users logins.
June 15, 2012 at 3:55 am
I used to support a database that was used globally and ran into a similar situation where people from the other sites would leave the company and I wouldn't be notified. That caused the database to become full of inactive users. To overcome this, a linked server to Active Directory was setup as well as a job that ran weekly which identified database users that were no longer in AD. It would then send out an email listing any database users that couldn't be found in AD. After verifying that the users truly left the company, they would be dropped from the database. The last step wasn't automated just incase something went wrong during the AD query.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply