EXEC Permissions

I have a user in DB that has db_datareader and db_datawriter permissions. Now
I want to assign EXEC permission to all the stored procedures. How can I
achieve this through database standard roles. My objective is to avoid giving
permission on each stored procedure the EXEC permission.
Any clean way of creating a special database role with EXEC, db_datareader &
db_datawriter and map it to a login?
Thanks in advance
Hi David,
Here are a few ideas...
Best practice, for me, is to have a security script that controls the
security of all objects in the database. Every time I write a stored
procedure, the process is to adjust the security procedure prior to
completion. This way you can periodically run the the security procedure to
bring the database(s) back into compliance. This is more time consuming.
Grant 'public' permission to execute. Since all users are members of
public, any existing or new users will have the ability to execute said
procedure. I have a script that I wrote a while back that grants 'public'
execute to all procedures. If I can locate it, I will forward.
If you want a simple way out. You can add the 'user' to the db_owner role.
This is not a safe option considering how costly db_owner access can be (i.e.
dropping tables, etc).
HTH,
Adam
"David" wrote:

> I have a user in DB that has db_datareader and db_datawriter permissions. Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader &
> db_datawriter and map it to a login?
> Thanks in advance
|||David
Once you generate dynamic EXEC script to grant an EXECUTE permissions you
can save it an use again and again.
What is if tomorrow your boss tells you do DENY EXECUTE permission on some
stored procedures?
"David" <David@.discussions.microsoft.com> wrote in message
news:95A66800-22CA-4410-ABD7-03D7DD7A98A4@.microsoft.com...
>I have a user in DB that has db_datareader and db_datawriter permissions.
>Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid
> giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
> &
> db_datawriter and map it to a login?
> Thanks in advance
|||To grant exec permission to public is risky.
Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
can specify the user to whom you want to grant exec to all.
Regards
Amish Shah
|||amish
I did not meant to grant it to the public, I hope OP understood me.
"amish" <shahamishm@.gmail.com> wrote in message
news:1138700452.078652.59830@.g14g2000cwa.googlegro ups.com...
> To grant exec permission to public is risky.
> Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
> can specify the user to whom you want to grant exec to all.
>
> Regards
> Amish Shah
>
|||Uri
I replied for the answer from Goose. He replied to give permission to
public , which I feel risky.
Please dont missunderstand.
Regards
Amish Shah