January 29, 2009 at 4:24 pm
Is there a script which deletes the users(left company)compeltely form the server.
1.What if they own any objects,schemas,db's etc.
2. What if the user was a sysadmin he might have owned many db's,jos too, deleting it may affect many jobs on the server.
3.Instead of going to each db and check them to delete is there a way we can do at one shot.
January 29, 2009 at 5:09 pm
I don't use a auto script as security is an auditable spec for me. I usually rename and disable the accounts for a start.
Then over time progress through each account on the databases in which they have rights and make decisions based on what is detailled. Reallocate schemas etc as needed and then delete user accounts and finally logins.
If there is more than one environment I save the sql scripts generated in the previous steps to run on the other servers. Doing the above steps ensures no functionality is lost and all steps are signed off providing auditors and management with due process.
Hope this helps
Adonia
January 29, 2009 at 5:25 pm
Here is my idea.
1. find out objects,schemas etc owned by user1
2. change ownership to my name
3.delete the account
can this process make the process run good after deleting. I am concerened becasue the user i am deleting had sysadmin previalges before
January 29, 2009 at 5:38 pm
Your plan sounds fine but as Adonia said in previous post, disable the user rather than deleting. In future, if you'll face any problem in any sql server object referencing to the old user, you can still enable the user and let the job finish its tasks. Once that job is complete, you can modify the affected object to point to new user and disable it again.
Leave the disables user for couple of weeks or months. It will not harm you even if its disabled.
January 29, 2009 at 5:49 pm
disable? do you mean disableing the windows login or is in sql server at server level to disable it.
January 29, 2009 at 5:55 pm
Disable the SQL Server login as this is ensures no-one can use the account, but it's still there.
Adonia
February 18, 2009 at 6:43 am
How would i find unused logins and verify if they owned any objects and then remove them.
is there any script which does all these at once.
thanks
February 24, 2009 at 11:27 am
How can i pull out if the user has owned any obejcts,scemas etc.. as i am deleting the user so that wanna make sure i change the ownership of those objects and then delete.
thanks
February 24, 2009 at 2:56 pm
Posted it as a blog post so it would be easier to find in the future. This works for SQL Server 2005 and above.
Determining if a Server Principal Owns Database Objects
K. Brian Kelley
@kbriankelley
February 24, 2009 at 3:00 pm
Plan B : disable the login and see if anything breaks or anybody complains that they can't access the server anymore.
Easy fix if you break something...
If you plan to delete the logins you'll have to go to that step anyways, so that's a good place to start. I'm sure Brian has more brillaint stuff in his article about ownership... another safe thing to do before doing anything to that login.
February 24, 2009 at 3:06 pm
Ninja's_RGR'us (2/24/2009)
Plan B : disable the login and see if anything breaks or anybody complains that they can't access the server anymore.Easy fix if you break something...
If you plan to delete the logins you'll have to go to that step anyways, so that's a good place to start. I'm sure Brian has more brillaint stuff in his article about ownership... another safe thing to do before doing anything to that login.
Sorry for the double post, I came in from the 5 most recent thread and I only saw Brian's post.
Needless to say that I agree with the ideas of the previous posters :hehe:.
February 26, 2009 at 11:48 am
I was able to check for the owners and change ownership, now i am deleting the login from the server, but deleting it from the server may not delete on each database, how wud i delete on all the databases too in a single step.
thanks
February 26, 2009 at 2:07 pm
something like sp_msforeachdb?
February 26, 2009 at 2:13 pm
Mike Levan (2/26/2009)
I was able to check for the owners and change ownership, now i am deleting the login from the server, but deleting it from the server may not delete on each database, how wud i delete on all the databases too in a single step.thanks
See this:
Mapping Database Principals to Server Principals
You could modify that to generate a script for you that basically says:
USE Database;
GO
DROP USER ThatUser;
GO
And they you output the results in text format, paste in into another query window, verify, and execute.
K. Brian Kelley
@kbriankelley
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply