Leaving a SQL Server DBA Job Gracefully


By:   |   Updated: 2010-03-11   |   Comments (5)   |   Related: More > Professional Development Career Transition

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:

The job failed. Unable to determine if the owner (domain\exDBA) of job _DBA - Manage Partitions has server access (reason: Could not obtain information about Windows NT domain\user 'domain\exDBA '. [SQLSTATE 42000]

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:

The job failed. The owner (domain\exDBA) of job _DBA - Manage Partitions does not have server access.

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

select name
from msdb..sysjobs
where owner_sid = (select sid
from sys.syslogins
where name = 'domain\exDBA')
GO

Then update them either through SSMS Job Properties:

Or you can use this update query:

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name='_DBA - Manage Partitions', @owner_login_name='sa'
GO

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:

EXEC sp_msforeachdb 'use ? select db_name(), name, type_desc
from sys.database_principals
where type_desc = ''DATABASE_ROLE''
and owning_principal_id = (select principal_id
from sys.database_principals
where name = ''domain\exDBA '')'

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

USE [myDB]
GO
ALTER AUTHORIZATION ON ROLE::[myRole] TO [domain\sqlservice]
GO

EndPoints and other Entities

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

Drop failed for User 'domain\exDBA'. (Microsoft.SqlServer.Smo)
The database principal owns an endpoint and cannot be dropped. (Microsoft SQL Server, Error: 15141)

Drop failed for User 'domain\exDBA'. (Microsoft.SqlServer.Smo)
The database principal owns data types in the database and cannot be dropped. (Microsoft SQL Server, Error: 15184)

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':

USE master
GO
ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO [sa]
GO

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:

-- the old fashioned way
USE myDB
GO
EXEC sp_changedbowner 'sa'
GO

-- the new way
USE master
GO
ALTER AUTHORIZATION ON Database::myDB TO [sa]
GO

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:

EXEC msdb..sp_reassign_dtspackageowner
@name = 'London Equities Paris2 Extract_NEW_RERUN',
@id = '80A50206-A739-4CE9-906F-833A1094BBF6', -- get this from the msdb..sysdtspackages table
@newloginname = 'domain\SQLService'
GO

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.

Failure sending mail: The user or group name 'domain\exDBA' is not recognized.

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: 

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description
FROM ReportServer.dbo.Subscriptions AS S
LEFT OUTER JOIN ReportServer.dbo.[Catalog] AS C
ON C.ItemID = S.Report_OID
WHERE S.LastStatus LIKE 'Failure sending mail: The user or group name %% is not recognized.'
GO

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:

USE master
GO
-- drop all users first
EXEC sp_msforeachdb 'USE ? SELECT db_name() DROP USER [domain\exDBA]'
GO
-- then drop login
DROP LOGIN [domian\exDBA]
GO
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


Last Updated: 2010-03-11


get scripts

next tip button



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

View all my tips
Related Resources





Comments For This Article




Sunday, December 19, 2010 - 10:18:43 AM - Jack Whittaker Back To Top (10465)

Useful and timely advice, Alan - I'm leaving my current job in a couple of months time and it wwould be nice to leave things tidy - thank you


Sunday, March 21, 2010 - 5:21:57 AM - --cranfield Back To Top (5089)

Well, I've never had to fire anyone for becoming a security threat - thats a tough one. Password management and control is a core skill and responsibility of any SE or DBA. Changing system and application passwords whenever a DBA is replaced is not an option in most shops. Password change, storage and retrieval must be a procedure that more than one member of the team is familiar with. This is more important in mixed shops where Windows is not the only operating system and SQL not the only DBMS.


Friday, March 19, 2010 - 8:41:27 AM - bobw111 Back To Top (5084)

I am beginning to suspect that the end result of burying passwords all over the place and making it next to impossible to change them is less secure data, not more.

For instance, what do you do when you have had to fire someone that knew all those passwords because that person became a real security threat to the organization?

 You can't just change the locks on the door or the combination on the safe and hope that is the end of it.


Thursday, March 18, 2010 - 8:47:24 PM - --cranfield Back To Top (5081)

unless those passwords are used in applications, connection strings, services etc.  The last thing you want is things breaking after the DBA leaves. Passwords need to be securely logged and stored somewhere where the new DBA has access and the old DBA does not.


Thursday, March 18, 2010 - 11:29:14 AM - bobw111 Back To Top (5074)

I suggest you give your replacement explicit instructions to change ANY passwords you knew the minute you walk out the door.

I made this a policy many years ago and made sure any one that worked for me understood that this had to happen when either myself or they left the organization.

In at least two instances this put a quick end to someone "assuming" that a problem was sabotage caused by someone that had just left the company.

I emphasize it because I've come across far to many instances where it was a "policy", but was never enforced.



download


Recommended Reading

Preparing Yourself to Move into a Management Role

Job Search Plan Task List

SQL Server for Network Administrators





get free sql tips
agree to terms


Learn more about SQL Server tools