permissions needed for executing stored procedures

We want to create a SQL login say user1 and the only privilges we want to
grant it is to be able to execute stored procedures in that database.
So is it just good enough to just do the following ?
Grant exec on sprocx to user1
Does this take care of conditions that include DMLs ( insert,updates,selects
and deletes) that are within the stored procedure ? What about creating temp
tables,etc. ?
ThanksYes, doing that is possible, if the owner of the stored procedure (other
than user1 in your example) is also the owner of the tables to do the INSERT,
DELETE, etc.
Take a look at Ownership Chains in BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs ( insert,updates,selects
> and deletes) that are within the stored procedure ? What about creating temp
> tables,etc. ?
> Thanks
>|||Hassan (hassan@.test.com) writes:
> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs (
> insert,updates,selects and deletes) that are within the stored procedure?
Yes, provided that the tables and the procedures have the same owner.
And provided that you don't engage in dynamic SQL.
Also beware that if your stored procedures goes beyond INSERT, UPDATE,
DELETE and SELECT, granting execution rights to the procedure is not
sufficient. However, SQL 2005 offers mechanisms that permit you to address
this. I have an article on by web site that discusses this in detail:
http://www.sommarskog.se/grantperm.html
> What about creating temp tables,etc. ?
Any user have the permission to create temp tables, stored procedures or
not.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx