Leaving a SQL Server DBA Job Gracefully

By: Alan Cranfield   |   Read Comments (5)   |   Related Tips: More > Professional Development Career

Problem

You’re leaving your current employer for a new opportunity and want to make the move as smooth as possible. You don’t want to burn your bridges and want to make sure that everything you’ve left behind can be managed by the people you’ve left behind or the DBA who is replacing you.  So, you’ve done your handover documentation and trained up the new guy. You definitely don’t want to be that DBA who is cursed the week after they leave when their network account is disabled and a bunch of stuff ‘breaks’.

Solution

What follows is a list of things to check and how to check them when a DBA is leaving. Ideally, you want the DBA who is leaving to go through these steps themselves and make the changes. It shouldn’t be up to NewDBA to do this.

SQL Server 2005 severed the implicit connection between database users and schemas.  Ownership of schemas can now be transferred without changing their names. This has simplified the work of us DBAs and now allows us to transfer the ownership of schemas without changing their names. For the sake of this tip I will only address operational issues that may arise when a DBAs account is disabled or deleted in Active Directory or a login is dropped from the SQL Server.

Steps

DBAs are always in the sysadmin server role and usually in the db_owner role in all databases. This means that all objects created by DBAs are usually in the dbo schema which means we usually don’t have to re-assign any schema ownership when a DBA leaves.

There are, however, some objects that retain the DBAs windows account as owner and these need to be addressed.

SQL Jobs

By default when a SQL Server Agent Job is created it records the owner of the job as the login creating the job. I usually change this to ‘sa’ or the SQLServerAgent service account where applicable. If a job owner is a windows login then SQL Server checks the windows login in AD when the job runs and if it can’t find the login in AD then it will fail as this error below shows:

Also, if the DBA has moved departments and his account still exists but his access from your servers has been removed then you may get this error:

To resolve this find all the jobs on the server owned by exDBA using this simple query:

Then update them either through SSMS Job Properties:

Or you can use this update query:

Database Principals

Database Roles are the only database principals that don’t have to be owned by dbo so let’s check all databases for any roles owned by exDBA:

These can be easily fixed by transferring ownership of the database role.

EndPoints and other Entities

You may experience errors like this when dropping a login or user.

To transfer ownership of these entities you can use the ALTER AUTHORIZATION statement. The example below transfers ownership of the Database Mirroring endpoint to ‘sa’:

It is also possible that the exDBA could be an owner of any of these database entities.  Again, use the ALTER AUTHORIZATION statement to transfer ownership if you have:

• Type
• XML Schema Collection
• Fulltext Catalog
• Schema
• Assembly
• Role
• Message Type
• Contract
• Service
• Remote Service Binding
• Route
• Symmetric Key
• Certificate

Changing database owners

I have never found the owner of a database to ever be an issue. I often see database owner logins which were employees that have long since left the company. If you want to change ownership of the database then you can do so as follows. I like to have the database owned by ‘sa’. Preferences may vary.  The new owner either has to be a valid login or a valid Windows account:

DTS Packages

Many of us still run legacy DTS packages. There is no problem with deleting an account that owns DTS packages. For example if you delete the domain\exDBA Windows account it will not cause any problems with DTS packages owned by this account.

If you do want to re-assign package ownership for other reasons then you can use the following undocumented stored procedure:

You can also update the owner and owner_sid columns directly in the msdb..sysdtspackages table but take precautions and backups as this is a system table.

SSRS Report schedules

We recently experienced scheduled SSRS report failures when an account was deleted from Active Directory and that account owned schedules in SSRS.

To prevent this from happening I suggest checking for any subscriptions owned by the exDBA and changing ownership before exDBA is removed from AD. This can’t be done from Report Manager but can be done directly against the ReportServer database as follows:

--NB: backup the ReportServer DB 
USE ReportServer
GO 

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier 

SELECT @OldUserID = UserID 
FROM dbo.Users 
WHERE UserName = 'domain\exDBA' 

SELECT @NewUserID = UserID 
FROM dbo.Users
WHERE UserName = 'domain\SQLService' 

UPDATE dbo.Subscriptions 
SET OwnerID = @NewUserID 
WHERE OwnerID = @OldUserID
GO

Also, run the following query against the ReportServer database periodically to check for any report delivery failures which may be related to removing exDBA from AD: 

You may get some errors like this which you will need to investigate: 

Orphaned users

SQL Server will allow you to drop a login without actually removing all associated database users. This has the undesirable side effect of creating orphaned users. You do, however, get the following warning when dropping a login through SSMS as a reminder to also delete the database users:

What I usually do is drop the mapped users from each database BEFORE I drop the login so that I have no orphaned users:

Next Steps

• Check for any Windows Services running under exDBA’s account.
• Check DatabaseMail profiles and update to new email addresses.
• Remove or edit any Operators that have been defined using exDBA’s email address.
• Monitor the SQL Error logs for any failed logins from exDBA

About the author

Alan is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

View all my tips

We Recommend

• More Career Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code