Setting Execute Permissions on Stored Procedures

See Also

You can set execute permissions for a stored procedure that you own to allow access to the stored procedure by specific users or groups. In many databases, if you are not the database owner, then you must explicitly grant permissions for your stored procedure to other users. You can grant permissions to all users, or to just a single user or group. The syntax for both is similar.

Note When you create a stored procedure in an Oracle database, you must explicitly grant permissions for your stored procedure to other users. If you do not grant permissions to a user, that user must have the EXECUTE ANY PROCEDURE system privilege in order to execute the procedure.

To grant permissions to all users

Create a new SQL script file. For details, see Creating SQL Scripts.
Enter the following SQL statement in the script file, replacing sp_name with the name of your stored procedure:
```
Grant Execute On sp_name To Public
```

To grant permissions to a user or group

Create a new SQL script file. For details, see Creating SQL Scripts.
Enter the following SQL statement in the script file, replacing sp_name with the name of your stored procedure, and replacing user with the name of the user or group to grant permissions to:
```
Grant Execute On sp_name To user
```
For example, if you want to grant permissions on a stored procedure to a group named Managers, you can create an SQL script file and add the following SQL statement:
```
Grant Execute On MyPublicProcedure To Managers
```

Note Microsoft® Visual Basic™ does not support using SQL scripts to change permissions on a stored procedure. You'll need to use a utility such as SQL Enterprise Manager.