dbTalk Databases Forums  

How Do I give EXECUTE Permissions on Stored Procedures?

Go Back dbTalk Databases Forums Databases comp.databases.ms-sqlserver How Do I give EXECUTE Permissions on Stored Procedures?

comp.databases.ms-sqlserver comp.databases.ms-sqlserver


Discuss How Do I give EXECUTE Permissions on Stored Procedures? in the comp.databases.ms-sqlserver forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
alvinstraight38@hotmail.com
 
Posts: n/a

Default How Do I give EXECUTE Permissions on Stored Procedures? - 09-20-2007 , 09:14 AM





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!


Reply With Quote
  #2  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: How Do I give EXECUTE Permissions on Stored Procedures? - 09-20-2007 , 04:11 PM





alvinstraight38 (AT) hotmail (DOT) com (alvinstraight38 (AT) hotmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) 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


Reply With Quote
  #3  
Old   
alvinstraight38@hotmail.com
 
Posts: n/a

Default Re: How Do I give EXECUTE Permissions on Stored Procedures? - 09-20-2007 , 04:26 PM


On Sep 20, 4:11 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Quote:
alvinstraigh... (AT) hotmail (DOT) com (alvinstraigh... (AT) hotmail (DOT) 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... (AT) sommarskog (DOT) 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 Help - About and it shows:

Microsoft SQL Server Management Studio Express Version 9.00.2047.00



Reply With Quote
  #4  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: How Do I give EXECUTE Permissions on Stored Procedures? - 09-20-2007 , 04:40 PM


alvinstraight38 (AT) hotmail (DOT) com (alvinstraight38 (AT) hotmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) 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


Reply With Quote
  #5  
Old   
abu hisham
 
Posts: n/a

Default Re: How Do I give EXECUTE Permissions on Stored Procedures? - 10-01-2007 , 10:06 AM


On Sep 20, 10:26 pm, "alvinstraigh... (AT) hotmail (DOT) com"
<alvinstraigh... (AT) hotmail (DOT) com> wrote:
Quote:
On Sep 20, 4:11 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:





alvinstraigh... (AT) hotmail (DOT) com (alvinstraigh... (AT) hotmail (DOT) 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... (AT) sommarskog (DOT) 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 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')



Reply With Quote
  #6  
Old   
Ed Murphy
 
Posts: n/a

Default Re: How Do I give EXECUTE Permissions on Stored Procedures? - 10-01-2007 , 11:59 AM


abu hisham wrote:

Quote:
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.


Reply With Quote
  #7  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: How Do I give EXECUTE Permissions on Stored Procedures? - 10-01-2007 , 04:40 PM


abu hisham (yjogee (AT) hotmail (DOT) co.uk) writes:
Quote:
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 (AT) sommarskog (DOT) 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


Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top