Database Role that allows execution of stored procedures?

We have a rule for developing database-driven applications that all
interaction with the database must be done through stored procedures i.e.
all selects, inserts, updates etc.
I am looking for simple ways to enforce & support this design principle -
and one would be if I could put the SQL login that the application uses into
a database role(s) that only allowed execution of stored procedure, i.e. no
direct access to tables or views. I know that the long way to do this is to
create my own role and grant it execute rights on each SP and no rights to
tables/views, but I was wondering if there was anything already built into
SQL Server.No built-in role like that in SQL Server 2000. You'll have to create your
own database role, and give it the required permissions. This might help:
http://vyaskn.tripod.com/generate_s...e_sql_tasks.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
into
> a database role(s) that only allowed execution of stored procedure, i.e.
no
> direct access to tables or views. I know that the long way to do this is
to
> create my own role and grant it execute rights on each SP and no rights to
> tables/views, but I was wondering if there was anything already built into
> SQL Server.
>|||This might help as well
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
> We have a rule for developing database-driven applications that all
> interaction with the database must be done through stored procedures i.e.
> all selects, inserts, updates etc.
> I am looking for simple ways to enforce & support this design principle -
> and one would be if I could put the SQL login that the application uses
> into a database role(s) that only allowed execution of stored procedure,
> i.e. no direct access to tables or views. I know that the long way to do
> this is to create my own role and grant it execute rights on each SP and
> no rights to tables/views, but I was wondering if there was anything
> already built into SQL Server.
>|||Thanks, that is useful.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OF8hIIrvEHA.1392@.TK2MSFTNGP14.phx.gbl...
> This might help as well
> Granting execute permissions to all stored procedures in a database
> http://www.sqldbatips.com/showarticle.asp?ID=8
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OHbd5VmvEHA.3624@.TK2MSFTNGP09.phx.gbl...
>