How Do I give EXECUTE Permissions on Stored Procedures?
This is a discussion on How Do I give EXECUTE Permissions on Stored Procedures? within the SQL Server forums, part of the Microsoft SQL Server category; --> Hey guys, I'm pretty new to SQL configuration, and I need to give EXECUTE persmissions for one of the ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-01-2008, 03:41 PM
| ||||
| ||||
 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!      |
|
03-01-2008, 03:41 PM
| |||
| |||
| 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 |
|
03-01-2008, 03:41 PM
| |||
| |||
| 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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
03-01-2008, 03:41 PM
| |||
| |||
| 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 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/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 |
|
03-01-2008, 03:41 PM
| |||
| |||
| 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/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
03-01-2008, 03:42 PM
| |||
| |||
| 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 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/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') |
|
03-01-2008, 03:42 PM
| |||
| |||
| 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. |
|
03-01-2008, 03:42 PM
| ||||
| ||||
| 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 |
Linear Mode
