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]
   ; 

SSMS – Error Message – “Property Owner is not available for Database”

Background

Trying to access the database property on one of our databases.

Error

Error Message

Syntax

Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Sample

Property Owner is not available for Database ‘[AdminDB]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

 

Troubleshooting

Metadata

sp_helpdb

Syntax

use master
go

exec sp_helpdb
go

Output

Explanation

  1. Database
    • AdminDB
      • ~~UNKNOWN~~

sys.databases

Syntax

select 
		  tblSD.[database_id]
		, tblSD.[name]
		, tblSD.[owner_sid]
		, [owner]
			= SUSER_SNAME(tblSD.owner_sid)

from [master].[sys].[databases] tblSD
go

Output

Explanation

  1. Database
    • AdminDB
      • SUSER_SNAME(sys.databases.owner_sid) returns null

 

Remediation

Alter Authorization

Syntax


use [master]
go

ALTER AUTHORIZATION
DATABASE::[database]
TO [sa]
;

Sample


use master
go

BEGIN TRAN

	exec sp_helpdb

	ALTER AUTHORIZATION 
		ON DATABASE::[AdminDB] 
		TO [sa]
		;    

	exec sp_helpdb

ROLLBACK

Output

 

Reproducible

SSMS – v2017

The error does not occur in SSMS v2017.

SSMS – v2014

Noticed it in v2014 when trying to access the database property for an orphaned database.

 

References

  1. Microsoft
    • Docs > SQL > T-SQL > Statements
      • ALTER AUTHORIZATION (Transact-SQL)
        Link
    • Docs > SQL > T-SQL > Functions