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

  • Question

  • The title makes it sound as if question has been answered, but in fact it has not.  Because of differences.

    A db_owner created a user in the database.  This person erroneously left the default schema as dbo.  When the attempt was made to delete the user the error message cited above was received.  So created a schema in the database with the same name as the user.  Gave the user ownership.  Then set the default schema to be the new schema.  Tried to delete.  The schema deleted just fine.  The user refused to delete again with the error message above.  Ran below query to see what schemas were  owned by this user.

    SELECT name FROM sys.schemas WHERE principal_id = USER_ID('myuser')
    But the query returned a result set with zero rows.  So how can this user be owning a schema when it doesn't own any?  Why can't we drop this user for the reason of schema ownership when it owns no schemas?

    Edward R. Joell MCSD MCDBA




    • Edited by joeller Wednesday, June 20, 2012 3:52 PM Query hidden by formating
    Tuesday, June 19, 2012 6:33 PM

Answers

  • Unfortunately, I have little idea of what is going on. I see that message 15138 has a parameter where the word "schema" appears in the actual message. Maybe there is a bug so that it says "schema" when it should say something else?

    I don't have much hope, but try this:

    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 the result set from the above, and if prints something that catalog view may be worth looking into a little more. Change "youruser" to the correct username - and of course run in the right database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Tuesday, June 26, 2012 1:27 AM
    • Unmarked as answer by joeller Monday, July 2, 2012 4:36 PM
    • Marked as answer by joeller Monday, July 2, 2012 7:01 PM
    Wednesday, June 20, 2012 9:45 PM
  • Erland ran your script and and ran the result set. The user showed up in two sys views, sys.database_principals and sys.schemas. (as expected)

    Then did select top 1000 from sys.shemas and altered the query to join to sys.database_principals and included name column from that view in the select.

    SELECT s.*, p.[name] 
    
    FROM sys.schemas s
    JOIN sys.database_principals p ON s.Principal_id = p.principal_id

    And that query result showed that somehow it was now showing that user as the owner of the schemas db_datareader and db_denydatareader. (!?!) For some reason this did NOT show up when we ran the query below which returned an empty result set (see OP). (Again !?!)

    SELECT s.name As SchemaNameResult 
    FROM sys.schemas s
    WHERE s.principal_id = USER_ID(‘youruserid’)

    So I opened the properties gui for those schemas sure enough those schemas were showing our user as the owner. (Now bear in mind that I did look in all of those property guis when the problem first occurred. I can only figure that the government database manager, whom I am supporting, changed something after I viewed those results.)

    Anyway, I then reset their owners back to be the roles that are supposed to own them. Voila!!! The user was able to be deleted.

    Very bizarre all around.


    Edward R. Joell MCSD MCDBA

    • Marked as answer by joeller Monday, July 2, 2012 7:01 PM
    Monday, July 2, 2012 7:00 PM

All replies

  • Hm, that seems mysterious. You seem to have run the correct query to diagnose the situation - I will have to assume that you ran it in the correct database. :-) But just to cover up, try this query and scurtinise the result:

    SELECT dp.name AS Owner, s.name AS [schema]
    FROM   sys.schemas s
    LEFT   JOIN  sys.database_principals dp ON s.principal_id = dp.principal_id
    ORDER  BY dp.name

    Also try DBCC CHECKCATALOG in case there is some sort of corruption.

    If none of this works out, maybe you should open a case with Microsoft.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 19, 2012 9:53 PM
  • Hi

    Here is the another way

    Open SSMS-->VIEW-->Object explorer details---->DATABASES(select the Database where your getting problem)---->Security---->Schemas

    here you can check which principal owns the schema

    Wednesday, June 20, 2012 4:29 AM
  • it is strange, how ever can you check the below- will that work

    you can do like this ->Identify the schema then trasfer the schema to other then drop the user.
    1.      Get all the schemas owned by the Userid that you Want to delete

    SELECT s.name As SchemaNameResult

    FROM sys.schemas s

    WHERE s.principal_id = USER_ID(‘youruserid’)

    2.   then replace the YourSchemaName with each record from the poutput that you have identified.

    ALTER AUTHORIZATION ON SCHEMA::<YourSchemaName> TO dbo;

    or change DBO to other user...

    3.Drop the user


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Wednesday, June 20, 2012 11:11 AM
  • Erland: I  will try your query.

    Sushil: That was the first thing we tried before we even tried to do a query on the sys.schemas, and of course none of the schemas were shown as being owned by this user.

    Rama:  When I opened this thread today, I noted that in the original post, the code block with the query I ran was obscured by scroll bars, so I assume that is the reason that you did not see that I had already run the query you are suggesting.


    Edward R. Joell MCSD MCDBA

    Wednesday, June 20, 2012 3:59 PM
  • Erland

    I ran you query.

    User is not listed as a schema owner.

    DBCC CheckCatalog returned no error messages.

    We don't have any MS support.


    Edward R. Joell MCSD MCDBA

    Wednesday, June 20, 2012 4:14 PM
  • Unfortunately, I have little idea of what is going on. I see that message 15138 has a parameter where the word "schema" appears in the actual message. Maybe there is a bug so that it says "schema" when it should say something else?

    I don't have much hope, but try this:

    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 the result set from the above, and if prints something that catalog view may be worth looking into a little more. Change "youruser" to the correct username - and of course run in the right database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Tuesday, June 26, 2012 1:27 AM
    • Unmarked as answer by joeller Monday, July 2, 2012 4:36 PM
    • Marked as answer by joeller Monday, July 2, 2012 7:01 PM
    Wednesday, June 20, 2012 9:45 PM
  • Been dealing with other more pressing problems so haven't been able to work on this as yet.  Ms Zhang if you had read Erland post you would have seen that he was not proposing a solution but a way to investigate the problem.

    Edward R. Joell MCSD MCDBA

    Monday, July 2, 2012 4:44 PM
  • Erland ran your script and and ran the result set. The user showed up in two sys views, sys.database_principals and sys.schemas. (as expected)

    Then did select top 1000 from sys.shemas and altered the query to join to sys.database_principals and included name column from that view in the select.

    SELECT s.*, p.[name] 
    
    FROM sys.schemas s
    JOIN sys.database_principals p ON s.Principal_id = p.principal_id

    And that query result showed that somehow it was now showing that user as the owner of the schemas db_datareader and db_denydatareader. (!?!) For some reason this did NOT show up when we ran the query below which returned an empty result set (see OP). (Again !?!)

    SELECT s.name As SchemaNameResult 
    FROM sys.schemas s
    WHERE s.principal_id = USER_ID(‘youruserid’)

    So I opened the properties gui for those schemas sure enough those schemas were showing our user as the owner. (Now bear in mind that I did look in all of those property guis when the problem first occurred. I can only figure that the government database manager, whom I am supporting, changed something after I viewed those results.)

    Anyway, I then reset their owners back to be the roles that are supposed to own them. Voila!!! The user was able to be deleted.

    Very bizarre all around.


    Edward R. Joell MCSD MCDBA

    • Marked as answer by joeller Monday, July 2, 2012 7:01 PM
    Monday, July 2, 2012 7:00 PM
  • And that query result showed that somehow it was now showing that user as the owner of the schemas db_datareader and db_denydatareader. (!?!)

    Huh? Now, that's crazy!

    You said you did SELECT TOP 1000 from sys.schemas - how many schemas do you have really? And what is SELECT MAX(principal_id) FROM sys.database_principals running at for the moment?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 2, 2012 8:20 PM
  • "Select top 1000" is the SS 2008 context menu entry for what used to be called "open table".  There were only 19 schemas in that database.  The fixed database roles have principal_id in the 16000s but the for the real db users the max id was 16.  (Which is now gone.)

    Edward R. Joell MCSD MCDBA


    • Edited by joeller Monday, July 2, 2012 8:46 PM
    Monday, July 2, 2012 8:45 PM
  • OK. I was thinking if that if you have a lot of them maybe it was due a a 16-bit cutoff somewhere. Just a wild idea.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 2, 2012 9:56 PM
  • Erland, I am having the same trouble, and tried what you suggested, and found items in the following tables:

    sys.server_principals

    sys.conversation_endpoints

    sys.services

    sys.database_principals

    But I dont know how to correct them.

    Could you please help me?

    The situation is as follows: I have sql 2005 in one server, and set up another server with sql 2012. I am starting migrating some databases, so I took a backup from one database and restored it in the new server.

    It created the database, and of course no login exists and I don´t want it to exists. I just want to take the database structure, and redefine the users. But the user from the original database was created in the new database on the new server. But I cannot delete it. When I executed the following sentence:

    SELECT s.name As SchemaNameResult

    FROM sys.schemas s

    WHERE s.principal_id = USER_ID(‘youruserid’)

    No rows were returned, so I tried your post and I got the tables that i mentioned.

    But now I don ´t know how to fix it.

    I just want to get rid of the user and recreate another users

    Thanks in advance for your support

    Monday, May 27, 2013 7:55 PM
  • So sys.server_principals are where you have the logins and sys.database_principals are where the database users are defined, so they are no cause for alarm.

    But the other two are relevant here. sys.services is Service Broker services and sys.conversation_endpoints are active conversations.

    You will have to decide what to with the service(s). You should probably don't drop them, as that could affect the function of the database. You could transfer them to a different owner, but there may be a reason why the service is owned by this particular user. I would find someone who has detailed knowledge about this database before I start to meddle with the services.

    As for the active conversations, that's a somewhat different story. If you have only taken a copy of the database, you should probably run this:

    select 'END CONVERSATION ' +
           quotename(convert(char(36), conversation_handle), '''') +
           ' WITH CLEANUP'
    from sys.conversation_endpoints

    and then run the result. But if you have moved the database, this is probably not the right thing to do. Again, you need to find someone who knows about the Service Broker implementation in this database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, May 27, 2013 9:41 PM
  • Thank you very much for your answer. You were right and now I solved the problem.

    As I restored a database from another server I think it came with one service associated with the user. I could not reassign the ownership to another user, as I was not able to see how this could be done.

    I run the following sentence: alter database dbname set enable_broker with rollback immediate, which solved the associations, and therefore I was able to drop the user

     

    Thank you very much!

    Wednesday, May 29, 2013 12:13 PM
  • As I restored a database from another server I think it came with one service associated with the user. I could not reassign the ownership to another user, as I was not able to see how this could be done.

    While moot now, the command would be:

      ALTER AUTHORIZATION ON SERVICE::thatservice TO someuser


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 29, 2013 9:35 PM
  • Thanks again!
    Friday, May 31, 2013 9:25 PM