Systems Engineering and RDBMS

SP_HELPDB and the Insert Error

Posted by decipherinfosys on July 10, 2008

A reader asked yesterday how to resovle this error that he was getting when trying to run sp_helpdb on one of the instances:

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column ‘owner’, table ‘tempdb.dbo.#spdbdesc_
The statement has been terminated.

As you might already know, sp_helpdb returns information about all of your databases on a given instance and can be very useful for different reasons – one can be to monitor the size of the databases – we had blogged that before here. For the error above, that the end user received – the issue resolution is pretty straight forward. As the error message states, the database needs to have an owner else it will try to insert a NULL value into the “Owner” column of the temporary table and it will fail to provide the output when the system stored procedure sp_helpdb gets executed.

And the resolution is pretty straight forward as well – find the databases which do not have an owner assigned and assign an owner to them. We can use sysdatabases or sys.databases (in SQL Server 2005 and 2008):

For SQL Server 2000:

select name as database_name, SUSER_SNAME(sid) as owner_name, crdate as create_date_time
from master.dbo.sysdatabases

For SQL Server 2005:

select name as database_name, SUSER_SNAME(owner_sid) as owner_name, create_date as create_date_time
from master.sys.databases

And then see which databases do not have an owner assigned to them. The ones that do not have an owner assigned to them can be assigned an owner using this command:

Use <put the database name here>

go

exec sp_changedbowner ‘put the owner name here’

Once that is done, execution of sp_helpdb will work perfectly fine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: