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

  • Question

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

    I have tried most options with google, but unable to drop the user, checked SUSER, SID nothing found. Please advise.

    
    • Edited by Shanky_621MVP Thursday, February 11, 2016 10:24 AM formatted the question
    Thursday, February 11, 2016 10:21 AM

Answers

  • Hi Satishs1206,

    Please execute the following query provided by Erland in this similar thread.

    SELECT ' IF EXISTS (SELECT * FROM sys.' + quotename(o.name) +
    
            ' WHERE principal_id = user_id(''youruser'')) PRINT ''sys.' + o.name + ''''
    
     FROM   sys.all_objects o
    
     JOIN   sys.all_columns c ON o.object_id = c.object_id
    
     WHERE  c.name = 'principal_id'
    
       AND  o.schema_id = 4
    
       AND  o.type = 'V'
    
       AND  o.name NOT LIKE 'pdw%'
    


    Run result set from the above, and investigate the catalog view it prints.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Friday, February 12, 2016 6:39 AM

All replies

  • Hello - The message indicates that the user (Database principal)you are trying to drop owns the schema therefore you first have to transfer this schema ownership to dbo and then execute drop action.

    This might help you:

    SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('UserName')
    ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 
    GO
    DROP USER UserName

    Note: In the above code you will have to replace UserName with actual User name as per your environment


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, February 11, 2016 10:29 AM
  • Your erreor is throw when you try to delete a user?

    If right,

    check at this post  : https://blogs.technet.microsoft.com/mdegre/2010/12/19/the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Thursday, February 11, 2016 10:30 AM
  • This won't work for me, simply am running below code to drop user, DROP USER username and that giving me still that error.

    Am not getting any result while running below code.

    SELECT name FROM  sys.schemas WHERE principal_id = USER_ID(‘myUser’)

    Thursday, February 11, 2016 10:41 AM
  • This won't work for me, simply am running below code to drop user, DROP USER username and that giving me still that error.

    Am not getting any result while running below code.

    SELECT name FROM  sys.schemas WHERE principal_id = USER_ID(‘myUser’)

    Thursday, February 11, 2016 10:41 AM
  • Ok,

    By GUI in SSMS: Right click on the user name under Database -> Security -> Users -> Owned schemas

    Do you have something checked?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it


    • Edited by remi.smeeckaert Thursday, February 11, 2016 11:01 AM orthograph
    Thursday, February 11, 2016 11:01 AM
  • Look at this post ,

    Maybe your problem is due to that

    https://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Thursday, February 11, 2016 11:05 AM
  • I checked but nothing found. appropriate if you can tell me, any other solution. 
    Thursday, February 11, 2016 12:01 PM
  • No, this won't help too. I tried all. 
    Thursday, February 11, 2016 12:03 PM
  • Hi Satishs1206,

    Please execute the following query provided by Erland in this similar thread.

    SELECT ' IF EXISTS (SELECT * FROM sys.' + quotename(o.name) +
    
            ' WHERE principal_id = user_id(''youruser'')) PRINT ''sys.' + o.name + ''''
    
     FROM   sys.all_objects o
    
     JOIN   sys.all_columns c ON o.object_id = c.object_id
    
     WHERE  c.name = 'principal_id'
    
       AND  o.schema_id = 4
    
       AND  o.type = 'V'
    
       AND  o.name NOT LIKE 'pdw%'
    


    Run result set from the above, and investigate the catalog view it prints.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Friday, February 12, 2016 6:39 AM
  • Tanks you so much !,

    Best Regards

    Tuesday, November 20, 2018 10:58 AM