Re: Execute Permission for Database Role

Fraz (Fraz@xxxxxxxxxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.