Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

re-install system stored procedures sql 2000

Status
Not open for further replies.

aspijker

Programmer
Feb 14, 2007
37
NL
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
 
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
 
Hi, it is an extended stored procedure, so there is nothing to script out, sorry, forgot to mention that.

Thanx for trying though :)
 
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
 
Also ... give this a shot:

Exec Master..sp_sdidebug 'LEGACY_ON'

Thanks

J. Kusch
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top