Deleting stored procedures through query analyzer

Hi All!

I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:

1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day

I've got everything except removing the stored procedure so I will really appreciate the help.

Thank you all!

Rijckewaert wrote:

Hi All!

I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database?

Transact-SQL ... here Oracle is notwelcome

the actual traditional code is

IF OBJECT_ID('[schema/owner].[object]') IS NOT NULL

DROP PROCEDURE [schema/owner].[object];

Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:

1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day

I've got everything except removing the stored procedure so I will really appreciate the help.

Thank you all!

this "design" is not clear to me... why would you always drop an object to be recreated (if the inner code remains the same)?

is this becouse of the well known "orphaned users" problem?
this problem (perhaps the problem you are experimenting) is involved when a "restored" database user(s) is no longer mapped to a corresponding server's standard SQL Server login..
in SQL Server 2000 this means the relationship between master.dbo.syslogins is broken with database.dbo.sysusers...
actually the JOIN on master.dbo.syslogins.sid ~ database.dbo.sysusers.sid

if this is the case, you do not need to delete the object and the user... you just need to resync it/them, via the sp_change_users_logins system stored procedure http://msdn2.microsoft.com/en-us/library/ms174378.aspx ... please have a look at http://msdn2.microsoft.com/en-us/library/ms175475.aspx as well..

if this is not the case... please expand

regards