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)