Grant Permissions on Stored Procedure

Published on Monday, June 13, 2011

After you finished your Stored Procedures, you have to know you should grant permissions to database users, otherwise, when you deploy your application, you might find your stored procedures can not work due to permissions issues.

How to grant permissions on stored procedures?

1: Visual Studio 2005+:

SQL Server 2005 and newer versions can make the EXECUTE permission grantable at the database scope.  We can use scripts to GRANT execute permissions on all existing stored procedures, scalar functions and all subsequently created ones.

/* Create a new role */
CREATE ROLE  my_role

/* set permission to the new role */
GRANT EXECUTE TO my_role

Or, use script as below:

GRANT EXECUTE ON mySP TO x

x=

1: SQL User
2: Role
3: Admin Group/Account

There are more samples, please read MSDN.

2: Via SQL Server Management Studio:

If you just want grant permissions for a single Stored Procedure or several Stored Procedures, you can directly choose SQL Server Management Studio.

Right click your stored procedure and select Properties

grantSP00

Because we do not have a SQL Server Management sample, so the next steps we just followed Microsoft official site:

To grant permissions to a user, database role, or application role, click Add.

In Select Users or Roles, click Object Types to add or clear the users and roles you want.

In the Explicit Permissions grid, select the permissions to grant to the specified user or role.