Grant Execute Or View Permission To Stored Procedures In SQL Server

In our previous article we learned how to grant read and write permission to any user. We have also seen a demonstration where we provided permissions via GUI and T-SQL.

For this example as well, we require two connections.

• Admin Connection
• SQL Server User Connection

Firstly, connect your SQL Server instance with your admin connection and select your database, we’ll be using our as usual database ‘CSharpCornerDB’.

Now let’s create a simple procedure with the following script to work with.

CREATE PROCEDURE usp_GetUserDetails
@Username varchar(15)
AS
BEGIN
--SET NOCOUNT ON added to prevent extra result sets from
--interfering with SELECT statements.
SET NOCOUNT ON;

SELECT au.Author_Name as[Author], au.City, au.Country, au.IsMVP, Count(at.ArticleName) as[Total Articles] from tblArticles at
join tblAuthors au
on au.Author_Name = at.AuthorName
where at.AuthorName = @Username
group by au.Author_Name, au.City, au.Country, au.IsMVP

Select ArticleName as[Articles] from tblArticles where AuthorName = @Username
END
GO

Our procedure is ready now.



This procedure takes username as parameter and returns author details and number of articles written by them. In short, we’re trying to read user details by providing user’s name.

Now connect your server with SQL Server Account. We’ve our SQL Account login named ‘MChand’.



As we already provided our user READ/WRITE permission, let’s connect and run above created stored procedure.

To run stored procedure, execute the following command and let’s see the output we get.

EXEC usp_GetUserDetails 'Abhishek'

When SQL Server user tries to run above query, he/she will encounter the following error message.

Msg 229, Level 14, State 5, Procedure usp_GetUserDetails, Line 1
The EXECUTE permission was denied on the object 'usp_GetUserDetails', database 'CSharpCornerDB', schema 'dbo'.

Also you won’t find created procedure under 'Stored Procedures' folder.



Even ifthe user had READ permission on database, the above query should have executed without any issue or procedure should have listed in the folder. But this doesn’t happen. Let’s see why.

If you see the following diagram, we have a database under which we’ve multiples of database objects like tables, views, stored procedures, etc.



And ifthe user has READ permission on database,the  SQL Server knows that the user has full right to read data stored in its objects and if the user wants to read data from tables or views, he/she requires SELECT statement. Till here, SQL Server knows there’s no harm with SELECT statement because it’s not going to modify any data inside any objects, so it allows user to READ data from Tables, Views or Functions.

But when it comes to Stored Procedures, the scenario is different. This is because stored procedure uses multiple dynamic queries which may contain CREATE, DELETE, UPDATE or any other statement which may modify data in objects.

Let’s consider a scenario where I’ve a stored procedure named ‘usp_GetDataFromTables’.



By looking at procedure name, we may assume that this procedure may return data from tables. But we don’t know what type of statements this procedure is using. Let’s check the statement the procedure is using to get the data from the tables.

create proc usp_GetDataFromTables
as
BEGIN
Delete from tblArticles
END

Ooopppss!! A DELETE statement!!!

Imagine if this procedure executes on your server, you might lose your entire data from that table.

This is the reason SQL Server doesn’t provide direct access on any of the objects which may modify the data.

Even if your stored procedures are using SELECT statements, you won’t be able to execute those procedures unless you’ve permission to execute. If you’ve READ permission on database, you can read data only from Tables, Views, and Functions.

But to execute stored procedures, you need to provide permission explicitly to user. There are multiple ways you can provide execute permission to any user. We’ll see those one-by-one.

Way 1:

Connect Server with Admin Session - Go to Database, Securities, Users, then select user.



Right click and select Properties and you’ll get the following database user property window.



Go to Securable and click on Search button as in the preceding image. On clicking the Search button, you’ll find he following window to add the type of object.



Select your desired option. We want to give permission on a specific object; we’ll go with option 1.

Click on Object Types button and you’ll get “Select Object Types” window with various objects.



Select 'Stored procedures' and click OK.

Now if you see, stored procedure is listed in the object types area. Now we’ll select our specific stored procedure on which we want to provide permission.



For this, click on Browse button and select your stored procedure as in the preceding image. Click OK and you’ll get the following window with selected details.



Click OK and your procedure gets listed in Securable section with multiple permissions.

Tick the Grant column checkbox which will allow user to execute stored procedure and click OK as shown below.



Way 2:

Connect Server with Admin Session - Go to Database, Programmability, Stored Procedures, then select your Procedure.



Right click on your procedure and select Properties. You’ll get the following window.



As shown inthe preceding image, go to Permissions tab and click on Search button. On click you’ll get a window to select user and roles, click on Browse to select users that require permission and click OK.

You’ll get the following ‘Image 3’ with selected user.




Click OK and you’ll get a list of permissions to apply for the user. Go to execute permission and check Grant checkbox as shown below.



Click OK and you’re set to go.

Way 3: T-SQL

use [CSharpCornerDB]
GO
GRANT EXECUTE ON [dbo].[usp_GetUserDetails] TO [MChand]
GO

Now connect to instance with SQL Login (i.e. MChand) and now you’ll be able to view the procedure.



When you execute the following query from SQL Server user session, you’ll get proper output.

EXEC usp_GetUserDetails 'Abhishek'

Output



We’ve successfully provided permission to execute the procedure on the server. But if you wish to view the code the procedure is using, execute the following command.

sp_helptext usp_GetUserDetails

On execution, SQL Server will throw below error due to insufficient access to the user.

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107
There is no text for object 'usp_GetUserDetails'.

To view the code, our user requires VIEW DEFINITION permission on this procedure.

To provide it via GUI, you can select any of the above shown ways and on permission window, select ‘View definition’ and check ‘Grant’ checkbox as shown below.



T-SQL

use [CSharpCornerDB]
GO
GRANT VIEW DEFINITION ON [dbo].[usp_GetUserDetails] TO [MChand]
GO

Now you’re able to view the code used in the stored procedure as in the following screenshot:



Conclusion

So this was the article based on another SQL Server Security where we learned how to provide execute and view definition permission to a user on any procedure.

I hope this article will help you understand how to provide execute permission. You can also play with other permissions on your procedures. If you need any help in this, please let me know. I’ll try my best to solve those.

Please provide your valuable feedback and comments that enable me to provide a better article next time. Until then keep learning and sharing.