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.

Database user permission

Add a new user to a database and assign permission only to 2 tables in this database for select/update/insert/delete.

the user role is Public.

But when you login in this database with SQL Query Analyzer using this user account, in Object Browser (left side), this user can see all info same as dbo, such as table name, column, data types,... although this user can not access the data for others tables.

How to limit the this user view in Object Browser and only see the tables that the user have proper permission on?

With SQL Server 2000, you could NOT limit the user. They can 'see' all objects in the database.

With SQL Server 2005, the user can only see the objects in the schema(s) they have permissions for.

Are you using SQL 2000?

|||Thanks for reply. Yes, I use SQL Server 2000|||

Unfortunately, with SQL 2000, all users can see all database objects with the client tools.

There is nothing you can do about it -except upgrade to SQL 2005. With SQL 2005, you can keep things private and undisclosed.