re-install system stored procedures sql 2000

[aspijker]

Hi all,

While running source code in visual studio I get an error while connecting to the sql server 2000 database, that there is no permission for the user to execute sp in master: sp_sdidebug

When I cjheck in the master db, I see that this sp does not exist, it does exist on other sql servers that I administer. Is there a way to reinstall the system stored procedures on sql server 2000? Or maybe just that one sp?

Regards and thanx in advance for answering

Anne

 

[JayKusch]

While it is usually not advisable, but I have seen it at every shop I have worked at - lol, you can create stores procedures in the Master DB.

Just script out the SP you need, get into query analyzer and make sure your are pointed to the Master DB or add the "USE MASTER" and "GO" lines to the begining of the stored procedure.

Just remember, the reason we do not create objects in the Master DB, or any system DB for that matter, is that the objects could be removed during a service pack update, security patch or full version upgrade.

Thanks

J. Kusch

 

[aspijker]

Hi, it is an extended stored procedure, so there is nothing to script out, sorry, forgot to mention that.

Thanx for trying though

 

[JayKusch]

Same principle and warning applies for extended SPs in the Master DB.

That being said, all you do is fire up Enterprise Mngr, right click on the extended stored procedures folder in the Master DB and choose "New extended stored procedure".

From there you give the SP a name and the path to the object that the SP will leverage.

Thanks

J. Kusch

 

[JayKusch]

Also ... give this a shot:

Exec Master..sp_sdidebug 'LEGACY_ON'

Thanks

J. Kusch

 

[aspijker]

Hey GREAT!!! Thanx!