How Do I give EXECUTE Permissions on Stored Procedures?

Hey guys,

I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!

Re: How Do I give EXECUTE Permissions on Stored Procedures?

alvinstraight38@hotmail.com wrote:
> Hey guys,
>
> I'm pretty new to SQL configuration, and I need to give EXECUTE
> persmissions for one of the SQL user roles. I am running SQL 2005
> Management Studio Express - free version. I found the list of my
> stored procedures, but I can not locate any permissions screen. Can
> someone help point me in the right direction? Thanks!
>

if you right click on the SP and click properties, that should bring up
the options.

--sharif

Re: How Do I give EXECUTE Permissions on Stored Procedures?

alvinstraight38@hotmail.com (alvinstraight38@hotmail.com) writes:
> I'm pretty new to SQL configuration, and I need to give EXECUTE
> persmissions for one of the SQL user roles. I am running SQL 2005
> Management Studio Express - free version. I found the list of my
> stored procedures, but I can not locate any permissions screen. Can
> someone help point me in the right direction? Thanks!

If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.

Then again, in the long run you are better of using GRANT commands.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: How Do I give EXECUTE Permissions on Stored Procedures?

On Sep 20, 4:11 pm, Erland Sommarskog wrote:
> alvinstraigh...@hotmail.com (alvinstraigh...@hotmail.com) writes:
> > I'm pretty new to SQL configuration, and I need to give EXECUTE
> > persmissions for one of the SQL user roles. I am running SQL 2005
> > Management Studio Express - free version. I found the list of my
> > stored procedures, but I can not locate any permissions screen. Can
> > someone help point me in the right direction? Thanks!
>
> If you want to use the GUI, make sure that you have SP2. I think that
> alternative was missing in RTM and SP1.
>
> Then again, in the long run you are better of using GRANT commands.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx


Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:

Microsoft SQL Server Management Studio Express Version 9.00.2047.00

Re: How Do I give EXECUTE Permissions on Stored Procedures?

alvinstraight38@hotmail.com (alvinstraight38@hotmail.com) writes:
> Ahh, I see now why I was lost. I right click on the SP, and there is
> no option for Properties. Yet, I can set permissions on tables. How
> stupid. You mention SP2. How can I tell which service pack I am
> running? I went to Help - About and it shows:
>
> Microsoft SQL Server Management Studio Express Version 9.00.2047.00

That's SP1.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: How Do I give EXECUTE Permissions on Stored Procedures?

On Sep 20, 10:26 pm, "alvinstraigh...@hotmail.com"
wrote:
> On Sep 20, 4:11 pm, Erland Sommarskog wrote:
>
>
>
>
>
> > alvinstraigh...@hotmail.com (alvinstraigh...@hotmail.com) writes:
> > > I'm pretty new to SQL configuration, and I need to give EXECUTE
> > > persmissions for one of the SQL user roles. I am running SQL 2005
> > > Management Studio Express - free version. I found the list of my
> > > stored procedures, but I can not locate any permissions screen. Can
> > > someone help point me in the right direction? Thanks!
>
> > If you want to use the GUI, make sure that you have SP2. I think that
> > alternative was missing in RTM and SP1.
>
> > Then again, in the long run you are better of using GRANT commands.
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
>
> Ahh, I see now why I was lost. I right click on the SP, and there is
> no option for Properties. Yet, I can set permissions on tables. How
> stupid. You mention SP2. How can I tell which service pack I am
> running? I went to Help - About and it shows:
>
> Microsoft SQL Server Management Studio Express Version 9.00.2047.00- Hide quoted text -
>
> - Show quoted text -

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')

/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

Re: How Do I give EXECUTE Permissions on Stored Procedures?

abu hisham wrote:

> You can grant permissions dynamically in this way to all db objects:
> /* tables and views*/
> select 'Grant select,insert,update,delete on '+name+ ' to USER'
> from sysobjects
> where xtype in ('U','V')
>
> /*Stored procedures*/
> select 'Grant exec on '+name+ ' to USER'
> from sysobjects
> where xtype in ('P')

To clarify, this will not directly grant the permissions, but will
output SQL code that can be copy+pasted into Query Analyzer and
executed to grant the permissions.

Re: How Do I give EXECUTE Permissions on Stored Procedures?

abu hisham (yjogee@hotmail.co.uk) writes:
> You can grant permissions dynamically in this way to all db objects:
> /* tables and views*/
> select 'Grant select,insert,update,delete on '+name+ ' to USER'
> from sysobjects
> where xtype in ('U','V')
>
> /*Stored procedures*/
> select 'Grant exec on '+name+ ' to USER'
> from sysobjects
> where xtype in ('P')

In SQL 2005 this can be achieved with a single statement:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::schema_name TO user

Access granted on schema level are inherited by objects in the schema, which
means that it also applies to future objects.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx