Dropping a Principal Who Owns a schema

16. July 2024 Mehedi SQL Server , SQL Tips Comments (0)

That day one of my junior colleague rushed to me and sought my assistance. He failed to drop an user. It showed - "The database principal owns a schema in the database, and cannot be dropped." I took him to our office kitchen, had tea, and explained the issue. Before drive deep to the issue, let's check some key concepts here.

Database Principal

A Principal is an SQL Server entity (individuals, groups, and processes) which can request for resources. For example: Windows Login, Windows Group, Database User etc. Principals could be:

  1. SQL Server-level principals - For example:
    • SQL Server authentication Login
    • Windows authentication login for a Windows user
    • Windows authentication login for a Windows group
    • Microsoft Entra authentication login for a Microsoft Entra user
    • Microsoft Entra authentication login for a Microsoft Entra group
    • Server Role 
  2. Database-level principals - For example: 
    • Database User
    • Database Role
    • Application Role

Schema

SQL Schema is a logical collection of database objects like tables, views, stored procedures, functions, indexes, triggers etc. It helps to segregate database objects for different applications, access rights, managing the security administration of databases. There is no restrictions on the number of objects in a schema.

Schema Owner

A user who owns the schema is known as schema owner. From SQL Server 2005, user and schema have different meanings. Now, the database object owner is a schema. There could be single or multiple schema owners.

Error Resolution

For error resolution, you need to 1. find the principal's schemas, 2. transfer the ownership finally 3. drop the principal.

List of Schemas and Owners

Below query will show you list of schema owners and corresponding schema.

SELECT db.name AS [DB User], s.name AS [Schema]
FROM sys.database_principals db
JOIN sys.schemas s ON s.principal_id = db.principal_id

Diagram: User wise Schema

Transferring Ownership

Once you find out schema names, you can use below scripts to transfer the ownership of schema to DBO.

ALTER AUTHORIZATION
ON SCHEMA::<Schema Name>
TO DBO
GO

Dropping Principal

Now, you can drop the principal.

drop user
UserNeedToDelete
go

Conclusion

Hopefully, you got the idea to resolve the "The database principal owns a schema in the database, and cannot be dropped." error.



Tags:

Add comment