error when using sp_helpdb
-
I am going thru servers and trying to find out who owns databases. I am getting this error on some of the servers. Can you tell me why??
I am using exec sp_helpdb
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.
thanks!!!
-
Hello Angelindiego,
If you open sp_helpdb stored procedure on master database you will see that the line 53 is around this statement:
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)
At the beginning of the procedure the field for the owner is not defined as nullable when temp table is created:
create table #spdbdesc
(
dbname sysname,
owner sysname,
created nvarchar(11),
dbid smallint,
dbdesc nvarchar(600) null,
dbsize nvarchar(13) null,
cmptlevel tinyint
)
There are some databases that were restored from other servers that may have owner unknown or whoever was the owner may or may not be a login anymore. In this case it is possible that expression suser_sname(sid) that fills out the owner field will return NULL and since this filed is not nullable you will have your (not exactly yours) error. For example, when I run the following query it returns NULL:
select suser_sname(25)
Hope this is a good explanation.
Regards,Yelena Varsha
-
Thank you for the explanation. This brings me to ask you another question. The reason I was trying to run the sp_helpdb, is to find out who owns what databases, because a person who used to do alot of db work is no longer with us and I wanted to see if he is the owner on any of the databases before I delete is login on the servers. Thus, my problem. So, the next question is, how do I find out who are owners without using sp_helpdb?? Is there another way of getting this info??
Thank you again!!!
-
Normally you can't remove a login when it owns some objects. (Test it first.)
-
I think you can query servers for each database as:
Select * from sysusers where SID = (Select SID from syslogins where name = 'previous sysadmin login name goes here') and UID = 1
This line will return 1 row if the database owner is your previous sysadmin. I would use this query inside sp_MSforeachdb so one query will scan all databases.
Regards,Yelena Varsha
-
It basically opens a cursor and selects all databases on the server.
Then the command is the tsql you want executed for each of the databases on the server. The question mark (?) is used as a wildcard to replace the database name in the command.
So basically you open a cursor to execute dynamic sql on all databases. It's really usefull in admin task but I wouldn't want to see that on a production server
. -
Angelindiego,
sp_MSforeachdb is Undocumented Stored Procedure and as such is in the article on this site by Alexander Chigrik
http://www.sqlservercentral.com/columnists/achigrik/sql2000.asp
SQL Server 2000 Useful Undocumented Stored Procedures
He has good descriptions for this and other procedures.
Also I would check if the login you are talking about owns other objects, not only databases but tables, views etc. You have to check in all databases if the user that corresponds to this login owns any objects.
Regards,Yelena Varsha
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply