locked
The database principal owns a schema in the database RRS feed

  • Question

  • Hi Team,

    I need to delete few orphan users from the database. While we are trying to delete I got an error

    Error: 15138 the database principal owns a schema in the database, and cannot be dropped.

    Hence I have transferred the ownership to dbo by using below query.

    ALTER AUTHORIZATION ON SCHEMA :: SchemaName TO dbo 
    GO

    After that I have deleted the users.

    Could you please let me know if there is any impact of changing schema ownership to dbo.

    As per my understanding as they are orphan users and also when we don’t need them, we can change it. Kindly suggest me if it is not correct. 

    Thursday, December 14, 2017 2:14 AM

Answers

All replies

  • It is OK moved the schema to DBO for example for orphan users

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by mito access Thursday, December 14, 2017 6:33 AM
    Thursday, December 14, 2017 5:50 AM
    Answerer
  • No, you cannot just move it to dbo. All depending on how the schema is used that could create a security hole.

    You need to investigate what the schema is used for. If there are no objects in it - just drop it.

    Also, when it comes to users in the database that do not map to a server login, they are of three different kinds:

    1) Users who authenticate directly to the database (can only appear in a contained database).
    2) Users explicitly created WITHOUT LOGIN.
    3) Users that once mapped to a login, but no longer does, because the login was dropped or the database was restored to a different server.

    It is only the last group that qualify as "orphaned" and which you may want to drop for that resaon.

    Thursday, December 14, 2017 8:22 AM