sp_helpdb error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sp_helpdb error

Author

Topic

vsmurali
Starting Member

4 Posts

Posted - 2005-12-06 : 12:52:21

Hi,
I have MOM 2005 installed to monitor all our sql servers. In one of the sql server on a specific db, I get error alerts when running the sp_helpdb error.

The error output is

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

I checked the sp_helpdb stored procedure and here is where it fails:

insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
select name, suser_sname(sid), convert(nvarchar(11), crdate), dbid, cmptlevel from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)

I understand this is a temporary table created by the stored procedure to insert all the db info.

I ran a query as follows:

select name, suser_sname(sid), crdate, dbid, cmptlevel
from master.dbo.sysdatabases

The output is

DB Name User Name Cr date db id cmpt
master sa 2000-08-06 01:29:12.250 1 80
model sa 2000-08-06 01:40:52.437 3 80
msdb sa 2000-08-06 01:40:56.810 4 80
RMSummary NULL 2002-06-13 16:40:32.203 8 80
tempdb sa 2005-11-29 19:10:48.450 2 80

The problem is in RMSummary database and the "dbo" login does not
have a username.

How do I add a User Name "sa" to the DB Owner "dbo" for the RMSummary database?

I did try the following things with no success:

1. Try to delete the 'dbo' user in 'RMSummary' database so that
I can add a new login name as 'dbo' and username is 'sa'.
Error message is 'dbo' owns some objects and does not get deleted.
2. I tried sp_changedbowner but it does not work either.

Any help on this quickly is much appreciated.

Thanks
Murali

Kristen
Test

22859 Posts

Posted - 2005-12-06 : 16:18:19

Did you recover that database from a different server ... and not re-synchronise the users with the current server?

Mind you, even if that's correct I'm not quite sure how the user "dbo" can be missing ... a user defined table-owner I could understand, but not "dbo"

Kristen

vsmurali
Starting Member

4 Posts

Posted - 2005-12-06 : 16:25:29

The 'dbo' user does exist in the 'RMSummary' DB without the login name (which should have been "sa"). I don't know how to change / update this.

Thx,

Murali

Kristen
Test

22859 Posts

Posted - 2005-12-06 : 16:28:01

Was it RESTOREd from a different server?

vsmurali
Starting Member

4 Posts

Posted - 2005-12-06 : 16:33:36

I don't know but I am checking it right now. Will let you know.

Murali

vsmurali
Starting Member

4 Posts

Posted - 2005-12-06 : 16:46:20

I have resolved the problem.

The solution is to reset the db owner to sa. After that sp_helpdb does works without any problem.
Code:
exec sp_changedbowner 'sa'

Thanks
Murali

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 00:47:48

As sp_helpdb will show all Db details all Dbs should have owner

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-07 : 03:16:04

I reckon the most likely scenario is that it was restored from a machine with a different "owner" and the corresponding owner has not been created on the new box. Which will go for any other owners of objects within the database too of course. Ho hum!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources