How Do I give EXECUTE Permissions on Stored Procedures?

20/09/2007, 16h14

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 point me in the right direction? Thanks!

20/09/2007, 21h39

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 point me in the right direction? Thanks!
>

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

--sharif

20/09/2007, 23h11

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 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

20/09/2007, 23h26

On Sep 20, 4:11 pm, Erland Sommarskog <esq...@sommarskog.se> 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 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/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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 - About and it shows:

Microsoft SQL Server Management Studio Express Version 9.00.2047.00

20/09/2007, 23h40

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 - 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

01/10/2007, 17h06

On Sep 20, 10:26 pm, "alvinstraigh...@hotmail.com"
<alvinstraigh...@hotmail.com> wrote:
> On Sep 20, 4:11 pm, Erland Sommarskog <esq...@sommarskog.se> 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 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/downloads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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 - 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')

01/10/2007, 18h59

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.

01/10/2007, 23h40

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

20/09/2007, 16h14	#1
alvinstraight38@hotmail.com Messages: n/a Hébergeur:	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 point me in the right direction? Thanks!

20/09/2007, 21h39	#2
Sharif Islam Messages: n/a Hébergeur:	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 point me in the right direction? Thanks! > if you right click on the SP and click properties, that should bring up the options. --sharif

20/09/2007, 23h11	#3
Erland Sommarskog Messages: n/a Hébergeur:	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 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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx

20/09/2007, 23h26	#4
alvinstraight38@hotmail.com Messages: n/a Hébergeur:	Re: How Do I give EXECUTE Permissions on Stored Procedures? On Sep 20, 4:11 pm, Erland Sommarskog <esq...@sommarskog.se> 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 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/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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 - About and it shows: Microsoft SQL Server Management Studio Express Version 9.00.2047.00

20/09/2007, 23h40	#5
Erland Sommarskog Messages: n/a Hébergeur:	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 - 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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx

01/10/2007, 17h06	#6
abu hisham Messages: n/a Hébergeur:	Re: How Do I give EXECUTE Permissions on Stored Procedures? On Sep 20, 10:26 pm, "alvinstraigh...@hotmail.com" <alvinstraigh...@hotmail.com> wrote: > On Sep 20, 4:11 pm, Erland Sommarskog <esq...@sommarskog.se> 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 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/downloads/books... > > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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 - 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')

01/10/2007, 18h59	#7
Ed Murphy Messages: n/a Hébergeur:	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.

01/10/2007, 23h40	#8
Erland Sommarskog Messages: n/a Hébergeur:	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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email