'Cannot execute as the database principal....'

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

At times the following message could show up in the Messages window or during use of the DB Load utility:

U0003592 Status: '42000' Native error: '15517' Msg: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Environment

Release: All

Resolution

Investigation

The user specified (in this case "dbo") is not in the connect string, but is actually the database owner.  The customer does not expect any user other than the logged-in database user to be accessed.
This usually happens on a reproduced or mirrored database.  What happens is that the DB Load runs the chngdb.sql file.  The end of this creates a stored procedure that uses "EXECUTE AS SELF" statement:
 
 CREATE  PROCEDURE UC_Truncate_Table
 
 CREATE  PROCEDURE UC_Update_Stat_Table
 
 More information can be found here: http://support.microsoft.com/kb/913423

A look at the stored procedure within your database will show that Automic is pulling the DB owner rather than the logged-in user to run this procedure.

Solution

This is a native SQL Server message being passed to Operations Manager.  Here are two explanations for why this error occurred:
 1) The database owner is a Windows administrator, but not the administrator in Windows 2008 and he has not been granted full rights to the database.
 2) If this database was restored from another server, the login that is the database owner (and therefore the login that becomes dbo) does not exist on the new server.  If the owner is a login (other that 'sa') with the same name on both hosts, the SID is still different and does not actually match up.
 
 You will need the assistance of your DBA to resolve this problem.  The DBA can use the following commands to help determine the user/owner for the scenario

 select suser_sname (owner_sid)
 from master.sys.databases
 where database_id db_id = ()
 
 Use this command to register the correct administrator:
 sp_changedbowner @ loginame = 'sa'
 

The DBA can then run an ALTER AUTHORIZATION statement