Solved

How to grant user permission to execute all stored procedures in SQL 2005

Posted on 2010-11-19
5
1,298 Views
Last Modified: 2012-05-10
Hello,

Is there a way in SQL 2005 to grant database user with permission to execute all stored procedures in the same database including store procs that will be created in the future?
I guess, I am looking for some kind of role based permission.
I understadn SysAdmin or db_owner roles are available.
However, I want to avoid over-granting permissions than required.

Thank you for your help,
0
Comment
Question by:JOSHUABT
  • 3
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
pbarry1 earned 500 total points
ID: 34178007
Hi,

you need to grant EXECUTE permission at the database level.  So, all you need to do is:

GRANT EXECUTE TO UserName;

where UserName is the database user who need to execute any existing and future stored procedures.
0
 
LVL 4

Assisted Solution

by:pbarry1
pbarry1 earned 500 total points
ID: 34178031
P.S. Just make sure your in the right database when issuing the command.

Or if you want, you can do this in the SQL Management Studio:
- right-click on the database name
  - Properties
  - Permissions
  - select the user you want
  - in the Permissions list, click on the GRANT option for EXECUTE
  - click OK and Voilà!


0
 

Author Comment

by:JOSHUABT
ID: 34178062
pbarry1,

Thank you for your response.
What about views in the same database?
How can I ensure the user can open any view in the same database?
0
 
LVL 4

Assisted Solution

by:pbarry1
pbarry1 earned 500 total points
ID: 34178119
Unfortunately, there is no easy way to grant SELECT permission only on Views (current and future).  But if you don't mind also giving SELECT permission on all tables and all views, you could use the "db_datareader" role.

If you can't give SELECT permissions on all tables, here's what you could do:
-  Create a new role (ex: View Reader)
-  Manually GRANT SELECT on all existing view to this new role  (ex: View Reader)
- Create a Database scope trigger on the CREATE VIEW statement that GRANTs the SELECT permission for that new role (ex: View Reader).
0
 

Author Closing Comment

by:JOSHUABT
ID: 34178179
pbarry1
Thank you for your help.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SCOM to SQL port 14xx failed? 1 32
SQL Server Resume 5 46
Connect remotely to SQL Server DB without TCP/IP enabled? 2 37
sql server major issue  need help 2 46
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question