• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1321
  • Last Modified:

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

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
JOSHUABT
Asked:
JOSHUABT
  • 3
  • 2
3 Solutions
 
pbarry1Commented:
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
 
pbarry1Commented:
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
 
JOSHUABTAuthor Commented:
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
 
pbarry1Commented:
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
 
JOSHUABTAuthor Commented:
pbarry1
Thank you for your help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now