Database Cleanup when Admin is Leaving

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Database Cleanup when Admin is Leaving

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-03-24 : 17:08:45

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

15732 Posts

Posted - 2014-03-24 : 19:19:18

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.

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-03-25 : 09:15:07

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources