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 tried dropping a publication in a transactional replication setup and received below error,

TITLE: Microsoft SQL Server Management Studio

Could not delete publication ‘JBDBREP’.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18338.0&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 ‘JBDBREPL’. (Microsoft SQL Server, Error: 15517)

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

BUTTONS:
OK

-> The error message points to some issue related to owner of the replicated database.

-> Right click the publisher database and click on properties. Click on “Files” tab and check for Owner.

-> In my case I see it blank and this seems to be the problem.

-> I executed below query to change the owner to SA. Instead of SA an appropriate login will also help.

USE [JBDBREPL]
GO
ALTER AUTHORIZATION ON DATABASE::[JBDBREPL] TO [sa]
GO

-> I tried removing the publication and it worked fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s