Problem dropping Stored Procedures

[Jack]

I am having problem dropping some stored procedures in the user databases that does not have an owner.
"Owner is NULL"
I got this error when i issued 'DROP PROC @procedurename' statement,
"cannot drop the procedure '@procedurename', because it does not exist in the system catalog."

I can see those stored procedures when i issued sp_help statement, and from the Enterprise Manager.
I am running SQL Server 7.0.

Thanks,
Jack.

[nr]

Try
select * from sysobjects where type = 'P'
and looking at the uid field for these SPs.
This field doen't allow nulls so must have something there.
Would guess that the owner is no longer a member of the database as this seems to give the symtoms you are experiencing.

Check that uid exists in the DB (sysusers)

Can set the owner of the SP to someone that does exist.
update sysobjects set uid = ? where name = '???'
(need to allow system catalogue updates)

You should question how these SPs ended up with a null user though and maybe consider recreating the database without the SPs as there might be other problems with it.


------------
Jack at 8/25/00 10:06:04 AM


I am having problem dropping some stored procedures in the user databases that does not have an owner.
"Owner is NULL"
I got this error when i issued 'DROP PROC @procedurename' statement,
"cannot drop the procedure '@procedurename', because it does not exist in the system catalog."

I can see those stored procedures when i issued sp_help statement, and from the Enterprise Manager.
I am running SQL Server 7.0.

Thanks,
Jack.

[Mauricio Ikegami]

Try sp_changeobjectowner (check Books OnLine) - better than playing with system tables...

Mauricio

------------
Jack at 8/25/00 10:06:04 AM


I am having problem dropping some stored procedures in the user databases that does not have an owner.
"Owner is NULL"
I got this error when i issued 'DROP PROC @procedurename' statement,
"cannot drop the procedure '@procedurename', because it does not exist in the system catalog."

I can see those stored procedures when i issued sp_help statement, and from the Enterprise Manager.
I am running SQL Server 7.0.

Thanks,
Jack.