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
GO
--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
BEGIN
    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
     
    DECLARE @SQL NVARCHAR(4000)
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
         
        SET @SQL = 'ALTER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[')
        EXEC(@SQL)
         
        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName
    END;
 
    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
END
/****** Object:  DatabaseRole [db_execproc]    Script Date: 3/5/2016 8:40:20 PM ******/
DROP ROLE [db_execproc]
GO
 
/****** Object:  DatabaseRole [db_execproc]    Script Date: 3/5/2016 8:40:20 PM ******/
CREATE ROLE [db_execproc]
GO
 
--Grant EXECUTE permission at the schema level to the role.
 
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO
 
--EXEC sp_addrolemember N'db_execproc', N'TestUser'; -- Add user here
GO