Grant Execute to All Stored Procedures

GRANT EXECUTE to all stored procedures

SQL Server 2008 and Above:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

For just a user (not a role):

USE [DBName]
GO
GRANT EXECUTE TO [user]

Grant execute on all stored procedures using SSMS

Looks like you can do this, though not as "simple" as you would expect.

I would, personally create a database role to do this. Go to your database in the Object explorer, and expand the Security Folder. Then right click Roles and Select New -> New Database Role.

Give the Role a name (I'll use db_executor) on the General Pane and then go to the Securables Pane. Click Search... at the top and select the radio option Specific Objects... and click OK. Click Object Types... and then tick Databases and then OK. Now click Browse... and tick the database you are adding the role to, then click OK and then OK.

In the datagrid at the bottom locate the Permission Execute, and tick the box in the column Grant. Then OK. This will run the below SQL on your instance:

USE [YourDatabase]
GO
CREATE ROLE [db_executor]
GO
use [YourDatabase]
GO
GRANT EXECUTE To [db_executor]
GO

Yes, Microsoft really is inconsistent with the casing of USE for that statement, and it omits the ; in it's commands.

Now you have created the role, locate the user you want to give access to in the object explorer in the Users folder. Right Click them and select Properties. Go to the Membership Pane and tick the box next to db_executor. Then click OK. This will run the below SQL on your instance:

USE [YourDatabase]
GO
ALTER ROLE [db_executor] ADD MEMBER [YourUser]
GO

Of course, why you wouldn't just run the 2 above commands, which is far quicker, I do not know.

Grant execute permission for a user on all stored procedures in database?

Create a role add this role to users, and then you can grant execute to all the routines in one shot to this role.

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

EDIT
This works in SQL Server 2005, I'm not sure about backward compatibility of this feature, I'm sure anything later than 2005 should be fine.

GRANT EXECUTE permission to ALL STORED PROCEDURES in snowflake

A few things that might help you.

1. I'd recommend fully-qualifying that table name in the SELECT statement, this way whenever the stored procedure is called, the "context" of the user's session will not matter, as long as the session's current role has access to the table and schema you should be good.
   
   A fully-qualified table has the form: database_name.schema_name.object_name

   Example: hr_prod_db.hr_schema.employees

You can read more about object name resolution at this link: https://docs.snowflake.net/manuals/sql-reference/name-resolution.html

2. I'd recommend you spend a little bit of time reading about "Session State", at the following link, as this link discuses "Caller's rights" vs. "Owner's rights" stored procedures. If your procedure is only going to be called from a session with the role of the stored procedure owner, this shouldn't matter, but if you are granting USAGE on the procedure to another role, it's very important to understand this and set this properly.
   https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#session-state

3. If your procedure is going to be called by a session that has it's current role set to a different role than the "owning role, you'll need to ensure the proper grants on the procedure (and schema + database) to the role that is going to be executing the procedure. This is all outlined here in this document quite thoroughly, pay particular attention to this as in your example code you have a table or view name that is different than what your error message is reporting, so perhaps stproc_test_employees is a view on top of SYEMPLOYEES:
   https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#access-control-privileges
   Note: When/if you grant usage on this procedure to another role, you will need to include the datatype of the arguments, example:

   GRANT USAGE ON database_name.schema_name.get_column_scale(float) TO ROLE other_role_name_here;

I hope this helps...Rich

Granting execute permission on all stored procedures in a certain database

Generate the statements, then copy them and paste into query window to run them

select 'grant execute on ' + 
    QuoteName(specific_schema) + '.' +
    QuoteName(specific_name) + ' to someone'
from information_schema.routines
where routine_type='PROCEDURE'

How do you grant execute permission for a single stored procedure?

GRANT EXECUTE ON dbo.procname TO username;

How to grant a user access to all stored procedures on mysql?

Use this instead, it will work:

GRANT EXECUTE ON mydb.* TO 'my_user'@'%';

how to give the user to execute all procedures under the db (regular methods doesn't works)?

I created below Procedure with root user

 DELIMITER //
 
 CREATE PROCEDURE GetAllTitles() BEGIN  SELECT *  FROM titles; END //
 
 DELIMITER ;

Then gave the proper permission as you mentioned

 CREATE USER 'my_user'@'%' IDENTIFIED BY 'Not_so_secure!1'; GRANT
 CREATE, DROP, DELETE, INSERT, SELECT, UPDATE ON employees.* TO
 'my_user'@'%'; GRANT EXECUTE ON employees.* TO 'my_user'@'%'; FLUSH
 PRIVILEGES;

Then connected with that user my_user

       mysql> select user();
      +-------------------+ 
      | user()            |
      +-------------------+ 
      | my_user@localhost |
      +-------------------+ 
      1 row in set (0.00 sec)
 

Then check the if I can see the procedure with below command connected to mysql using my_user

 mysql> SHOW PROCEDURE STATUS \G;
 *************************** 1. row ***************************
                   Db: employees
                 Name: GetAllTitles
                 Type: PROCEDURE
              Definer: root@localhost
             Modified: 2021-05-17 12:55:05
             Created: 2021-05-17 12:55:05
       Security_type: DEFINER
              Comment:  character_set_client: latin1 collation_connection: latin1_swedish_ci   Database Collation:
 latin1_swedish_ci 1 row in set (0.00 sec)

Then tried to call it's working

 CALL employees.GetAllTitles();

Make sure you are flushing the privileges

---

Related Topics