Stored Proc Execute Permission being revoked

Question

Stored Proc Execute Permission being revoked

Will Saunders

Newbie

Points: 2
More actions
January 26, 2011 at 10:12 am

#142192

I created a new user and granted that use right to execute only one stored procedure. Everything works fine. For some reason, I'm having to regrant the execute permission to that user every Wednesday. I've opened a ticket with the vendor, but I wanted to see if anyone knows what I can look for myself to pinpoint what is removing the permissions weekly. Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eddie Wuerch SSChampion Points: 12536 More actions · Answer 1

Do you have some sort of weekly deployments or script refreshes? If you drop and re-create a procedure, any explicit permissions granted for that procedure get dropped with the procedure.

Eddie Wuerch
MCM: SQL

Will Saunders Newbie Points: 2 More actions · Answer 2

that is my first suspicion. I've opened a ticket with the vendor to see if they are doing something like that. Is there a way for me to tell if this is happening by looking at something in the db?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Good thought Eddie.. I was also thinking a trace during the time might be ok, especially if it is just focused on permissions changes. I would think that would be pretty light.. Perhaps a DDL trigger on that database.. I don't know about this gent's DB's but my permissions change pretty infrequently..

CEWII

Eddie Wuerch SSChampion Points: 12536 More actions · Answer 4

Will Saunders (1/26/2011)
Is there a way for me to tell if this is happening by looking at something in the db?

There is a create_date column in sys.objects, and there will be a row in there for the procedure.

Eddie Wuerch
MCM: SQL