June 19, 2002 at 2:07 pm
If I grant EXECUTE permission on a stored procedure for a user, will everything in that stored procedure run when the user runs it?
ex. If the stored proc creates tables, creates logins, etc.
Will the proc. fail or complete?
June 19, 2002 at 3:00 pm
I believe the proc will fail unless the user has permission to do whatever is called within the procedure. If you create a second procedure that calls your first procedure and grant the user execute permissions to both procedures then you can work around just about any permissions issue in the first procedure.
The only drawback is if the execution plan has not been created then the permissions will be checked, so you have to run it with an account that has access first.
I believe this is called stupid security, a.k.a. Microsoft Security... 🙂
Give it a try (I could be wrong),
DanW
June 19, 2002 at 5:20 pm
For things like create login there is a specific level a use has to be at and it is checked before run. I don not believe you can bypass this with a stored procedure even using dynamic sql.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 8:23 pm
any information on this in BOL or anywhere else? I can't find any.
June 20, 2002 at 5:45 am
For logins look at sp_addlogin in BOL. Then GRANT, REVOKE, DENY for tables, views, procedures and many other pieces. Look at the permissions sections of each. I test sp_addlogin and it will not work under any bypass attempt.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/20/2002 05:47:19 AM
June 20, 2002 at 8:27 am
I believe that certain built in system stored procs have extra permission checks, like xp_sendmail or sp_addlogin. But the SQL Engine itself is not checking the permission.
June 20, 2002 at 9:49 am
In general, if you grant permission to a stored procedure, and the owner of the stored procedure also owns underlying objects, it will run.
In the case of create table, the user would have to have the rights to create a table (i.e. insert into system tables), so this would be checked separately.
What exactly are you trying to accomplish? If we knew that, we might better be able to help.
Steve Jones
June 20, 2002 at 5:28 pm
Also of note is if you do anything in Stored Procedures with sp_executesql or EXEC('sqlstring') they execute out of the scope of the SP owner and rights and under those of the logged in user. But Steve is right, what is it you need to do?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply