none
SQL 2008 - Can not execute as the database principle

    Question

  • When I try to drop publication created in 2008 I get a message "Can not execute as the database principle becasue the principle dbo does not exist, this type of principle can not be impersonated".

    The dbo does exist in the publisher db and I am sysadmin on the box. What could be the problem?

    This is what I am trying

    exec

     

    sp_droppublication @publication = N'HLA_ST'


    Msg 15517, Level 16, State 1, Procedure sp_replcmds, Line 1

    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.

    Wednesday, May 13, 2009 3:19 PM

All replies

  • Not sure why I was getting that error. I was able to remove the replication completely including distributor and recreate it. It works for now.
    Wednesday, May 13, 2009 3:50 PM
  • I have same problem, MSSQL server 2008, there was snapshot replication set and about few days ago SQL server on which I pushed replication was removed.

    since MSSQL server isn't up & running I'm unable to remove replication for that server.

    here is error if I try to remove it from server management Studio
    ********************************************************************************************
    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Could not delete publication 'min_centrix'.

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    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.
    Changed database context to 'Min_centrix'. (Microsoft SQL Server, Error: 15517)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=15517&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
    ********************************************************************************************


    same is if I try to delete it with T-SQL

    USE min_centrix
    exec sp_droppublication @publication = N'min_centrix'

    error:
    Msg 15517, Level 16, State 1, Procedure sp_replcmds, Line 1
    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.

    Does anyone have any suggestions?

    ********************************************************************************************
    ********************************************************************************************

    here is how i solved it:

    use min_centrix
    exec sp_removedbreplication N'min_centrix'



    target
    • Proposed as answer by target222 Tuesday, August 04, 2009 10:51 AM
    Tuesday, August 04, 2009 10:42 AM
  • I have same problem, MSSQL server 2008, there was snapshot replication set and about few days ago SQL server on which I pushed replication was removed.

    since MSSQL server isn't up & running I'm unable to remove replication for that server.

    here is error if I try to remove it from server management Studio
    ********************************************************************************************
    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Could not delete publication 'min_centrix'.

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    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.
    Changed database context to 'Min_centrix'. (Microsoft SQL Server, Error: 15517)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=15517&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
    ********************************************************************************************


    same is if I try to delete it with T-SQL

    USE min_centrix
    exec sp_droppublication @publication = N'min_centrix'

    error:
    Msg 15517, Level 16, State 1, Procedure sp_replcmds, Line 1
    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.

    Does anyone have any suggestions?

    ********************************************************************************************
    ********************************************************************************************

    here is how i solved it:

    use min_centrix
    exec sp_removedbreplication N'min_centrix'



    target
    PERFECT answer--short and simple and I was stuck googling for my life!  Thank you target!
    Thursday, May 20, 2010 4:10 PM
  • Had the same problem, but the solution required an additional step:

     

    1) Database had been "copied" from another server using a backup/restore

    2) Database owner had not been "re-defined" (http://support.microsoft.com/kb/913423)

    3) Then I used the statement above to clear the old replication

    Monday, August 16, 2010 2:46 PM
  • WHat you means on step 3? i´m with the same problem here, but, its not dbo, it is auditor_ddl, the error msg is the same....

     

    i tryed a lot of things, this user is already sysadmin, dbowner, ddladmin, but nothing works!!!!...

     

    Can you help me?

    Tuesday, December 14, 2010 6:31 PM
  • use [databasename] EXEC sp_changedbowner 'sa'
    • Proposed as answer by Butschkow Wednesday, June 01, 2011 7:24 AM
    Wednesday, February 23, 2011 2:42 PM
  • Same issue.. "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."

    I had attached a database from a different server.

    It was missing a database owner. THe login that i was using for access as dbo role was the one i needed as the database owner so..

    1. Remove user access from Login to the database
    2. Go to the database, select properties (files tab)
    3. in the owner box - select the login i had removed above - this adds the login as DBO not just a user with dbowner roles permissiosn.
    4. No more errror.

    (if the login has a user account in the database it cant be used which is why i had to remove the database access from that login to begin with)

    Tuesday, November 27, 2012 2:23 PM
  • Please check Database owner through Database properties, because I had the same issue in SQL 2012 and on checking the database owner field it was empty, so I chose sa as database owner and then I was able to drop the publication successfully. PLEASE TRY this in LAB\TEST environment before implementing the change in PROD.
    • Proposed as answer by Ricky.khosla Monday, June 09, 2014 7:28 PM
    Friday, April 26, 2013 4:36 AM