System Stored Proc Cannot Edit

[rac2]

I used sp_MS_marksystemobject on my stored procedure in master. Now I cannot edit it, nor delete it, nor rename it. I am using Enterprise Manager (SQL Server 2000) and accessing the stored procedure for editing by clicking on Properties. The owner is dbo, which is not me. How do I revise my sytem stored procedure?

 

[NoCoolHandle]

I think you will need to become dbo.

 

[SQLBill]

You should NEVER edit a system stored procedure. If you create a stored procedure, it should be saved in the same database that it is going to be run against and not saved in the Master database.

But as NoCoolHandle says, you will need to get DBO privileges.

-SQLBill

Posting advice: FAQ481-4875

 

[rac2]

Well Bill, system stored procedures must be in master being as how they are gonna be run against many databases. And somebody has got to build them. And sometimes, we dont build them in final form the first time.

I find it interesting that one can create a procedure; specify that is it owned by dbo, who evidiently I am not; mark it as a system object; and then no longer have access to it. Kind of a one-way street. I dont feel that this is a bad thing, just that it is odd. In general, it seems a good thing that ordinary users not be able to tinker with system objects.

Anyway thanks for the answer. Guess I must visit the sys admin. Shudder.

 

[NoCoolHandle]

<snip>I find it interesting that one can create a procedure; specify that is it owned by dbo, who evidiently I am not; mark it as a system object; and then no longer have access to it.</snip>

It is by design and helps make sure that developers who don't have permissions on some ojects can still reference them inside code then build the code and have someone else execute the code.

It also helps to prevent ownership chain issues. But the primary reason is so that some people who might not necessarly have permissions to do the things their code can do, can still create the code.

Rob

 

[NoCoolHandle]

Oh,and Rac.

I agree with you. MS didn't write all the system stored procs I use or need. And of course they do need to be in Master, but I don't think there is any need to mark them as being a system object. As an sp_ they automaticly get used if a proc of the same name doesn't exist in the local db.

 

[rac2]

Maybe what I need is not a system stored proc.

It is not working on the system, whatever that may entail. It is for an application, a report, that runs against many databases, all of which have the same structure. Now I could put copies of my proc_report_interesting_facts in every single database, but that would be tedious.

So I put it in master and name it sp_report_interesting_facts. I find that when I

USE DBTwo
EXECUTE sp_report_interesting_facts

I get no rows.

Yes, the copy in DBTwo yields rows.

USE DBTwo
EXECUTE proc_report_interesting_facts

Some years ago, in this forum, from a well-regarded expert member, I learned that one must(?) use the undocumented system proc, sp_MS_marksystemobject, in order to run system procs from other than master. Indeed when I

USE master
EXECUTE sp_MS_marksystemobject 'sp_report_interesting_facts'

I can now

USE DBTwo
EXECUTE sp_report_interesting_facts

and get some rows.

Considering that sp_MS_marksystemobject is undocumented, how might I acccomplish this "legally"?

 

[mrdenny]

If you were able to run sp_MS_marksystemobject you are DBO.

To make your procedure no longer a system object run this.

update sysobjects
set status = 1610612737,
	base_schema_ver = 0
where name = '{procedure name}'

Keep in mind that updates to system objects are no longer allowed in SQL 2005.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[rac2]

So given the need to use a stored proc in many different databases, how would one accomplish that? Especially, considering that sp_MS_marksystemobject was never really intended for end-users and is not part of SQL Server 2005.

 

[mrdenny]

I've put procedures in the master database without marking them as system objects and I call them fine from other databases. For example I created an sp_who3 that I put in master and can call from anywhere.

Although it doesn't reference data within the local database. I'll look at my 2005 install and see what I can find for you.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)