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. ?
Thanks
Yes, 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

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

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,selec
ts
> and deletes) that are within the stored procedure ? What about creating te
mp
> 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Permission ?

Hi,
I have a user call "user1" and he is in the db_denydatawriter role.
I have a stored procedure called "Testing1" like the following
Create Procedure Testing1
as
Update customers set country = 'Mexicos' where country = 'Mexico'
Then I grant him permission
Grant Exec on Testing1 to user1
I am supposed the user1 should not be able to update the table "customers"
However, i am wrong. the db_denydatawriter does not overwrite the Exec
permission. It doesn't make sense to me and I though db_denydatawriter woul
d
deny user altering data in any situation unless he is in Symin role.
Can someone explain to me why user1 can still update data in the table?
Thanks
EdEd
Unless you don't have dynamic SQL within a stored procedure ,the user will
be able to execute this SP.
From Bol
"A granted permission removes the denied or revoked permission at the level
granted (user, group, or role). "
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:AEA7CBAE-66DC-40B3-8C80-D9469EB39A3E@.microsoft.com...
> Hi,
> I have a user call "user1" and he is in the db_denydatawriter role.
> I have a stored procedure called "Testing1" like the following
> Create Procedure Testing1
> as
> Update customers set country = 'Mexicos' where country = 'Mexico'
> Then I grant him permission
> Grant Exec on Testing1 to user1
> I am supposed the user1 should not be able to update the table "customers"
> However, i am wrong. the db_denydatawriter does not overwrite the Exec
> permission. It doesn't make sense to me and I though db_denydatawriter
> would
> deny user altering data in any situation unless he is in Symin role.
> Can someone explain to me why user1 can still update data in the table?
> Thanks
> Ed|||Thanks for the answer,
but from what I know,
Deny overwrites grant and grant overwrites revoke...
is that true Exec overwrites Deny?
"Uri Dimant" wrote:

> Ed
> Unless you don't have dynamic SQL within a stored procedure ,the user wil
l
> be able to execute this SP.
> From Bol
> "A granted permission removes the denied or revoked permission at the leve
l
> granted (user, group, or role). "
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:AEA7CBAE-66DC-40B3-8C80-D9469EB39A3E@.microsoft.com...
>
>|||To add to Uri's response, be mindful of ownership chains. When the
ownership chain is unbroken, permissions on indirectly referenced objects
are not checked. Ownership chains are especially handy when you want to
allow access only through stored procedures and views but you need to be
aware of the implications.
See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:AEA7CBAE-66DC-40B3-8C80-D9469EB39A3E@.microsoft.com...
> Hi,
> I have a user call "user1" and he is in the db_denydatawriter role.
> I have a stored procedure called "Testing1" like the following
> Create Procedure Testing1
> as
> Update customers set country = 'Mexicos' where country = 'Mexico'
> Then I grant him permission
> Grant Exec on Testing1 to user1
> I am supposed the user1 should not be able to update the table "customers"
> However, i am wrong. the db_denydatawriter does not overwrite the Exec
> permission. It doesn't make sense to me and I though db_denydatawriter
> would
> deny user altering data in any situation unless he is in Symin role.
> Can someone explain to me why user1 can still update data in the table?
> Thanks
> Ed|||Hi Ed
It doesn't matter whether the user1 has deny, revoke or grant on the object
inside the stored procedure. If the owner of the object is the same as the
owner of the procedure, then permissions for user1 ARE NOT CHECKED AT ALL.
So it is not a question of overwriting, or overriding.
The exception as Uri mentioned, is if you have dynamic SQL and then user1
must have permission on the object, and the deny would keep him from
executing the statement in the procedure.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:97F3E530-BAE8-44B5-87E9-978EE6081602@.microsoft.com...
> Thanks for the answer,
> but from what I know,
> Deny overwrites grant and grant overwrites revoke...
> is that true Exec overwrites Deny?
> "Uri Dimant" wrote:
>
>