August 26, 2009 at 5:30 am
Hello, I don't have much practice, especially with current task, so I'm asking for a help.
company has 250+ SQL servers with multiple databases. I'm here the only one who manages SQL and I got no documentation.
last DBA was using his own domain login in all his SQL activity: creating DB, procedures, jobs.
he's not working for a long time there is about 6 moth gap between him going out and me going in,
Domain admins noticed me, that they want to delete his login in Domain (not to disable, but delete, I think they have reasons).
what are the steps should I do (in SQL) before Domain admins will delete his login.
or just delete login from all servers, user from databases and if there is some more the server itself will tell me?!
what to check and where to look.
I'm a bit afraid of whole system collapse.
p.s.
what about reports and analysis servers?
August 26, 2009 at 7:38 am
you need to check object ownership and change them all that are currently under his/her login to new ownership.
August 26, 2009 at 11:42 pm
Followign code might help to find the object owned by him in a current database:
select * from sys.objects o join sys.schemas s
on s.schema_id=o.schema_id
and s.principal_id=user_id('Username')
or o.principal_id=user_id('Username')
HTH!
MJ
August 26, 2009 at 11:53 pm
thanks, as far as I understood I should check: Database owners, db objects, Agent Job owners.
what more? there were something about deleting user who granted some rights to others?
What about Analysis and Reports ?
August 28, 2009 at 9:34 am
Before you delete the login on any server, I would suggest that you change ownership on all of the objects that you can find and test to see that everything still works. After that is successful in 2005 you can disable the login. I would sugest going a minimm of 35 days with the login disabled before deleting it. You might be surprised at the monthly and even quarterly reports that run. If you find that a report is missing you can generally track down a failed login on the machine it was suppose to run on, enable the login, run the report, and then change the owner of the report.....
Good Luck,
John Campbell
August 28, 2009 at 10:12 am
http://www.sql-server-performance.com/articles/dba/security_audit_database_level_p1.aspx
There are some scripts in this link which will give you object level permissions. You can modify them for your task to find to filter particular domain account. Also as people pointed out some jobs may run longer intervals. So you will have to be careful with that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply