The database principal owns a schema in the database, and cannot be dropped.

Question

Hi Friends,

I am unable to drop the user and i am getting below error mesage, i just restored the DB from different environment/

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Answer 1

Hallo Maddy,

you'll find the owners of schemata with the following query:

SELECT s.name,
       p.name
FROM   sys.schemas s INNER JOIN sys.database_principals p
       ON	(s.principal_id = p.principal_id)

Before you can drop a database user you have to drop the owned schema or change the owner of the schema

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO NewOwner

Get more details for authorization here:
http://msdn.microsoft.com/en-us/library/ms187359.aspx

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 2

Thanks UWE, iTS WORKED