Cannot execute as the database principal

Document ID : KB000090415
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Cannot execute as the database principal
Resolution:
Detailed Description and Symptoms

Error message:

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


Investigation

The user specified (in this case "automicloader") 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 scenario
1):
 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 following command can be used to change the user/owner for scenario
2):
 alter authorization on database to ...

For continued troubleshooting:

If the above steps were tried and the error still persists, please contact Support and send the following:
*) Screenshots of your SQL Server users and Automic DB owner
*) Set a TCP/IP=2 and DB=3 trace on the WPs
*) Reproduce the issue and send the WP log and trc files from the timeframe