In SQL Server a good practice is to access the data via calls through stored procedure.  Have a look at the document available in that link.

To further this practice, one may create a database role, then add users to that role.  Permissions to execute the stored procedures would then be granted to the role.  A role is simple enough to create.  You can do that with the following code.

Code block

CREATE ROLE db_executor

After creating this role, simply add users to that role.  The next part of the process is to ensure that you have granted the appropriate permissions to this role.  There are two methods to do that: 1) blanket execute to all procedures, and 2) pick and choose the procs to which you wish to grant permission.

Before we get to adding permissions, let’s create a little test proc for testing purposes.  I will reuse something from a past article to simplify.  In that article, I already did the setup for the table – you can get it from here.  The stored procedure is as follows.

Code block

CREATE PROCEDURE colorwheel
@ColorType INT = 3
 
AS
SET NOCOUNT ON
BEGIN
SELECT cp1.*
FROM ColorPlate cp1
WHERE cp1.colortype & @ColorType <> 0
ORDER BY ColorID;
END

Let’s also make sure that the user has been added to the role.

Code block

EXEC SP_ADDROLEMEMBER 'db_executor', 'testu'

As for the exercise in how to create that database user and the associated login, I will leave that for you to do.

In order to test, we need to connect to the SQL Server as that user.  Once connected, run the following to verify that your session is connected as desired.

Code block

SELECT SYSTEM_USER AS LoggedInUser

On my connection, running that query will show that the LoggedInUser is testu.  Now, having confirmed that I am connected as the appropriate user, I will try to execute that test proc we created.

Code block

EXEC colorwheel

At this point, the expected results should be similar to this error message.

Code block

Msg 229, LEVEL 14, STATE 5, PROCEDURE colorwheel, Line 1
The EXECUTE permission was denied ON the OBJECT 'colorwheel', DATABASE 'TestA', SCHEMA 'dbo'.

Now, I will switch over to the previous connection where I have administrative permissions.  I will now proceed to grant execute permissions following the first method – blanket grant.

Code block

GRANT EXECUTE TO db_executor
go

And now, flip back to the user connection to test our permissions.  Try running that proc again, and your results should be similar to these.

That is good, but what else can this user now do?  The user can execute all user created stored procedures.  Do you necessarily want this?  What if your business requirements specify that certain user groups be able to execute only certain procs?

In that case, we now need to grant execute permissions on a more granular level.  A big problem with this method pops up right from the beginning.  What if there are thousands of stored procedures?  What if you need to grant execute permissions to hundreds of stored procedures for each role?

We have two avenues for these types of situations.  One avenue is to separate the various stored procedures via schema and then grant execute to the schema.  The other is via naming convention.

In the event you have a suitable naming convention to help mass assign permissions, here is a little script to help.

Code block

SELECT 'Grant Execute on ' + NAME + 'to [db_executor]'
FROM sys.objects
WHERE type = 'p'
AND Name LIKE '%usp_rs_%'

It is very simplistic, I know.  I also left an example of such a naming scheme.  In this example, the naming convention may imply that the procedure is a Reporting Services stored procedure.  I can query for all of the Reporting Services procs in the database, and then assign permissions to all of them much faster.

This method does not immediately grant permissions to the entire result set.  It does allow for you to review the results.

Are there more elaborate examples out there?  Certainly.  Find a method that suits you.  I would be very careful about using the first method though – it just might be too much granted to the user.