Deleting / Modifying a System Object (stored procedure) 1

[RSfromCO]

I create a stored procedure which included the code

--Mark procedure as system object
EXEC sp_MS_marksystemobject sp_generate_inserts
GO

PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'
GRANT EXEC ON sp_generate_inserts TO public

Now I want to modify or delete the stored procedure but I can't seem to modify it in Enterprise Manager and if I try to delete it I get the message....

You tried to delete one or more system objects, they were not deleted.

How can I modify or delete this stored procedure now?

 

[mrdenny]

You have to update the status column of the sysobjects table for that object.

exec sp_configure 'allow updates', 1
reconfigure
go
update sysobjects
set status = status + 2147483648,
	base_schema_ver = 0
where name = 'sp_who3'
go
exec sp_configure 'allow updates', 0
reconfigure

Only run the update command once. If you do it more than once it'll change the status of the proc to the wrong value.

I would recommend not setting non-microsoft procedures as system objects as in SQL 2005 you can't update the sysobjects table any more if there is a problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RSfromCO]

Thanks for the reply.

Is the line where name = 'sp_who3' correct? I just want to verify before I try to execute this. I'm not sure where the "sp_who3" is coming from.

I'm assuming that after I run this that I will be able to delete the stored procedure and perhaps re-create it and not make it a system object.

 

[mrdenny]

sp_who3 is a version of sp_who2 that I wrote. Replace sp_who3 with the name of your proc.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RSfromCO]

I ran the update command and received these messages...

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Server: Msg 5808, Level 16, State 1, Line 2
Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

I'm still not able to delete or modify the procedure.

 

[mrdenny]

Hugh, looks like I forgot to add something to the reconfigure commands.

exec sp_configure 'allow updates', 1
reconfigure with override
go
update sysobjects
set status = status + 2147483648,
    base_schema_ver = 0
where name = 'sp_who3'
go
exec sp_configure 'allow updates', 0
reconfigure with override

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com