September 8, 2009 at 1:23 pm
What is the best practice for removing a user once they leave the company? Do we just go in to each database and right-click delete the user, then right-click delete the Login through the GUI? Or should we run the EXEC sp_dropuser 'John' on each database then run the EXEC sp_droplogin 'John'? What is the best, most efficient approach? Also, user hase to be removed before login correct?
September 8, 2009 at 1:42 pm
this is in the 2005 forum so presuming you are on 2005.
the quick way to do it is via SSMS, that drops the login and associated users for you. However prone to failure if the user owns any objects. Also what it does is under the covers so if this is a process you expect to do on a regular basis I would script it up. drop user from the database(s) first then drop the login.
Get into the habit of using new sql 2005 syntax, so sp_revokedbaccess and drop login rather than old sp's which will be deprecated at some point.
You may have to take into account schemas owned by the user, if applicable.
---------------------------------------------------------------------
September 8, 2009 at 1:52 pm
Good advice. I appreciate it.
Thanks
September 9, 2009 at 4:54 am
Better to remove the users before the login, check as well that the user doesnt own any objects and if they used to do backups and restores, make sure that they are not down as the database owner, check to see if they own any jobs or their account is used to run any jobs. anywhere where their account/login could be hardcoded.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 6:07 pm
a common and good practice is to use windows authentication and just disable their accounts.
September 10, 2009 at 3:36 am
Marcin Gol [SQL Server MVP] (9/9/2009)
a common and good practice is to use windows authentication and just disable their accounts.
......and of course if you have reached the nirvana of all access via membership of windows groups then all you have to do is get a sysadmin to remove them from the group and you don't even have to do any SQL work!
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply