none
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.

    Saturday, March 03, 2012 8:56 PM

Answers

  • 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

    Saturday, March 03, 2012 9:25 PM

All replies

  • 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

    Saturday, March 03, 2012 9:25 PM
  • Thanks UWE, iTS WORKED
    Monday, March 05, 2012 12:52 PM