Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    99

    Unanswered: Error running sp_helpdb

    When I run sp_helpdb against the master (or any other DB for that matter) I get the following error:

    Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
    Cannot insert the value NULL into column 'owner', table 'tempdb.dbo.#spdbdesc_ 0010001A6EF'; column does not allow nulls. INSERT fails. The statement has been terminated.

    I have about 20 DBs on a SQL Server 2000 Standard Edition Instance with sp3a .

    There have been no changes made to the Server or the SQL Server Instance in quite some time. Can anyone tell me how I should proceed??????

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    I think spid is null in master.dbo.sysdatabases,
    check that first.

    select spid from master.dbo.sysdatabases
    u can use sp, sp_changedbowner 'ownername' if it is null .

    And check that link,it may help u.
    http://dbforums.com/t554105.html
    Last edited by mallier; 11-15-05 at 10:37.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    99
    I did check select * from master.dbo.sysdatabases and I don't see anything unusual. and thanks.. but that other post wasn't very helpful... : (

    I did some poking around and noticed that when I look at the properties of my Model and msdb databases in Enterprise Manager, they BOTH have {unknown} for Owner !!?? that CAN'T be a good thing !!
    I checked BOL for sp_changedbowner and it says that you can't change the owner on master, msdb and ,model - so I'm a little leary to try it... Anyone have an suggestions? ... please..

    Can I use sp_changdbowner on model and msdb if they are already Null or as Enterprise Manager puts it... UNKNOWN ??? ???

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Yes u cannot chage owner of master,msdb and model db,Onlyway is,


    eg:this will will change model db owner to 'sa'
    exec sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    go
    update sysdatabases
    set sid = 0x01
    where name = 'model'
    go
    sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    Last edited by mallier; 11-15-05 at 11:24.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    99
    Can I ask a dumb question????

    How does that code change the owner? I see where it sets the SID. and, should the SID be set the same for model and msdb???

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    0x01 is the sid of sa user,msdb,model db's owner should be under sa.

    u can see that in,
    Code:
    select name, suser_sname(sid),sid
       from master.dbo.sysdatabases
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  7. #7
    Join Date
    Mar 2004
    Posts
    99
    Told you it was a dumb question. Thanks much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •