Transact SQL :- Error – Msg 15138 – “The database principal owns a schema in the database, and cannot be dropped”

Background

Cleaning up a database as we move it from Development to Production.

Recreate

Drop User

SQL


if user_id('LAB\daniel') is not null
begin

	exec sp_droprolemember
			  @rolename = 'db_owner'
			, @membername = 'LAB\daniel'

	drop user [LAB\daniel];

end
go

Error

Msg 15138

Error Text


Msg 15138, Level 16, State 1, Line 115

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

Error Image

TheDatabasePrincipalOwnsASchemaIntheDatabase.20190204.1119AM.PNG

 

Troubleshoot

Metadata

sys.schemas

SQL


select
          [schema]
            =tblSS.[name]

        , tblSS.schema_id

        , tblSS.principal_id

        , [principal]
            = user_name(tblSS.principal_id)

		, [principalIsFixedRole]
			= tblSDP.is_fixed_role

		, [principalType]
			= tblSDP.[type_desc]

		--, tblSDP.*

from   sys.schemas tblSS

inner join sys.database_principals tblSDP

		on tblSS.principal_id = tblSDP.principal_id 

order by
    user_name(tblSS.principal_id) asc

Output

sys.schemas.20190204.1134AM.PNG

Explanation

  1. Schema
    • db_datareader
      • The owner for the db_datareader schema has been assigned to a database account, other than itself

Remediate

Change Schema Owner

ALTER AUTHORIZATION

SQL


ALTER AUTHORIZATION ON SCHEMA::db_datareader
   TO [db_datareader]
   ; 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s