SSIS Proxy Setup

BEGIN TRY

    DECLARE @errorMessage NVARCHAR(4000)
    DECLARE @errorNumber INT
    DECLARE @errorSeverity INT
    DECLARE @errorState INT
    DECLARE @errorLine INT

    --Create login based on windows login.
    USE [master]

    IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'$(SSISAccount)')
        BEGIN

            CREATE LOGIN [$(SSISAccount)] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english]
            PRINT 'Login created'
        END
    ELSE
        BEGIN
            PRINT 'Login already exists'
        END

    --Create user and grant access on msdb.
    USE [msdb]

    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
        BEGIN
            CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo]
            PRINT 'MSDB user created'
        END
    ELSE
        BEGIN
            PRINT 'MSDB user already exists'
        END

    --Check for SQL Server 2008 and above - role name changed in 2008.
    DECLARE @version NVARCHAR(128)

    SELECT @version = CAST(SERVERPROPERTY ('ProductVersion') AS NVARCHAR(128))

    SELECT @version = SUBSTRING(@version, 1 , CHARINDEX('.', @version)-1)

    IF (CAST(@version AS INT) >= 10)
        BEGIN
            EXEC sp_addrolemember 'db_ssisoperator', '$(SSISAccount)'
        END
    ELSE
        BEGIN
            EXEC sp_addrolemember 'db_dtsoperator', '$(SSISAccount)'
        END

    EXEC sp_addrolemember 'SQLAgentUserRole', '$(SSISAccount)'

    PRINT 'MSDB user added to roles'


    --create user and grant access on Act-isure db. Add db_executor role for stored procedures.
    USE [$(SourceDB)];

    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
        BEGIN
            CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo];
            PRINT '$(SourceDB) user created'
    END
    ELSE
        BEGIN
            PRINT '$(SourceDB) user already exists'
        END


    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = 'db_executor' AND [type] ='R')
        BEGIN
            --Create a new role for executing stored procedures.
            CREATE ROLE db_executor

            --Grant stored procedure execute rights to the role
            GRANT EXECUTE TO db_executor

            PRINT 'db_executor role created'
        END
    ELSE
        BEGIN
            PRINT 'db_executor role already exists'
        END


    --Add a user to the db_executor role
    EXEC sp_addrolemember 'db_executor', '$(SSISAccount)'
    EXEC sp_addrolemember 'db_datareader', '$(SSISAccount)'

    PRINT '$(SourceDB) user added to roles'




    --Create user and grant access on MIDBLocations db.
    USE [MIDBLocations];

    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
        BEGIN
            CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo];
            PRINT 'MIDBLocations user created'
        END
    ELSE
        BEGIN
            PRINT 'MIDBLocations user already exists'
        END

    EXEC sp_addrolemember 'db_datareader', '$(SSISAccount)'

    PRINT 'MIDBLocations user created added to roles'


    --Create user and grant access on MIDB db. As well as adding the user to roles we grant explicit CRUD permissions on the database to the user with the GRANT OPTION so that
    --the SSIS account can then later grant these permissions to the public role once the tables have been created in the database.
    USE [$(TargetDB)];
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
        BEGIN
            CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo];
            PRINT '$(TargetDB) user created'
        END
    ELSE
        BEGIN
            PRINT '$(TargetDB) user already exists'
        END

    GRANT SELECT, INSERT, DELETE, UPDATE ON database::[$(TargetDB)] TO [$(SSISAccount)] WITH GRANT OPTION;

    EXEC sp_addrolemember 'db_datawriter', '$(SSISAccount)'
    EXEC sp_addrolemember 'db_ddladmin', '$(SSISAccount)'

    PRINT '$(TargetDB) user added to roles'



    --Create the credential required for SQL Server Agent jobs to use as a proxy for running SSIS packages.
    USE [master]

    DECLARE @sql NVARCHAR(MAX)
    DECLARE @name NVARCHAR(250)

    SET @name = SUBSTRING('$(SSISAccount)', CHARINDEX('\', '$(SSISAccount)', 0) + 1, LEN('$(SSISAccount)') - CHARINDEX('\', '$(SSISAccount)', 0) + 1)

    IF NOT EXISTS (SELECT 1 FROM sys.credentials WHERE name = @name)
        BEGIN
            SET @sql = N'CREATE CREDENTIAL [' + @name  + '] WITH IDENTITY = ''$(SSISAccount)'', SECRET = ''$(SSISAccountPwd)'''
            EXEC sp_executesql @sql
            PRINT 'Credential created'
        END
    ELSE
        BEGIN
            PRINT 'Credential already exists'
        END


    --Create SQL Agent proxy using credential and grant access to SSIS subsystem.
    USE [msdb]

    SET @name = SUBSTRING('$(SSISAccount)', CHARINDEX('\', '$(SSISAccount)', 0) + 1, LEN('$(SSISAccount)') - CHARINDEX('\', '$(SSISAccount)', 0) + 1)

    IF NOT EXISTS (SELECT 1 FROM dbo.sysproxies WHERE name = @name)
        BEGIN
            SET @sql = N'EXEC msdb.dbo.sp_add_proxy @proxy_name=N''' + @name + ''',@credential_name=''' + @name + ''', @enabled=1'
            EXEC sp_executesql @sql
            PRINT 'Proxy created'
        END
    ELSE
        BEGIN
            PRINT 'Proxy already exists'
        END

    BEGIN TRY
        EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=@name, @subsystem_id=11
    END TRY
    BEGIN CATCH
        --sp_grant_proxy_to_subsystem is not fault-tolerant so if the grant already exists it throws an error.
        --We're not bothered if that error comes up, but other errors should be re-thrown. 14531 is the error
        --thrown if the grant already exists.
        IF ERROR_NUMBER() <> 14531
            BEGIN
                SET @ErrorMessage = '%u - ' + ERROR_MESSAGE() + ' Line Number: %u'
                SET @ErrorSeverity = ERROR_SEVERITY()
                SET @ErrorState = ERROR_STATE()
                SET @errorNumber = ERROR_NUMBER()
                SET @errorLine = ERROR_LINE()
                RAISERROR (@ErrorMessage, @errorSeverity, @errorState, @errorNumber, @errorLine)
            END
    END CATCH

    PRINT 'Proxy granted access to SSIS subsystem'


    BEGIN TRY
        EXEC sp_grant_login_to_proxy @login_name = '$(SSISAccount)', @proxy_name = @name
    END TRY
    BEGIN CATCH
        --sp_grant_login_to_proxy is not fault-tolerant so if the grant already exists it throws an error.
        --We're not bothered if that error comes up, but other errors should be re-thrown. 14531 is the error
        --thrown if the grant already exists.
        IF ERROR_NUMBER() <> 14531
            BEGIN
                SET @ErrorMessage = '%u - ' + ERROR_MESSAGE() + ' Line Number: %u'
                SET @ErrorSeverity = ERROR_SEVERITY()
                SET @ErrorState = ERROR_STATE()
                SET @errorNumber = ERROR_NUMBER()
                SET @errorLine = ERROR_LINE()
                RAISERROR (@ErrorMessage, @errorSeverity, @errorState, @errorNumber, @errorLine)
            END
    END CATCH

    PRINT 'Proxy linked to server principal $(SSISAccount)'

END TRY

BEGIN CATCH

    SET @ErrorMessage = '%u - ' + ERROR_MESSAGE() + ' Line Number: %u'
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState = ERROR_STATE()
    SET @errorNumber = ERROR_NUMBER()
    SET @errorLine = ERROR_LINE()

    RAISERROR (@ErrorMessage, @errorSeverity, @errorState, @errorNumber, @errorLine)
END CATCH