Solved

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

Posted on 2010-11-19
5
1,256 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
pbarry1
Thank you for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now