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:
>
>