Thursday, March 17, 2016

SQL Server db_executor role to grant execute permission

Recently I was answering to a question on a SQL Server public forum, that how to grant only execute permission on a database for some users? Yes, It’s very easy to grant execute permission to a user but somehow I feel there should have some built-in roles like db_datareader or db_datawriter for the same? Anyway it’s not that difficult to create one. Let’s create a role and name it db_execproc, then add role-members.
USE [DBA] --Change the database name
--It will drop the role if it is already exists.
DECLARE @RoleName sysname
set @RoleName = N'db_execproc'
IF @RoleName <> N'public' and (select is_fixed_role from sys.database_principals where name = @RoleName) = 0
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name]
    from sys.database_principals
    where principal_id in (
        select member_principal_id
        from sys.database_role_members
        where role_principal_id in (
            select principal_id
            FROM sys.database_principals where [name] = @RoleName AND type = 'R'))
    OPEN Member_Cursor;
    FETCH NEXT FROM Member_Cursor
    into @RoleMemberName
        SET @SQL = 'ALTER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[')
        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName
    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
/****** Object:  DatabaseRole [db_execproc]    Script Date: 3/5/2016 8:40:20 PM ******/
DROP ROLE [db_execproc]
/****** Object:  DatabaseRole [db_execproc]    Script Date: 3/5/2016 8:40:20 PM ******/
CREATE ROLE [db_execproc]
--Grant EXECUTE permission at the schema level to the role.
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
--EXEC sp_addrolemember N'db_execproc', N'TestUser'; -- Add user here