December 20, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/whoownsthatdatabase.asp
March 3, 2003 at 3:53 pm
Of course, your article arrived in my inbox about 2 hours after having this same issue myself! Almost perfect timing.
I found the following tip in this thread:
http://www.tek-tips.com/gpviewthread.cfm/qid/438490/pid/183/lev2/4/lev3/27
sp_changedbowner 'anyotheruser'
--then
sp_changedbowner 'sa'
Seemed to work OK, plus maybe easer to remember than your query...
March 3, 2003 at 5:12 pm
March 4, 2003 at 12:08 pm
Steve,
This article caught my eye because one of our servers was throwing the same error when I run sp_helpdb. Read the article and found that seven databases on one of our servers didn't have owners. So, I wasn't interested in modifying the system tables so I did some experimenting with sp_changedbowner. I found that I couldn't change the owner to sa but I could change it to another valid user. Then, I changed the owner to sa. sp_helpdb runs without errors now. Saw gr8scott's post after returning to SQL Server Central to comment on your article. Nice job.
March 5, 2003 at 10:19 am
Thanks. Never had a db with no owner, though that procedure makes sense.
Steve Jones
September 18, 2003 at 3:07 pm
Curious if Microsoft is going to do anything to keep it from crashing in this case. Should be possible to have it report db name as `problem with owner` or some such, and go on to give normal results from sp_helpdb for rest of dbs.
Of course, you can use
sp_helptext sp_helpdb
then copy/paste and modify it for your own version (e.g. tw_helpdb). But then any underlying Microsoft changes in future SQL updates need to be brought in to that custom stored procedure.
March 4, 2004 at 11:25 am
Appreciate you sharing your research and time on this topic. I believe the following are true.
a. Tempdb is created from Model DB when SQL Server is started
b. That user procedures, UDF's, etc. when stored in Model DB get passed along to new DBs created.
C. Model DB size is default DB size for new DBs.
Therefore in regards to your explanation's comments about replacing Model, if necessary, please be aware it's possible this could have some "side effects" that are unexpected. Of course if Model is backed up first, then a restore from the backup copy avoids this pitfall (albeit possible at the cost of a small amount of downtime).
Can anyone verify my suppositions?
Keep up the good work!!
March 10, 2004 at 9:36 am
Interesting idea and thanks. If I get some time, I'll try to look into the tempdb thing.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply