Execute permission

Question

Execute permission

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
June 19, 2002 at 2:07 pm

#64329

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?

Viewing 8 posts - 1 through 7 (of 7 total)

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

danw SSCommitted Points: 1842 More actions · Answer 1

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

Antares686 SSC Guru Points: 125444 More actions · Answer 2

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)

bryan99y SSCarpal Tunnel Points: 4672 More actions · Answer 3

any information on this in BOL or anywhere else? I can't find any.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

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

danw SSCommitted Points: 1842 More actions · Answer 5

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.

Steve Jones - SSC Editor SSC Guru Points: 734416 More actions · Answer 6

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

steve@dkranch.net

Antares686 SSC Guru Points: 125444 More actions · Answer 7

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)