none
Error: Cannot drop schema because it is being referenced by object RRS feed

  • Question

  • My database has a schema with the same name as a user (let's call it 'username'). This schema was not explicitly created. I want to drop the User 'username' but I have to drop the schema 'username' first; However, when I tried to do that I got the message...

    "Cannot drop schema 'username' because it is being referenced by object 'vw_Inventory'

    There is a view named 'vw_Inventory' but I don't see where it's referencing this schema, or any schema for that matter. Why would I get this error and how do I resolve it so I can drop the Schema and User?


    Darrell H Burns

    Sunday, December 29, 2013 2:34 AM

Answers

  • If a Schema has some objects in it, then you cannot drop that Schema until you remove all the objects out of it. You can change the schema owner to another database principal and drop the user like,

    Alter Authorization ON Schema::username TO DBO;

    Drop User username;

    other option would be Transfer the username schema objects to a different schema and drop username schema and user like.

    Alter Schema DBO Transfer Username.[OBEJECTNAME] ;
    --need to repeat this until you transfer all objects under this schema
    Drop Schema username;
    Drop User username;
    



    Regards,
    Praveen D'sa
    MCITP - Database Administrator - 2008
    sqlerrors

    Sunday, December 29, 2013 9:04 AM

All replies

  • Hi,

    I guess , View 'vw_Inventory' is created with SCHEMABINDING , so the objects referred to in the view definition can't be altered in a way that would make the view definition illegal .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Sunday, December 29, 2013 3:03 AM
  • If a Schema has some objects in it, then you cannot drop that Schema until you remove all the objects out of it. You can change the schema owner to another database principal and drop the user like,

    Alter Authorization ON Schema::username TO DBO;

    Drop User username;

    other option would be Transfer the username schema objects to a different schema and drop username schema and user like.

    Alter Schema DBO Transfer Username.[OBEJECTNAME] ;
    --need to repeat this until you transfer all objects under this schema
    Drop Schema username;
    Drop User username;
    



    Regards,
    Praveen D'sa
    MCITP - Database Administrator - 2008
    sqlerrors

    Sunday, December 29, 2013 9:04 AM