Generate GRANT commands using a SELECT statement:

-- Replace the length 30 with appropriate length
-- Replace DOMAIN\username with your login name
SELECT 'GRANT INSERT ON ' + LEFT([name],30) + ' TO [DOMAIN\username]' FROM sys.tables
 
-- Example output
GRANT INSERT ON Table1 TO [WBS\Leon]
GRANT INSERT ON Table2 TO [WBS\Leon]
GRANT INSERT ON Table3 TO [WBS\Leon]

Examples of the GRANT command:

-- Grant SELECT permissions on the table Book
GRANT SELECT ON Book TO username
 
-- Grant EXEC permissions on the sproc sp_FindBooks 
GRANT EXEC ON sp_FindBooks TO username
 
-- Grant EXEC permissions on custom function fn_BeginOfWeek
GRANT EXEC ON fn_BeginOfWeek TO username
 
-- Grant SELECT permissions on the table Customer in the database MyDatabase
GRANT SELECT ON MyDatabase..Customer TO username
 
-- Other examples
GRANT ALTER ANY SCHEMA TO username
GRANT CONNECT TO username
GRANT CREATE FUNCTION TO username
GRANT CREATE PROCEDURE TO username
GRANT EXECUTE TO username
GRANT SELECT TO username

Create GRANT commands dynamically:

DECLARE @GroupName NVARCHAR(256), @cmd NVARCHAR(500)
 
-- Get the group name from an Organization table
SELECT @GroupName=GroupName FROM Organization
 
SET @cmd = 'GRANT EXEC ON dbo.fn_BeginOfWeek TO [' + @GroupName + ']'
EXEC (@cmd) 
SET @cmd = 'GRANT EXEC ON dbo.sp_GetDate TO [' + @GroupName + ']'
EXEC (@cmd)