Mysteriously disappearing stored proc

Hi,
I have an issue with a stored procedure that is disappearing periodically.
It is called from the Agent every minute, and runs just fine for long
periods of a time, often more than a w at once. Then the stored
procedure will be deleted and I will see an entry in the event log for every
time the agent calls it that the stored proc is missing.
It is always the same stored proc. There is another stored proc that also
runs once per minute, and it is never missing.
Does anyone have a clue as to what could cause this, or perhaps some
recommendations for troubleshooting steps I can take in order to find this
out.
Thanks!
Ray ReavisSounds like someone's playing an april fools gag on you.
Response from Microsoft: "It's a feature!"
Sorry, couldn't resist... ; )
"Ray Reavis" wrote:

> Hi,
> I have an issue with a stored procedure that is disappearing periodically.
> It is called from the Agent every minute, and runs just fine for long
> periods of a time, often more than a w at once. Then the stored
> procedure will be deleted and I will see an entry in the event log for eve
ry
> time the agent calls it that the stored proc is missing.
> It is always the same stored proc. There is another stored proc that also
> runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find this
> out.
> Thanks!
> Ray Reavis
>
>|||Ray Reavis wrote:
> Hi,
> I have an issue with a stored procedure that is disappearing
> periodically. It is called from the Agent every minute, and runs just
> fine for long periods of a time, often more than a w at once.
> Then the stored procedure will be deleted and I will see an entry in
> the event log for every time the agent calls it that the stored proc
> is missing.
> It is always the same stored proc. There is another stored proc that
> also runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find
> this out.
> Thanks!
> Ray Reavis
Some process is obviously dropping the procedure. What user accounts
have rights on the database to drop the procedure? I wouldn't think too
many processes have admin rights or DROP PROC rights on the production
server.
David Gugick
Imceda Software
www.imceda.com|||Yeah, I agree with David. Use Profiler to find the statement dropping
the SP. You may take the stored procedure "create date" as a hint if
the sp is being dropped and recreated.
Aramid
On Wed, 6 Apr 2005 21:08:37 -0400, "David Gugick"
<davidg-nospam@.imceda.com> wrote:

>Ray Reavis wrote:
>Some process is obviously dropping the procedure. What user accounts
>have rights on the database to drop the procedure? I wouldn't think too
>many processes have admin rights or DROP PROC rights on the production
>server.|||David,
Thanks for the tip. I'm not a dba so some of these things don't come
naturally to me. I'm using the profiler to run a trace on all object
deletions, so hopefully that will turn something up.
As far as permissions are concerned, I don't see a place in SQL2000 where it
specifically shows DROP PROC rights. I looked on the permissions of the
database itself and saw Create Proc rights, but couldn't find drop proc
rights. I'm not sure how much that would help anyway since my company
doesn't have control over this server. We're using windows authentication
rather than SQL Server accounts so I presume administrators on that server
will have those permissions.
Thanks,
Ray
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u#peO5wOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Some process is obviously dropping the procedure. What user accounts
> have rights on the database to drop the procedure? I wouldn't think too
> many processes have admin rights or DROP PROC rights on the production
> server.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Ray Reavis wrote:
> David,
> Thanks for the tip. I'm not a dba so some of these things don't come
> naturally to me. I'm using the profiler to run a trace on all object
> deletions, so hopefully that will turn something up.
> As far as permissions are concerned, I don't see a place in SQL2000
> where it specifically shows DROP PROC rights. I looked on the
> permissions of the database itself and saw Create Proc rights, but
> couldn't find drop proc rights. I'm not sure how much that would
> help anyway since my company doesn't have control over this server.
> We're using windows authentication rather than SQL Server accounts so
> I presume administrators on that server will have those permissions.
Create Procedure rights should do it. As far as permissions, they are on
the SQL side, even with Windows Authentication.
This will show you everyone (excluding administrators and other
fixed-server roles who may have inherent rights) who have explicit
rights to create procs.
select user_name(uid) as "Has Create Proc Rights", * from
dbo.sysprotects
where action = 222 -- create proc
and protecttype != 206 -- Deny
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#YL3RW4OFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ray Reavis wrote:
> Create Procedure rights should do it. As far as permissions, they are on
> the SQL side, even with Windows Authentication.
> This will show you everyone (excluding administrators and other
> fixed-server roles who may have inherent rights) who have explicit
> rights to create procs.
> select user_name(uid) as "Has Create Proc Rights", * from
> dbo.sysprotects
> where action = 222 -- create proc
> and protecttype != 206 -- Deny
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks! I've saved that query in my bag-o-tricks folder. It didn't return
any rows, which is not a surprise. I suspected it was all built-in accounts
that were doing the dirty deed. So far it's been two days since the last
missing sp. I'll be satisfied if it stops but it will kill me to not know
what is going on.
We have the same software running at a lot of businesses nationwide with no
problems such as this -- only this one customer has this issue. It makes us
look bad if we can't prove what is going on even when we're pretty certain
it's not our code causing the problem.
-- Ray