By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

revoke permissions TO stored procedure

P: n/a
I have written an stored proc that reads from a text file and executes
the script as dynamic sql.

If the text file contains malicious code,I want to be able to detect it
and prevent the stored procedure from executing.

I've tried revoking delete,insert,update rights all tables in the
database to the user .
I then granted execute rights to the stored procedure for the same
user. But the user is still able to delete a record from the table by
executing the stored procedure.

Is there any means to I revoke,insert,delete ,update rights to a stored
proc?

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Once a SP is called, it has already been compiled. Changes to it while
it is running has no effect.

Inside the stored procedure, you can write conditional logic to abort
if necessary (using the RETURN or RAISEERROR statements).

However, a patient hacker can try hundreds of ways to bypass whatever
detection logic you write. If this a customer requirement, quote him
the AOL commercial that "he's just asking for his hard drive to make
noises like a yeti..."

Jul 23 '05 #2

P: n/a
(te****@yahoo.com) writes:
I have written an stored proc that reads from a text file and executes
the script as dynamic sql.

If the text file contains malicious code,I want to be able to detect it
and prevent the stored procedure from executing.

I've tried revoking delete,insert,update rights all tables in the
database to the user .
I then granted execute rights to the stored procedure for the same
user. But the user is still able to delete a record from the table by
executing the stored procedure.

Is there any means to I revoke,insert,delete ,update rights to a stored
proc?


It's not wholly clear what you are trying to accomplish, but the answer
to the last question is no. You can revoke rights for the procedure
owner.

But for dynamic SQL, it's the rights of the user that applies, so meddling
with the procedure owner won't help. Just grant the user the rights
he needs, but not more.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.