Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
Sam
Think I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam