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’.
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.
There are, however, some objects that retain the DBAs windows account as owner and these need to be addressed.
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 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)
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:
XML Schema Collection
Remote Service Binding
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
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:
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
Check for any Windows Services running under exDBA’s account.
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.
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.
Sunday, December 19, 2010 - 10:18:43 AM - Jack Whittaker