How to Give Permission to Execute Stored Procedure in SQL Server?

by

In this SQL Server tutorial, I will show you how to give permission to execute stored procedure in SQL Server.

You will understand why giving execute permission on the stored procedure is necessary with examples. Then, understand the syntax that you can use to give execute permission.

After that, I will demonstrate through query and SSMS (SQL Server Management Studio) to give execute permission on stored procedure. Also, you will learn how to view the permission on the specific stored procedure in your database.

Give Permission to Execute Stored Procedure in SQL Server

To give permission to execute a stored procedure in SQL Server allows you to maintain security and access control.

But why is executing permission is very essential? Let’s understand with examples.

Think about a banking system where employees of the bank need to access the database to perform a particular task.

  • For example, a customer service person can execute a stored procedure to get the account balances, whereas a financial analyst can execute the stored procedure to generate financial reports.
  • By granting execute permission for specific stored procedure, make sure that only authorized employees can perform these actions. If permission is not given according to the roles, then financial data could be exposed.

Now, suppose a healthcare system that can contain stored procedures that return the patient’s medical records.

  • Access to the patient’s medical records should be highly restricted; only the staff or medical professionals can execute the stored procedure to retrieve the patient’s medical records.
  • Giving permission to execute that kind of stored procedure makes sure that the patient’s health data remains confidential.

Another example can be related to the hacking world; by granting execute permission on stored procedures, you can prevent SQL injection. SQL Injection is a kind of security vulnerability that allows an attacker to execute arbitrary SQL code in a database.

Well, now you know you need to grant execute permission on the stored procedure. Let’s move ahead and see how to grant execute permission on the execute procedure.

Give permission to Execute stored Procedure in SQL Server To User

To grant or give permission on any stored procedure, you can use the GRANT statement. The syntax is given below.

GRANT EXECUTE ON schema.stored_procedure_name TO  username;

Where,

  • GRANT EXECUTE ON: It is the statement to grant execute permission on a stored procedure.
  • schema.stored_procedure_name: Name of the stored procedure on which you want to grant the execute permission.
  • TO username: It is the user to whom you want to grant permission to execute the stored procedure.

For example, in SQL Server, there is a new user named james and a stored procedure named GetOrdersByDate.

Now use the below query to grant execute permission on GetOrdersByDate to the user james.

GRANT EXECUTE ON dbo.GetOrdersByDate TO james;
GO
Give permission to Execute stored Procedure in SQL Server To User

After execution of the above query, it shows the error ‘Cannot find the user because it does not exist or you do not have permission’. To solve this error, follow this tutorial: Cannot Find the User Because It Does Not Exist in SQL Server.

When you have resolved the above error, then run the query again.

Give permission to Execute stored Procedure in SQL Server

You have successfully granted the execute permission on the GetOrdersByDate stored procedure, as you can see in the above output.

To view the granted permission, use the below query.

SELECT
USER_NAME(grantee_principal_id) AS [Grantee],
permission_name AS PermissionName,
state_desc AS PermissionState
FROM sys.database_permissions
WHERE major_id = OBJECT_ID('dbo.' + 'GetOrdersByDate', 'P');
Give permission to Execute stored Procedure in SQL Server View the permission

Now you can see the permission name is Execute with permission state equal to GRANT and to a james user. You can also check the permission on any stored procedure by specifying the stored procedure name within the WHERE clause of the above query.

This is how you can grant or give execute permission on stored procedure in SQL Server using the query.

Give permission to Execute stored Procedure in SQL Server To User Using SSMS

But, do you know how to give permission to execute stored procedure in SQL Server using the SQL Server Management Studio?

For that, open the SSMS, expand the Database node, then expand your DatabaseName (E_commerce) where you have created the stored procedure.

After that, expand the Programmability folder, then expand the Stored Procedures subfolder as shown below.

Give permission to Execute stored Procedure in SQL Server Using SSMS Viewing Procedure

Choose the dbo.GetOrdersByDate stored procedure, and right right-click on it, and select the Properties option as shown in the above picture.

Then, a Stored Procedure Properties dialog appears, as shown below.

Give permission to Execute stored Procedure in SQL Server Using SSMS Adding User

Follow the below steps to add the user for permission.

  1. Select the Permission page from the Select a page section on the left side.
  2. Click on the Search button to search for the specific user who can execute the stored procedure.
  3. After clicking on the Search button, a new dialog named Select Users or Roles appears. Then, type the username with square brackets such as [james] within the input box of Enter the object names to select the section.
  4. Finally, click on the OK button, as shown in the above picture.

After that, you see the added user, as shown below.

Give permission to Execute stored Procedure in SQL Server Using SSMS

For granting the execute permission, under the Permissions for james tick the check box button for the corresponding Execute permission, as shown in the above picture. Then click on the OK button.

Now, you have successfully granted or given the execute permission on the GetOrdersByDate stored procedure using the SSMS.

To check the granted permission again, open the properties for the stored procedure as shown in the below picture.

Give permission to Execute stored Procedure in SQL Server Using SSMS View Permission

If you see the Grantor, which is dbo in this case, then ‘james’ user has an execute permission on the GetOrdersByDate stored procedure.

This is how to give permission to execute stored procedure in SQL Server using the query and SQL Server Management Studio.

Conclusion

In this SQL Server tutorial, you learned how to give permission to execute stored procedure in SQL Server with syntax—also learned how to view the given permission on the stored procedure.

You may also like: