Sitecore : The database principal owns a schema in the database, and cannot be dropped message

-

While upgradation to Sitecore 10.2, I was to restore the database on the Managed Cloud Service Azure SQL Server.

I have connected the Azure SQL server on my local SSMS and started to import the Database using the option Import Data-tier Application.

While importing the database using the above option you need a BACPAC file instead of a .bak file.

So I was to create a BACPAC file of the database from my local. Go to the database, right-click and select the Export Data-tier Application option under the Task menu.

database-principal-sitecore-1

After clicking on the next, while creating the BACPAC file you may get the below error:

database-principal-sitecore-2

TITLE: Microsoft SQL Server Management Studio
------------------------------
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: Error validating element [mh-dbuserstg]: The element [mh-dbuserstg] has been orphaned from its login and cannot be deployed.
Error SQL71564: Error validating element [newrelic]: The element [newrelic] has been orphaned from its login and cannot be deployed.
Error SQL71627: The element User: [MNC-GEN3\_DBComm] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [IIS APPPOOL\uat.millenniumhotels.com] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [MNC-GEN3\Shannon.li] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [MNC-GEN3\Xiaobin.xiao] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [MNC-GEN3\Smart.huang] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [MNC-GEN3\Sam.lee] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
(Microsoft.SqlServer.Dac)
------------------------------
BUTTONS:
OK
view raw database-principal-sitecore-1 hosted with ❤ by GitHub

Solution: Go to the Users in that Database. Your Database => Security => Users

Find the user, right click and delete it.

If you are unable to delete the user and encountered a new error as below:

database-principal-sitecore-3

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for User 'IIS APPPOOL\username.com'. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.44091.28+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=15138&LinkId=20476
------------------------------
BUTTONS:
OK
view raw database-principal-sitecore-2 hosted with ❤ by GitHub

It means the user you are going to delete owns a schema in the database and owners.

First, you need to search which schemas own by the user. Go to your database -> Security -> Users

Check the properties of the user by right click on the user and going to the owned schema.

database-principal-sitecore-4

Now change the owner of the schema. Go to your database -> Security -> Schemas

Right-click select properties and search the schema owner. If you don’t know then change the schema owner to DBO.

database-principal-sitecore-5

After that delete, the user and start again to export the database as BACPAC

Happy Sitecoreing 😊

Comments

Post a Comment

Popular posts from this blog

Sitecore Installation Error: Failed to Start Service 'Sitecore Marketing Automation Engine'

-
Image
 Error: [------- XConnectXP0_StartServices [2] : ManageService -----------------------] [XConnectXP0_StartServices [2]]:[Updating] sc903xconnect.dev.local-MarketingAutomationService Waiting for service 'Sitecore Marketing Automation Engine - sc903xconnect.dev.local-MarketingAutomationService (sc903xconnect.dev.local-MarketingAutomationService)' to start... Waiting for service 'Sitecore Marketing Automation Engine - sc903xconnect.dev.local-MarketingAutomationService (sc903xconnect.dev.local-MarketingAutomationService)' to start... Waiting for service 'Sitecore Marketing Automation Engine - sc903xconnect.dev.local-MarketingAutomationService (sc903xconnect.dev.local-MarketingAutomationService)' to start... Waiting for service 'Sitecore Marketing Automation Engine - sc903xconnect.dev.local-MarketingAutomationService (sc903xconnect.dev.local-MarketingAutomationService)' to start... Waiting for service 'Sitecore Marketing ...
Read more

Import CSV Data in Sitecore Using PowerShell: Part-3

-
Image
In my previous blog , we had discussed Sitecore PowerShell Command and Scripts. In this blog, I am discussing with you about How to Import CSV Data in Sitecore using PowerShell Scripts? To do the same you need to read the CSV file rows and create Sitecore Items into the Content Tree for each row then assign the column values of the row into the respective Item Field. There are two options: Write your own customized PowerShell Script and Import CSV Data. Use the inbuilt Data Importer tool that comes with Sitecore PowerShell Extension .  Option 1: Perform the Below steps to Import the CSV Data into Sitecore: First, you will need a CSV file. If you have Excel Sheet, then go to the File Menu and click on Save As option and change the format and save it into CSV format. Suppose you have a CSV file with the number of rows and each row has several columns with predefined values. Here I am taking the below CSV file for an...
Read more

Error: Method Through Reflection is Not Allowed after Applying Sitecore Hotfix

-
Image
Recently, Sitecore security bulletin SC2023-003-587441 addresses Critical (582720) and High (584731) severity vulnerabilities in Sitecore software and provides the cumulative hotfix for the same. I have applied this hotfix in my solution and deployed it, but I have encountered the following error while publishing the Sitecore Items. If you are looking for how to install the Sitecore Cumulative hotfix OnPrem and PaaS instances, see the blog post: Sitecore Cumulative Hotfixes Installation on OnPrem and PaaS Solution: This Security Hotfix might add a security layer that prevents the execution of unexpected methods through reflection. As I have customized the publishing method for Sitecore items, I am getting this issue while publishing the Sitecore items. Go to the path " \App_Config\Sitecore\CMS.Core\ " and open the " Sitecore.Reflection.Filtering.config " file. Add the method name to the " Sitecore.Reflect...
Read more