datareader doing updates

We created a Role (Alpha) and made it a member of the datareader Role. Our
hope was that members of this role would only be able to preform data reads.
On a test we granted Alpha exec rights to an update Stored Procedure. The
members of Alpha are now able to run the Stored Procedure and do updates eve
n
thought they are NOT members of datawriter. Further testing showed the same
for insert and delete stored procedures.
Does this sound right?
Thanks, Randy"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:0EA38A7F-70DF-44D3-91C0-6555FB42A4FA@.microsoft.com...
> We created a Role (Alpha) and made it a member of the datareader Role.
> Our
> hope was that members of this role would only be able to preform data
> reads.
> On a test we granted Alpha exec rights to an update Stored Procedure. The
> members of Alpha are now able to run the Stored Procedure and do updates
> even
> thought they are NOT members of datawriter. Further testing showed the
> same
> for insert and delete stored procedures.
> Does this sound right?
>
Yes. Look up "ownership chains" in BOL. If the user can run the
procedure, then permission checks on all objects owned by the owner of the
stored procedure are supressed.
David

Databse role to Create Stored Procedures

Hi,
With SQL2005, I'd like to create a database role whose members could
Create/Drop stored procedures (even new ones), Select/Insert/Delete/Update
from any table of the Database, but couldn't modifiy the tables' structure.
Could someone suggest me a script?
Thanks for your help.
JN.Hi,
Assign below roles to user to read and write on tables
DB_DATAREADER
DB_DATAWRITER
This will allow the user to create procedure
GRANT CREATE PROCEDURE TO <UserName>
The createor can drop the procedure
Thanks
Hari
SQL Server MVP
"Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote in
message news:203A9A00-5976-4B45-82FE-B9E31B32AEDB@.microsoft.com...
> Hi,
> With SQL2005, I'd like to create a database role whose members could
> Create/Drop stored procedures (even new ones), Select/Insert/Delete/Update
> from any table of the Database, but couldn't modifiy the tables'
> structure.
> Could someone suggest me a script?
> Thanks for your help.
> JN.