db_executor role for user permission to execute all procedures

SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :

-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor

 

--then add a member to this role

 

Check a list of objects and permissions

--list views, procs, table

select o.* from sys.database_principals p inner join sys.objects o on p.principal_id = p.principal_id where p.name = 'GRP_App_Users' and o.type in ('U','V','P') and o.is_ms_shipped = 0


--select permissions of a group or user on a database

select p1.*,o.* from sys.database_permissions p1 inner join sys.database_principals p2 on p1.grantee_principal_id = p2.principal_id inner join sys.objects o on o.object_id = p1.major_id where p2.name = 'GRP_SQLReportingUsers'


--FIND SPS WITH EXECUTE PERMISSIONS ON A USER

SELECT [name] FROM sys.objects obj INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id WHERE obj.[type] = 'P' -- stored procedure AND dp.permission_name = 'EXECUTE' AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT AND dp.grantee_principal_id = (SELECT principal_id FROM sys.database_principals WHERE [name] = 'GRP_USERS_ABC') ORDER BY NAME

Giving execute permission to all stored procedures through a databaserole

I had a situation where I had to give a user( AD group) execution permission to all the sps.

- Created a role called db_executor data user role

USE pubs
GO

exec sp_addrole  'db_executor'

GO

--added memebers to the role

USE pubs
GO

exec sp_addrolemember 'db_executor', 'GRP_ProdSupport'

GO

--gave permission to execute the stored procedures to the role.

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

(code source : http://www.sqlservercentral.com/articles/Security/sqlserversecuritythedb_executorrole/988/ )