how to grant permissions on a stored procedure in SQL Server

Using T-SQL Scripts or SQL Server Management Studio, this article discusses how to grant permissions on a stored procedure in SQL Server. A database role, application role, or an already-existing user can all be given permissions.

Use SQL Server Management Studio

1.     In Object Explorer, connect to an instance of Database Engine and then expand that instance.

2.     Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

3.     Expand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties.

4.     From Stored Procedure Properties, select the Permissions page.

5.     To grant permissions to a user, database role, or application role, select Search.

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

7.     Select Browse to display the list of users or roles. Select the users or roles to whom permissions should be granted.

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

Using T-SQL Scripts

 

--Create a new database role and assign EXECUTE permission:

 

CREATE ROLE SP_Executor

GO

GRANT EXECUTE TO SP_Executor

GO

 

--Create database user and assign this role to it:

 

CREATE USER TestUser FOR LOGIN Database_Readonly_User

GO

EXEC sp_addrolemember N'SP_Executor', N'TestUser'

GO

 

-- Assign EXECUTE permission for a particular stored procedure:

 

GRANT EXECUTE ON [usp_MyStoredProcedure] TO TestUser