grant permissions to all stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

grant permissions to all stored procedures

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2013-02-21 : 09:16:05

How do I grant permissions to a user to alter,write and execute ALL stored procedures in my database?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-21 : 22:39:53

use GRANT t-sql statement

http://msdn.microsoft.com/en-in/library/ms187965.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-02-22 : 07:18:21

More specifically,
http://msdn.microsoft.com/en-in/library/ms188371.aspx
All the way at the bottom of the page:

 apparently ON is optional

Example:


--To Grant execute rights to all stored procedure to a role

GRANT EXECUTE TO db_executor

where db_executor is a role created by:

--Create a new role for executing stored procedures
CREATE ROLE db_executor

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-02-22 : 15:53:58

As noted:


USE <db_name_to_be_affected>
GRANT EXECUTE TO <user_name>

Since there is no "ON <object>", this grant applies to the entire database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources