Have you ever encountered the error message “The database principal owns a schema in the database, and cannot be dropped” while trying to remove a login from a SQL Server database? If so, you’re not alone. This error can be frustrating and may prevent you from completing your task. But don’t worry, there is a quick workaround to resolve this issue.
The reason for this error is quite clear from the error message itself. There are schemas associated with the user you’re trying to delete, and these schemas need to be transferred to another user before the deletion can be performed.
Let’s assume you’re trying to delete a user named ‘pinaladmin’ from the ‘AdventureWorks’ database. To resolve this error, follow these steps:
- First, run the following script within the context of the database where the user belongs:
USE AdventureWorks;
SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('pinaladmin');
This script will return the names of the schemas associated with the user. In our case, let’s say we get two schema names as a result.
- Next, run the following script to transfer the ownership of the schemas to the ‘dbo’ user. In our case, we will execute it twice since we have two schemas:
ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;
After executing these scripts, you should be able to drop the database owner without encountering any errors.
Here is a generic script that you can use to resolve the error:
SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('YourUserID');
Replace ‘YourUserID’ with the actual user ID, and then use the resulting schema name in the following script:
ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;
By following these steps, you can successfully resolve the error 15138 and remove the login from the SQL Server database.
Remember, it’s important to transfer the ownership of the schemas to another user before deleting the login to avoid any potential issues with the database.
Hope this helps! If you have any further questions or need assistance, feel free to reach out.