SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Database Cleanup when Admin is Leaving
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ITTrucker
Yak Posting Veteran

USA
52 Posts

Posted - 03/24/2014 :  17:08:45  Show Profile  Reply with Quote
One of our IT staff is leaving and I want to double check everything in the database to see if there's anything that will stop working when we disable their login. I checked to see if they were the owner of any jobs with:
SELECT s.name,l.name
FROM msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid

and I ran through every database to make sure there were no logic bombs/owner/SP issues (that I know how to search for, they're leaving on good terms to not too worried about an LB...)

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%USERNAME%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1
GROUP BY OBJECT_NAME(id)



DECLARE @Search varchar(255)
SET @Search='USERNAME'

SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1


SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like '%USERNAME%'


SELECT name
FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%USERNAME%'


But is there anything else I should be looking for in terms of anything that might break when that employee walks out the door and we turn off their access?

Thanks

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 03/24/2014 :  19:19:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
1. Make sure there are no linked servers that use their credentials.
2. Look at all your SQL Server services and make sure they're not running under that person's credentials.
3. If you have any jobs that run batch files that connect to SQL Server, check those credentials too.
4. If you are using SQL authentication, and it's feasible, change all passwords to those accounts. Definitely change the sa password, even if you've disabled it.
5. Also check your database ownership and change the owner to sa if it's not already.

For ongoing operations, you should not grant anyone access via their Windows login, rather use Active Directory groups and grant permissions to those groups. Then you can simply add or remove people from the group as their access requires it.
Go to Top of Page

ITTrucker
Yak Posting Veteran

USA
52 Posts

Posted - 03/25/2014 :  09:15:07  Show Profile  Reply with Quote
No linked servers so that's good, I wrote the only scheduled task/batch files so that's ok, and we use a mix of sql and windows authentication but I'll be shutting those down when they leave on Friday.

Unfortunately the sa account is enabled (Dynamics GP modules check for sa user specifically so we have to have it) and the password will be difficult to change because of it's rampant hard coding into all kinds of applications, datasources, and other random settings (before I started).

Guess I'll be spending the next 2 weeks running traces on the database to track all those down which I should have done the last time the sa password was given out but it's been slow going.

Thanks, and I'll look into the AD groups instead of direct access.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000