January, 2014

...now browsing by month

 

Overcame the error: The database principal owns a schema in the database..

Friday, January 17th, 2014

Today I tried to remove a user from SQL server database and encountered this error:

The database principal owns a schema in the database, and cannot be dropped (Microsoft SQL Server, Error: 15138)

Followed this link from TechNet at http://blogs.technet.com/b/mdegre/archive/2010/12/19/the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped.aspx

Here were the steps I took to drop the user “syang”:

  1. Find the schema name by running this query:
    select * from sys.schemas where principal_id=USER_ID('syang')

    which returned:

     db_owner 16384 6 (name, schema_id, and principal_id)
  2. Run this query to alter the schema name:
    alter authorization on schema::db_owner to dbo
    go
  3. Now, I was able to drop the user syang successfully by running this query:
    drop user syang
  4. To remove user login syang completely from the SQL server, run this:
    IF  EXISTS (SELECT * FROM sys.server_principals WHERE name ='syang')
    DROP LOGIN syang 
    GO