none
Model database without db_owner RRS feed

  • Question

  •  

    I am receiving this message in my MOM Server:

    The system stored procedure sp_helpdb, which is used to gather information about the databases, has returned an error that may indicate that it cannot determine the db owner for the database [model]. Here are the details: sp_helpdb @dbname='model' on SQL Server Instance: INSTANCENAME. Error number: 515, Error Information: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
     
    So, I run this script in the Master DB:
    select name, suser_sname(sid) from master.dbo.sysdatabases where suser_sname(sid) is NULL
     
    And I get the result that the db_owner is set to NULL!
     
    I know that it is not possible to change the DB Owner of the model database but is there any workaround to solve this without reinstalling or similar solutions?
     
     
    Wednesday, December 5, 2007 12:35 PM

Answers

  •   From the SID’s structure I can tell you that it seems like a Windows user, most likely from a different domain (probably a test domain, but probably you have more information on this one) or that this Windows user doesn’t exist in the system anymore.

     

      I would recommend calling sp_changedbowner from a T-SQL statement directly instead of using the management tools. Changing the owner of master DB this way shouldn’t affect your applications. Model DB is used only when creating a new DB (i.e. it is a template DB), but the dbo information will be replaced during the DB creation time.

     

      Please let us know if this worked.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, December 10, 2007 8:14 PM
    Moderator
  •   I just gave it a try. Actually it seems like we tried to prevent such scenarios by explicitly blocking changing he owner of model temp & master DBs. The error message referring to master DB is a usability bug (it should read “model database”). I didn’t realize it before, I apologize for this omission; I should have tested it myself, but I assumed there was no such special rule for model as in your case there was already a different owner. At this point I am not sure how this data could have been modified in a normal scenario, but if you could submit a repro I will really appreciate it.

     

      This seems to be an out of ordinary scenario, and at the very least a usability bug, but unfortunately until I understand under what circumstances it is possible to modify the dbo information for model DB I can only think about updating the system table directly as a workaround.

     

    IMPORTANT SECUIRTY NOTE: Please notice that modifying a system table is workaround to solve a problem caused by a bug and not something we would ever suggest for any normal operation. Modifying the system tables directly is a dangerous operation that may cause system instability and even data loss. I am including below an excerpt from BOL:

     

    Caution  Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. If you create stored procedures while the allow updates option is enabled, those stored procedures always have the ability to update system tables even after you disable allow updates. On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services.

    I strongly recommend contacting Microsoft PSS to get better guidance on how to solve this one. Also because of this very same reason I apologize in advance as I won’t include any sample code on how to modify the system tables in this forum.

     

    I strongly recommend reading more about updating system tables in BOL. Here are some links that hopefully will be useful:

    ·         http://msdn2.microsoft.com/en-us/library/aa196704(sql.80).aspx

    ·         http://msdn2.microsoft.com/en-us/library/ms186267.aspx

    ·         http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89594&SiteID=1

     

    I would really like to ask once again if you can get us a repro on how to get into this scenario. Once we understand the root cause of the bug, we may also be able to give you a better workaround that doesn’t involve modifying the system tables.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, December 12, 2007 12:43 AM
    Moderator
  • Thanks for your answers.

    Seems like the problem is solved!

    I think that my problem was also because a Windows user that was deleted.

     

    Happy New Year for you all... 

    Thursday, December 27, 2007 11:00 AM

All replies

  •  From your description it seems like it may be an orphaned dbo. Most likely there is a SID associated with the database, but this principal doesn’t exist on the system anymore.

     

      Typically this can be corrected by changing ownership to a valid login in the system using sp_changeowner (this should work for SQL Server 2000 & SQL Server 2005).

     

      If you need further assistance with this issue, please let us know and please provide us with further details: SQL Server version, the SID of the current dbo (this should give us an idea if the orphaned SID was a SQL or Windows principal).

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Wednesday, December 5, 2007 9:55 PM
    Moderator
  • Hi.

    I forgot to mention but I already tryed it! When executing the script I get the message "Cannot change the owner of the master database"

    As you asked I checked the SID of the db. I think that there is something messed up because all other DB's SID are 0x01 but the model DB has this SID:

    0x0105000000000005150000006BD66204FBDA8B7F43170A328CDA0100.

     

    The SQL Server is version 2000. (8.0.2039)

     

    The dbo is set to NULL and when trying to check database properties from a SQL Server 2005 Management I get this message:

     

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Cannot show requested dialog.

    ------------------------------
    ADDITIONAL INFORMATION:

    Cannot show requested dialog. (SqlMgmt)

    ------------------------------

    Property Owner is not available for Database '[model]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476

     

    When I access properties via SQL Server 2000 I get the information that dbowner is Unknown.

     

    And I dont know if it is important but we have our SharePoint Databases runing on this server.

     

    Thanks in Advance

     

    _BM_

    Monday, December 10, 2007 11:45 AM
  •   From the SID’s structure I can tell you that it seems like a Windows user, most likely from a different domain (probably a test domain, but probably you have more information on this one) or that this Windows user doesn’t exist in the system anymore.

     

      I would recommend calling sp_changedbowner from a T-SQL statement directly instead of using the management tools. Changing the owner of master DB this way shouldn’t affect your applications. Model DB is used only when creating a new DB (i.e. it is a template DB), but the dbo information will be replaced during the DB creation time.

     

      Please let us know if this worked.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, December 10, 2007 8:14 PM
    Moderator
  • Hi,

     

    Refer Step 3 in the below blog for changing db owner.

    http://blogs.msdn.com/gertd/archive/2007/07/05/changing-database-ownership.aspx

    Monday, December 10, 2007 11:43 PM
    Moderator
  • Hi there,

     

    I have the same issue and the above solutions did not work. I have a model database that has NULL as the 'dbo'. I've tried using sp_changedbowner 'sa', @map='false' and i still get the message 'Cannot change the owner of the master database'.

     

    Why does it say master database when im trying to change the 'dbo' of the model database? I am definitely in the model database.

    Any other suggestions as to how to reset 'dbo' of model to 'sa'?

     

    Thanks in advance

    Tuesday, December 11, 2007 3:55 PM
  • Found the solution elsewhere; though of course not recommended to update system tables directly..

     

    -- To change sysdatabases
    exec sp_configure 'allow updates', 1
    reconfigure with override
    GO

    update sysdatabases
     set sid = 0x01
     where name = 'model'
    GO

    exec sp_configure 'allow updates', 0
    reconfigure with override
    GO

    Tuesday, December 11, 2007 8:24 PM
  •   I just gave it a try. Actually it seems like we tried to prevent such scenarios by explicitly blocking changing he owner of model temp & master DBs. The error message referring to master DB is a usability bug (it should read “model database”). I didn’t realize it before, I apologize for this omission; I should have tested it myself, but I assumed there was no such special rule for model as in your case there was already a different owner. At this point I am not sure how this data could have been modified in a normal scenario, but if you could submit a repro I will really appreciate it.

     

      This seems to be an out of ordinary scenario, and at the very least a usability bug, but unfortunately until I understand under what circumstances it is possible to modify the dbo information for model DB I can only think about updating the system table directly as a workaround.

     

    IMPORTANT SECUIRTY NOTE: Please notice that modifying a system table is workaround to solve a problem caused by a bug and not something we would ever suggest for any normal operation. Modifying the system tables directly is a dangerous operation that may cause system instability and even data loss. I am including below an excerpt from BOL:

     

    Caution  Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. If you create stored procedures while the allow updates option is enabled, those stored procedures always have the ability to update system tables even after you disable allow updates. On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services.

    I strongly recommend contacting Microsoft PSS to get better guidance on how to solve this one. Also because of this very same reason I apologize in advance as I won’t include any sample code on how to modify the system tables in this forum.

     

    I strongly recommend reading more about updating system tables in BOL. Here are some links that hopefully will be useful:

    ·         http://msdn2.microsoft.com/en-us/library/aa196704(sql.80).aspx

    ·         http://msdn2.microsoft.com/en-us/library/ms186267.aspx

    ·         http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89594&SiteID=1

     

    I would really like to ask once again if you can get us a repro on how to get into this scenario. Once we understand the root cause of the bug, we may also be able to give you a better workaround that doesn’t involve modifying the system tables.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, December 12, 2007 12:43 AM
    Moderator
  • I think the problem started before me actually. When i came into this shop, the model database only had a 'dbo' of the person whom i replaced - i dont know how they could have actually become 'dbo' of model to begin with. As i started cleaning things up, i removed her login from SQL logins and thus this is where i believe the orphaned model dbo took place. I wont be able to reproduce it but just giving you some background and hope it sheds a bit more light..

    Wednesday, December 12, 2007 3:09 PM
  •    It may be possible that the previous DBA may have hacked the system tables and assign herself as model DBO. Unfortunately if this is the case, most likely the only workaround may be to modify the system tables to fix them (at least I don’t know of any other workaround at this point), and as stated in BOL, I cannot recommend this solution as I am not part of the Microsoft Product Support Services (PSS).

     

        I am really sorry for not being able to give you a simple answer for this one. Please contact a Microsoft PSS representative to obtain better guidance on this particular case.

     

      Thanks a lot.

       -Raul Garcia

       SDE/T

       SQL Server Engine

    Thursday, December 13, 2007 6:54 AM
    Moderator
  • Thanks for your answers.

    Seems like the problem is solved!

    I think that my problem was also because a Windows user that was deleted.

     

    Happy New Year for you all... 

    Thursday, December 27, 2007 11:00 AM