?
Solved

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

Posted on 2010-11-19
5
Medium Priority
?
1,305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
pbarry1 earned 2000 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 2000 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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

777 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