This article represents the first in what I hope will become a regular series

of articles here at SQLServerCentral.com.  Security is a big topic

nowadays, and security with respect to SQL Server is no exception. SQL Server

has a lot of constructs to help make it a secure database platform. One such

construct is the concept of the role. A role is used to control access to data

within a given database (database roles) or to delegate certain administrative

functions on the server (server roles). Speaking of data access, within the

security world there are typically three ways to handle access:

• Mandatory Access Control (MAC)
• Discretionary Access Control (DAC)
• Role-Based Access Control (RBAC)

Mandatory Access Control is typically seen in military environments where

information is classified at a certain level. If you don't have the level, you

don't have access. For instance, if you bear a Secret clearance but the

information is rated Top Secret, you can't look at the data. However, if you

have Top Secret clearance, you can (provided everything else fits into place,

such as a "Need to Know"). Discretionary Access Control is typically

where permissions are assigned per user. SQL Server has such ability: I can

assign SELECT rights against the Orders table for user JDoe. But SQL Server also

has Role-Based Access Control. And RBAC can really simplify our security plan.

The db_datareader and db_datawiter Roles

Consider the database roles db_datareader and db_datawriter. If I want to

give a particular user the ability to issue a SELECT statement against every

table and view within a given database, I just add the user to the db_datareader

role. Likewise, if I want a given user to have the ability to modify data in any

table or view within the database, I add the user to the db_datawriter role.

Note: Before I go any further, I need to state that I'm not a big fan

of these blanket permission type roles. The main reason is I don't like giving

direct access to tables, preferring to control access through the use of views

and stored procedures. After all, if a user forgets a WHERE clause in a query or

includes it but doesn't get it quite right... you get the idea.. Also, the

db_datareader role has access to all of the system tables within a given

database. This is an information disclosure problem that violates the principle

of least privilege: give a user only the permissions the user needs to complete

his or her work and nothing more.  However, there are instances where these

blanket permission type of roles are implemented because time is short and the

risk is relatively small. The db_executor role will be just such a blanket

permission type of role.

What's Missing: The db_executor Role

While there is a role to SELECT data from all the tables and views in the

db_datareader role and there is a role to INSERT, UPDATE, and DELETE in

db_datawriter, notice that there isn't a role for executing all stored

procedures within a given database. I can fix this by creating a user-defined

role, one which I've named the db_executor since I want it to have EXECUTE

permissions on all stored procedures. To create the db_exector role, I simply

need to use the sp_addrole system stored procedure. I'll go ahead and create

this role in the pubs database:

USE pubs

GO

EXEC sp_addrole 'db_executor'

GO

However, the main problem with the db_executor role is that it currently has

no permissions. This differs from the db_datareader and db_datawriter roles

which implicitly have their rights. In other words, if I add a table or view,

the db_datareader and db_datawriter roles automatically gain access to the table

or view. In contrast, if I add a stored procedure, the db_executor role won't

automatically have EXECUTE rights. Such is the life of a user-defined role!

The Fix: Manually Granting Permission

Since SQL Server isn't going to handle granting permissions for me, I'll have

to do it manually. In order to do this, I'll need to make use of dynamic SQL

since I won't know the owners and stored procedure names at run-time. The trick

is to build the GRANT EXECUTE statement to give db_executor the ability to

execute all stored procedures. The following code does just that in the pubs

database. Notice that I just grant permissions to all stored procedures; I don't

try and compare what db_executor already has versus what it doesn't. If you have

a large number of stored procedures, this might be necessary. I've included a

second script that only retrieves those stored procedures that db_executor

doesn't have access to at the end of the article.

USE pubs

GO

DECLARE @SQL nvarchar(4000),

  @Owner sysname,

  @StoredProcedure sysname,

  @Return int

-- Cursor of all the stored procedures in the current database

DECLARE cursStoredProcedures CURSOR FAST_FORWARD

FOR

SELECT USER_NAME(uid) Owner, [name] StoredProcedure

FROM sysobjects

WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursStoredProcedures

INTO @Owner, @StoredProcedure

-- Set the return code to 0

SET @Return = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))

  BEGIN

    -- Create the SQL Statement. Since we're giving

    -- access to all stored procedures, we have to 

    -- use a two-part naming convention to get the owner.

    SET @SQL = 'GRANT EXECUTE ON [' + @Owner

          + '].[' + @StoredProcedure 

          + '] TO db_executor'

    -- Execute the SQL statement

    EXEC @Return = sp_executesql @SQL

    -- Get the next row

    FETCH NEXT FROM cursStoredProcedures

    INTO @Owner, @StoredProcedure

  END

-- Clean-up after the cursor

CLOSE cursStoredProcedures

DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.

IF (@Return = 0)

  BEGIN

    -- Exited fine, commit the permissions

    COMMIT TRAN

  END

ELSE

  BEGIN

    -- Exited with an error, rollback any changes

    ROLLBACK TRAN

    -- Report the error

    SET @SQL = 'Error granting permission to ['

    + @Owner + '].[' + @StoredProcedure + ']'

    RAISERROR(@SQL, 16, 1)

  END

GO

After the script runs, the db_executor role will have permission to execute

every stored procedure in the database. That means any user I add to the role

will have the ability to use all of the stored procedures in the database.

Scheduling Permission Updates

This script works great but there is a problem: it only runs once. In order

to keep the db_executor role permissions up-to-date, the script will have to be

run as frequently as is necessary for a given database. If there is an extreme

amount of change, the script may be running once a hour, maybe more. SQL Server

Agent can be used to schedule and run the script. But even with SQL Server

Agent, there will be a time lapse between when a stored procedure is created and

when the db_executor role has permission to execute it. The best solution is to

simply grant permission to the db_executor role when the stored procedure is

created, but this isn't always possible.

For instance, in a development environment you may have granted the

db_ddladmin role to a few users. This role allows a user to create database

objects but the role actually goes a step further: it allows a user to create a

database object with any owner (so long as the owner is a valid user in the

database). But the db_ddladmin role doesn't have the ability to assign

permissions. The db_securityadmin role has such rights but it's rare indeed when

I see a developer put in both roles (db_owner also can, but it's rarer still for

a developer to have such high permissions). If you're using db_ddladmin you may

have to look at scheduling the script in order to keep the db_executor role up-

to- date. How often will depend on the environment.

In a production environment with proper change control procedures, this

should never be a problem. When the stored procedure is put into the

environment, permissions to execute the stored procedure should also be

included. Even if they aren't, when you make an update to the production

environment, you can also run the script manually to update all the permissions

and ensure no stored procedure is missed. So scheduling automatic updates in the

production environment isn't of as great a concern as in development.

In Closing

It is a shame that SQL Server has the db_datareader and db_datawriter roles

but currently has nothing equivalent to a db_executor role. However, we can make

a user-defined role that sort of fits the bill. The problem is, of course,

keeping the permissions up-to-date. In a production environment change should be

carefully controlled and this usually isn't that big an issue, but development

is a different story. Unless developers possess the db_securityadmin role (or

db_owner role), you'll need to look at some automated mechanism (such as through

SQL Server Agent) scheduled regularly enough to be sufficient for the

development effort.  If you have need of the db_executor role, hopefully

this is enough to get you started.