How do I change the Type of a Stored Procedure from User to System?

[Vithar]

I built a database by using a generated script from the original
database. It built the System Store Procedures as User type. How do I
change them back to System type?

[Erland Sommarskog]

Re: How do I change the Type of a Stored Procedure from User to System?

Vithar (cjohnson@cskau to.com) writes:[color=blue]
> I built a database by using a generated script from the original
> database. It built the System Store Procedures as User type. How do I
> change them back to System type?[/color]

What database was that? System procedures are really only in master.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Chris Johnson]

Re: How do I change the Type of a Stored Procedure from User to System?


When I look in Enterprise Manager under Stored Procedures I see a huge
list of Stored Procs. Normally I edit my SQL Registration to not show
System stored procs. That way only the SPs I create show up in the list.
Since the database move all the SPs that were normally labelled System
are now marked as User so they all show up. I want to change those back
to System so they won't appear in that list again.


*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Re: How do I change the Type of a Stored Procedure from User to System?

Chris Johnson (cjohnson@cskau to.com) writes:[color=blue]
> When I look in Enterprise Manager under Stored Procedures I see a huge
> list of Stored Procs. Normally I edit my SQL Registration to not show
> System stored procs. That way only the SPs I create show up in the list.
> Since the database move all the SPs that were normally labelled System
> are now marked as User so they all show up. I want to change those back
> to System so they won't appear in that list again.[/color]

Which was the name of the database? Can you give a sample name of a few
stored procedures that you see, which you think should be marked as
system procedures?

I should add that there is no documented and supported way of doing this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[kevin.smith]

Solution

I have the same problem in a database I inherited. All the SPs start with dt_ and have a type of System in other databases.

The solution is to run the following SQL command on each SP:

[FONT=Courier New]EXEC sp_MS_marksyste mobject 'dt_addtosource control'[/FONT]