Grant execute on stored procedures.

sandiyan

unread,

Mar 14, 2002, 10:50:40 AM3/14/02

to

I have following scenaio;

1. A database role(roleA) with SELECT permission to a table(tableA).
2. I have granted EXECUTE permission to a storedprocedure(which
UPDATEs and INSERTs to tableA) for the above role(roleA)

If users who belong to roleA try to execute this stored procedure, can
they successfully UPDATE/INSERT tableA?

I think, as roleA only has SELECT permission, running the stored
procedure will fail to UPDATE/INSERT ?

Thanks,
Sandiyan

Narayana Vyas Kondreddi

unread,

Mar 14, 2002, 11:02:35 AM3/14/02

to

No, when the user runs this stored procedure, it will succeed. You could
have tried it on your own, it's easy to setup :-)
--
HTH,
Vyas, MVP (SQL Server)
Check out my SQL Server website @
http://vyaskn.tripod.com/

"sandiyan" <sand...@yahoo.co.uk> wrote in message
news:69e9c64b.02031...@posting.google.com...

Narayana Vyas Kondreddi

unread,

Mar 14, 2002, 11:40:22 AM3/14/02

to

Sorry, I forgot to talk about ownership chains (but Wayne did). I always
have all my database objects owned by dbo, so that slipped my mind.

--
HTH,
Vyas, MVP (SQL Server)
Check out my SQL Server website @
http://vyaskn.tripod.com/

"Narayana Vyas Kondreddi" <answ...@hotmail.com> wrote in message
news:#V#lYF3yBHA.2412@tkmsftngp07...

Eric Sabine

unread,

Mar 14, 2002, 3:30:54 PM3/14/02

to

sand...@yahoo.co.uk (sandiyan) wrote in message news:<69e9c64b.02031...@posting.google.com>...

Sandiyan,
They will successfully be able to run the stored procedure. It is
better (if pratical for your situation) to deny all users to tables
and control their access via stored procedures. I agree this can be
difficult to think about, but it can allow you to control what they
do, and prevent foolish mistakes like updating an entire table AND
forgetting to wrap your statments in a transaction.

SQL Server will ignore the user's rights (or lack therof) to the table
if you have given them EXEC permissions to an object which alters that
same table. This is predicated on the owner of the SP and the owner
of the table being THE SAME. Perhaps I should have said that
earlier.. If Joe owns tableA and SP_A, the update will proceed. If
not, then the table's specific permissisions are evaluated for that
user. IMO, have DBO own everything. I'm sure there are valid reasons
to the contrary, but in my experience, I've not had a reason to.

hth,
Eric

sandiyan

unread,

Mar 15, 2002, 4:29:50 AM3/15/02

to

Thanks a lot. Thats cleared now.(I even checked it out myself yesterday...)

Roshmi Choudhury

unread,

Mar 15, 2002, 7:10:37 AM3/15/02

to

No, they cannot. Grant exec on the stored procedure to public. Grant
SELECT, UPDATE, INSERT on table A to role A. Then Role A can get results
from the stored procedure which updates and inserts on tableA if he logs
in as roleA.

--
Roshmi Choudhury

Posted via dBforums
http://dbforums.com

Roshmi Choudhury

unread,

Mar 15, 2002, 7:10:37 AM3/15/02

to

No, they cannot. Grant exec on the stored procedure to public. Grant

SELECT, UPDATE, INSERT on table A to role A. Then Role A can get results

from the stored procedure which updates and inserts on tableA.

--
Roshmi Choudhury