System Stored Procedures

Question

System Stored Procedures

rosh-396756

SSCrazy

Points: 2338
More actions
November 28, 2008 at 12:48 am

#194420

This morning when I created a stored procedure it reflected as a system stored procedure. Till yesterday the sa login was creating user stored procedures..Am not able to understand why this happened?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

Hi,

I don't quite follow; the only way to have a stored procedure "reflect" as a system stored procedure is to run EXECUTE sp_ms_marksystemobject.

Can you give more specific on:

- in what way it is "reflecting" as a system stored procedure

- where does 'sa' enter the picture?

- what is the actual problem?

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

rosh-396756 SSCrazy Points: 2338 More actions · Answer 2

When I create a stored procedure it is getting marked as a system stored procedure, as in the type is getting reflected as 'System' instead of 'User'.

Is there any setting which might have accidently gotten set which causes this behaviour, because till yesterday this was not so.

Both new tables and procedures are getting marked as system objects.

I thought it was a login issue but I am not sure.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

rosh (11/28/2008)
When I create a stored procedure it is getting marked as a system stored procedure, as in the type is getting reflected as 'System' instead of 'User'.

In what tool are you seeing this and where in that tool?

Also, you aren't naming your stored procedures beginning with "sp_" are you?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 4

Which version of SQL Server are you on and as previously asked, what tool are you looking in or which query are you issuing to get this information?

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

rosh-396756 SSCrazy Points: 2338 More actions · Answer 5

SQL Server 2000.

I am viewing this in the enterprise manager.

My sps start with Gsp_

Please see attachment.

rosh-396756 SSCrazy Points: 2338 More actions · Answer 6

Its working fine now after restarting the SQL Server Service.

Thanks.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Ooo Kay...

?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736352 More actions · Answer 8

Sounds like the tool flaked. I wouldn't worry about it unless it happens again.

rosh-396756 SSCrazy Points: 2338 More actions · Answer 9

Hope so, I wanted to restart the service at that moment but was apprehensive as there were releases to be made that day. Didnt want the whole thing to conk out.