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 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 the username as a parameter and returns author details and a 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 the 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 the stored procedure, execute the following command, and let’s see the output we get.

EXEC usp_GetUserDetails 'Abhishek';

When an SQL Server user tries to run the 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', and schema 'do'.

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

Even if the user had READ permission on the database, the above query should have been executed without any issue or the procedure should have been 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 multiple database objects like tables, views, stored procedures, etc.

If the user has READ permission on the 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 a SELECT statement. Till here, the SQL Server knows there’s no harm with the SELECT statement because it’s not going to modify any data inside any objects, so it allows the user to READ data from Tables, Views, or Functions.

But when it comes to Stored Procedures, the scenario is different. This is because the 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 have a stored procedure named ‘usp_GetDataFromTables’.

By looking at the 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

Oops!! 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 to 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 them. If you’ve READ permission on the database, you can read data only from Tables, Views, and Functions.

But to execute stored procedures, you need to provide permission explicitly to the 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 the Search button as in the preceding image. On clicking the Search button, you’ll find the 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 the Object Types button and you’ll get the “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 the specific stored procedure on which we want to provide permission.

For this, click on the 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 the Securable section with multiple permissions.

Tick the Grant column checkbox which will allow the user to execute the 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 in the preceding image, go to the Permissions tab and click on the Search button. On click you’ll get a window to select users and roles, click on Browse to select users that require permission, and click OK.

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

Click OK and you’ll get a list of permissions to apply for the user. Go to execute permission and check the 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 the instance with SQL Login (i.e. MChand) and you’ll be able to view the procedure.

When you execute the following query from the 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, the SQL Server will throw the 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 the permission window, select ‘View definition’ and check the ‘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 with 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.