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.
As much as possible you should use Active Directory Groups and apply database permissions only to those groups. That way when a person leaves the company they simply have to be removed as a user, which deletes them from the groups, and consequently their SQL access.
If you have to use SQL authentication, you should have separate SQL logins for each person and their permissions should be managed via database roles. Users should not have permissions granted to them directly, it should only be done via role membership. If they leave you simple need to remove the user from the role and drop the SQL login.
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.
If you match SQL logins to their Windows login, you can query AD and get all users then match that against your SQL logins. I've done this before using the DSQUERY and DSGET utilities under Windows 2003. It's also available in Windows 2008 and higher if you install the Active Directory feature. You can also try setting up a linked server to Active Directory, but it's complicated and quirky. Log Parser might be easier:
Offboarding Users
Posted - 06/12/2012 : 15:50:56
