Execute Permission for Database Role

There are about 300 stored procedures and we are trying to separate EXECUTE
permission for Readers and Writers. Here is the scenario:
1) Two Domain groups created named SQLWriters and SQLReaders consisting of
respective users.
2) Two SQL logins created named SQLWriters and SQLReaders using Domain
groups SQLWriters and SQLReaders respectively. SQLWriters are in
db_datareader and db_datawriter roles and SQLReaders are in db_datareader
role only.
3) Two database roles created (a) db_executor for SQLWriters (b)
db_executor_reader for SQLReaders.
(4) db_executor has execute permission on all the stored procedures that
have Select Statements and Insert, Update, Delete Statements.
(5) db_executor_reader has execute permission on the stored procedures that
only have Select statement in the stored procedure.
When NT domain users from SQLReaders group are executing stored procedure
with Insert statement in it where their role (db_executor_reader) does not
have execute permission, it is still getting executed and doing the insert
and update. Any idea what else may be required here. Thanks.I had to explicitly dely execute premission on the SP. For some reason it
gives execute by default to all the stored procedures to all users. I can't
find documentation on why it is doing that. Will investigate more maybe one
of the MVPs can shed some light on this.
But you can check the effictive premissions to check what each Group has
access to.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fraz" wrote:

> There are about 300 stored procedures and we are trying to separate EXECUT
E
> permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures tha
t
> only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.|||Fraz (Fraz@.discussions.microsoft.com) writes:
> There are about 300 stored procedures and we are trying to separate
> EXECUTE permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures
> that only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.
Since I don't see your database, or know which version of SQL Server you
have, it's sort of difficult to say what is wrong. But I would suspect
that you at some earlier point granted execute rights to public. You can
use sp_helprotect to examine this. It could also be that users are members
of other roles and get permission this way.
To test that you have the actual setup correct, create an empty database
and set up users, procedure and permissions, and test that that works.
Then you can examine what is wrong in the target database.
In the end, DENY as Mohit suggested may be the best way, as it also
protects you against future accidents.
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