Role creation with alter privilege Hi I am creating a SSIS package that truncates table as part of ETL process. A role needs to be defined with the alter privilege and the service account added to the role

I have done it the following way. Could you let me know if it is correct ? Isnt there a way of only assigning alter permission to role rather than involving table 

USE CoreReferenceStaging
exec sp_addrole  'alterpermissions', 'INT\svc-w-corerefdata-de'  
exec sp_addrolemember 'alterpermissions', 'INT\svc-w-corerefdata-de'
GRANT ALTER ON [dbo].[theTable] TO [alterpermissions]
First to answer your question, yes that looks like it will work.  However, this approach opens up permissions a bit more than you probably want.  While it's accurate that ALTER permissions are required to Truncate a table, ALTER permissions also grant the following:


  When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.


If you only require this process to truncate a table, I suggest you create a Stored Procedure that utilizes elevated rights via Impersonation to perform the truncate so you keep your SSIS account at a minimal level of security.

First, you need to create a stored procedure that provides your Truncate Table functionality, as follows:

-- Run this procedure as a user with appropriate Control privileges in the database, and adjust to your liking
CREATE PROCEDURE [dbo].[TRUNCATE_TABLE]
(
    @TableName NVARCHAR(128)
) WITH EXECUTE AS SELF
AS
BEGIN
    -- Only Allow Truncates to dbo schema and user's owned Schema
    IF EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.schemas s
                    ON t.schema_id = s.schema_id
                   WHERE (s.name + N'.' + t.name = @TableName
                        OR s.name + N'.' + t.name = (SELECT TOP 1 default_schema_name FROM sys.database_principals 
                                                     WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID()))) + N'.' + @TableName)
                        AND s.name IN (N'dbo', (SELECT TOP 1 default_schema_name FROM sys.database_principals 
                                                     WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID())))))
    BEGIN
        DECLARE @ReturnCode INT
        DECLARE @DynamicSQL NVARCHAR(272)

        SELECT @TableName = CASE WHEN @TableName LIKE N'%.%' THEN @TableName 
                                 ELSE QUOTENAME((SELECT TOP 1 default_schema_name FROM sys.database_principals 
                                                 WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID())))) + N'.' + @TableName
                            END

        SET @DynamicSQL = N'TRUNCATE TABLE ' + @TableName

        -- Initialize @ReturnCode variable
        SET @ReturnCode = -1

        BEGIN TRY
            -- Attempt Truncate Operation
            EXEC @ReturnCode = sp_executeSQL @DynamicSQL
        END TRY
        BEGIN CATCH
            -- If an Error is encountered, trap it
            DECLARE @ErrorMessage NVARCHAR(2048), @ErrorNumber INT, @ErrorSeverity INT,
                @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(126);

            SELECT @ErrorNumber = ERROR_NUMBER(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE(),
                   @ErrorLine = ERROR_LINE(),
                   @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '');

            SELECT @ErrorMessage = N'Error %d, Level %d, State %d, PROCEDURE %s, Line %d, ' +
                N'Message: ' + ERROR_MESSAGE();

            -- Raise an error: msg_str parameter of RAISERROR will contain
            -- the original error information.
            RAISERROR(@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity,
                      @ErrorState, @ErrorProcedure, @ErrorLine)

            RETURN @ReturnCode
        END CATCH
    END

    ELSE
    BEGIN
        -- If user is trying to Truncate a table they are not allowed to, throw and error.
        RAISERROR(N'Cannot find the object because it does not exist or you do not have permissions.'
                ,16, 1, 1088, 16, 7, N'[dbo].[TRUNCATE_TABLE]', 1)
        RETURN -1
    END
END


Now create a database role against which execution of this stored procedure is provided.

-- Create a DB Role
CREATE ROLE TruncatePrivs
GRANT EXECUTE ON TRUNCATE_TABLE TO TruncatePrivs
GO

-- Add Users to the Role
EXEC sp_addrolemember 'TruncatePrivs', 'INT\svc-w-corerefdata-de'
GO


The SSIS user can now truncate whatever table you wish to allow simply by calling the stored procedure from within a Execute SQL Task step within the package, as follows:

EXEC dbo.TRUNCATE_TABLE @Tablename = N'TableName'