post icon

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

If you drop a user that owns a schema, you’ll receive the following error:

Drop failed for User ‘my_user’.  (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

so, to drop user, you have to find which schemas assigned to, and then transfer the ownership to another user (or role).

To find assegned schema, enjoy this snippet:

SELECT sc.name
FROM sys.schemas sc
WHERE sc.principal_id = USER_ID('my_user')

Once found schema (for example db_datareader), use it to transfer ownership with this snippet:

ALTER AUTHORIZATION
ON SCHEMA::db_datareader
TO dbo
September 1st, 2011 @ 02:52

4 Comments

Leave a comment
  1. Francesco
    April 6th, 2012 at 04:57 #

    Mi hai salvato un venerdi sera!!

  2. Nicola Celiento
    April 6th, 2012 at 05:14 #

    Un santo venerdì! 🙂

  3. TomClancy
    March 12th, 2013 at 07:36 #

    Grazie 🙂 davvero utile

  4. goblin
    November 15th, 2014 at 09:32 #

    Grazie mille funziona 😉

Leave a Reply

Are you human? Time limit is exhausted. Please reload CAPTCHA.