none
DB role unable to delete-The database principal owns a schema in the database, and cannot be dropped.

    Question

  • I created a role . Initially when I created schema assigned as dbo. now I change back to  role1 as image. But I am unable to drop the role.

    I run following commands

    ALTER

    AUTHORIZATIONONROLE::[role1] TO[role1];

    Still getting following error

    Drop failed for DatabaseRole 'role1'.  (Microsoft.SqlServer.Smo)

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

    Any one can help me on . Great Thanks


    Monday, May 28, 2018 3:44 AM

All replies

  • Try 

    ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 
    GO
    DROP ROLE role1


    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

    Monday, May 28, 2018 5:17 AM
    Moderator
  • Hi ashwan,

    That means, you are trying to drop a user owning a schema. In order to drop the user, you have to find the schema that’s assigned and then transfer the ownership to another user/role or to drop it.

    You can find out which schema is owned by this user with the query below:

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

    Then refer to Uri Dimant's script to drop the role.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 28, 2018 7:57 AM