Written by David Postlethwaite

Security of the SQL database is becoming more and more important these days yet I’m finding that vendors often overlook setting minimum required privileges on a database. Many database I’ve been given to install just assign db_owner to the user accounts that really only need read and write. This means that those accounts can do whatever they want to the database, even dropping it.

 
But if you try and fix this by limiting that account to datareader and datawriter you often find that the application no longer works because the account can’t execute any stored procedures or functions. The vendor has relied on the fact that db_owner can do anything so no explicit execute permissions have been given.

So how do we fix this?

 
In a previous article Gethyn showed how to grant implicit execute permissions to the whole database to a new role called db_executer.

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor

Then you just need to give the users datareader, datawriter and db_executer and hopefully everything will carry on working.
 
But from a support view , if another DBA looked at the properties of a stored procedure in SSMS he wouldn’t immediately see this as the explicit permissions screen would not show that the user had execute permissions for that particular stored procedure.

 
To assign explicit execute permissions to every stored procedure and function something slightly more complex is required. The code below will find all the stored procedures and functions in a database and create the appropriate T-SQL statement to grant the db_executer role execute permissions (or select permissions for a table value function)

For safety this will print the T-SQL, you will need to uncomment the exec line if you want it to run. Or you could put the output to text and copy and paste it into a query window and run it like that

CREATE ROLE db_executor

-- Declare Variables
DECLARE @cmd1 varchar(8000)
DECLARE @SchemaName varchar(128)
DECLARE @ProcName varchar(128)
DECLARE @ProcType varchar(128)
DECLARE @FuncType varchar(128)

-- Create Cursor
DECLARE temp_cursor CURSOR FOR
select routine_schema,routine_name,routine_type,data_type from information_schema.routines
where routine_type in('function','procedure')
order by routine_type desc, data_type

-- loop through cursor
OPEN temp_cursor
FETCH NEXT FROM temp_cursor
INTO @SchemaName ,@ProcName, @ProcType, @FuncType

WHILE @@FETCH_STATUS = 0
BEGIN
-- Build the cmd string
IF @ProcType='Procedure' OR (@ProcType='function' AND @FuncType<>'table')
BEGIN
SELECT @cmd1 = 'GRANT EXEC ON ' + '[' + @SchemaName + ']' + '.' + '[' + @ProcName + ']' + ' TO db_executor'
END

IF @ProcType='function' AND @FuncType='table'
BEGIN
SELECT @cmd1 = 'GRANT SELECT ON ' + '[' + @SchemaName + ']' + '.' + '[' + @ProcName + ']' + ' TO db_executor'
END

-- Execute the string
print @cmd1
-- EXEC(@cmd1)

FETCH NEXT FROM temp_cursor
INTO @SchemaName ,@ProcName, @ProcType, @FuncType
END

CLOSE temp_cursor
DEALLOCATE temp_cursor

I hope you find this useful