spettifer

SSIS Proxy Setup

May 2nd, 2013
310
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. BEGIN TRY
  3.  
  4.     DECLARE @errorMessage NVARCHAR(4000)
  5.     DECLARE @errorNumber INT
  6.     DECLARE @errorSeverity INT
  7.     DECLARE @errorState INT
  8.     DECLARE @errorLine INT
  9.  
  10.     --Create login based on windows login.
  11.     USE [master]
  12.    
  13.     IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'$(SSISAccount)')
  14.         BEGIN
  15.    
  16.             CREATE LOGIN [$(SSISAccount)] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english]
  17.             PRINT 'Login created'
  18.         END
  19.     ELSE
  20.         BEGIN
  21.             PRINT 'Login already exists'
  22.         END
  23.  
  24.     --Create user and grant access on msdb.
  25.     USE [msdb]
  26.    
  27.     IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
  28.         BEGIN
  29.             CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo]
  30.             PRINT 'MSDB user created'
  31.         END
  32.     ELSE
  33.         BEGIN
  34.             PRINT 'MSDB user already exists'
  35.         END
  36.  
  37.     --Check for SQL Server 2008 and above - role name changed in 2008.
  38.     DECLARE @version NVARCHAR(128)
  39.  
  40.     SELECT @version = CAST(SERVERPROPERTY ('ProductVersion') AS NVARCHAR(128))
  41.  
  42.     SELECT @version = SUBSTRING(@version, 1 , CHARINDEX('.', @version)-1)
  43.  
  44.     IF (CAST(@version AS INT) >= 10)
  45.         BEGIN
  46.             EXEC sp_addrolemember 'db_ssisoperator', '$(SSISAccount)'
  47.         END
  48.     ELSE
  49.         BEGIN
  50.             EXEC sp_addrolemember 'db_dtsoperator', '$(SSISAccount)'
  51.         END
  52.  
  53.     EXEC sp_addrolemember 'SQLAgentUserRole', '$(SSISAccount)'
  54.  
  55.     PRINT 'MSDB user added to roles'
  56.  
  57.  
  58.     --create user and grant access on Act-isure db. Add db_executor role for stored procedures.
  59.     USE [$(SourceDB)];
  60.    
  61.     IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
  62.         BEGIN
  63.             CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo];
  64.             PRINT '$(SourceDB) user created'
  65.     END
  66.     ELSE
  67.         BEGIN
  68.             PRINT '$(SourceDB) user already exists'
  69.         END
  70.  
  71.  
  72.     IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = 'db_executor' AND [type] ='R')
  73.         BEGIN
  74.             --Create a new role for executing stored procedures.
  75.             CREATE ROLE db_executor
  76.  
  77.             --Grant stored procedure execute rights to the role
  78.             GRANT EXECUTE TO db_executor
  79.            
  80.             PRINT 'db_executor role created'
  81.         END
  82.     ELSE
  83.         BEGIN
  84.             PRINT 'db_executor role already exists'
  85.         END
  86.    
  87.  
  88.     --Add a user to the db_executor role
  89.     EXEC sp_addrolemember 'db_executor', '$(SSISAccount)'
  90.     EXEC sp_addrolemember 'db_datareader', '$(SSISAccount)'
  91.    
  92.     PRINT '$(SourceDB) user added to roles'
  93.  
  94.    
  95.  
  96.  
  97.     --Create user and grant access on MIDBLocations db.
  98.     USE [MIDBLocations];
  99.    
  100.     IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
  101.         BEGIN
  102.             CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo]
  103.             PRINT 'MIDBLocations user created'
  104.         END
  105.     ELSE
  106.         BEGIN
  107.             PRINT 'MIDBLocations user already exists'
  108.         END
  109.    
  110.     EXEC sp_addrolemember 'db_datareader', '$(SSISAccount)'
  111.  
  112.     PRINT 'MIDBLocations user created added to roles'
  113.  
  114.  
  115.     --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
  116.     --the SSIS account can then later grant these permissions to the public role once the tables have been created in the database.
  117.     USE [$(TargetDB)];
  118.     IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(SSISAccount)')
  119.         BEGIN
  120.             CREATE USER [$(SSISAccount)] FOR LOGIN [$(SSISAccount)] WITH DEFAULT_SCHEMA=[dbo];
  121.             PRINT '$(TargetDB) user created'
  122.         END
  123.     ELSE
  124.         BEGIN
  125.             PRINT '$(TargetDB) user already exists'
  126.         END
  127.    
  128.     GRANT SELECT, INSERT, DELETE, UPDATE ON database::[$(TargetDB)] TO [$(SSISAccount)] WITH GRANT OPTION;
  129.    
  130.     EXEC sp_addrolemember 'db_datawriter', '$(SSISAccount)'
  131.     EXEC sp_addrolemember 'db_ddladmin', '$(SSISAccount)'
  132.    
  133.     PRINT '$(TargetDB) user added to roles'
  134.  
  135.  
  136.  
  137.     --Create the credential required for SQL Server Agent jobs to use as a proxy for running SSIS packages.
  138.     USE [master]
  139.    
  140.     DECLARE @sql NVARCHAR(MAX)
  141.     DECLARE @name NVARCHAR(250)
  142.    
  143.     SET @name = SUBSTRING('$(SSISAccount)', CHARINDEX('\', '$(SSISAccount)', 0) + 1, LEN('$(SSISAccount)') - CHARINDEX('\', '$(SSISAccount)', 0) + 1)
  144.    
  145.     IF NOT EXISTS (SELECT 1 FROM sys.credentials WHERE name = @name)
  146.         BEGIN
  147.             SET @sql = N'CREATE CREDENTIAL [' + @name  + '] WITH IDENTITY = ''$(SSISAccount)'', SECRET = ''$(SSISAccountPwd)'''
  148.             EXEC sp_executesql @sql
  149.             PRINT 'Credential created'
  150.         END
  151.     ELSE
  152.         BEGIN
  153.             PRINT 'Credential already exists'
  154.         END
  155.    
  156.  
  157.     --Create SQL Agent proxy using credential and grant access to SSIS subsystem.
  158.     USE [msdb]
  159.  
  160.     SET @name = SUBSTRING('$(SSISAccount)', CHARINDEX('\', '$(SSISAccount)', 0) + 1, LEN('$(SSISAccount)') - CHARINDEX('\', '$(SSISAccount)', 0) + 1)
  161.    
  162.     IF NOT EXISTS (SELECT 1 FROM dbo.sysproxies WHERE name = @name)
  163.         BEGIN
  164.             SET @sql = N'EXEC msdb.dbo.sp_add_proxy @proxy_name=N''' + @name + ''',@credential_name=''' + @name + ''', @enabled=1'
  165.             EXEC sp_executesql @sql
  166.             PRINT 'Proxy created'
  167.         END
  168.     ELSE
  169.         BEGIN
  170.             PRINT 'Proxy already exists'
  171.         END
  172.    
  173.     BEGIN TRY
  174.         EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=@name, @subsystem_id=11
  175.     END TRY
  176.     BEGIN CATCH
  177.         --sp_grant_proxy_to_subsystem is not fault-tolerant so if the grant already exists it throws an error.
  178.         --We're not bothered if that error comes up, but other errors should be re-thrown. 14531 is the error
  179.         --thrown if the grant already exists.
  180.         IF ERROR_NUMBER() <> 14531
  181.             BEGIN
  182.                 SET @ErrorMessage = '%u - ' + ERROR_MESSAGE() + ' Line Number: %u'
  183.                 SET @ErrorSeverity = ERROR_SEVERITY()
  184.                 SET @ErrorState = ERROR_STATE()
  185.                 SET @errorNumber = ERROR_NUMBER()
  186.                 SET @errorLine = ERROR_LINE()
  187.                 RAISERROR (@ErrorMessage, @errorSeverity, @errorState, @errorNumber, @errorLine)
  188.             END
  189.     END CATCH
  190.    
  191.     PRINT 'Proxy granted access to SSIS subsystem'
  192.    
  193.    
  194.     BEGIN TRY
  195.         EXEC sp_grant_login_to_proxy @login_name = '$(SSISAccount)', @proxy_name = @name
  196.     END TRY
  197.     BEGIN CATCH
  198.         --sp_grant_login_to_proxy is not fault-tolerant so if the grant already exists it throws an error.
  199.         --We're not bothered if that error comes up, but other errors should be re-thrown. 14531 is the error
  200.         --thrown if the grant already exists.
  201.         IF ERROR_NUMBER() <> 14531
  202.             BEGIN
  203.                 SET @ErrorMessage = '%u - ' + ERROR_MESSAGE() + ' Line Number: %u'
  204.                 SET @ErrorSeverity = ERROR_SEVERITY()
  205.                 SET @ErrorState = ERROR_STATE()
  206.                 SET @errorNumber = ERROR_NUMBER()
  207.                 SET @errorLine = ERROR_LINE()
  208.                 RAISERROR (@ErrorMessage, @errorSeverity, @errorState, @errorNumber, @errorLine)
  209.             END
  210.     END CATCH
  211.    
  212.     PRINT 'Proxy linked to server principal $(SSISAccount)'
  213.    
  214. END TRY
  215.  
  216. BEGIN CATCH
  217.  
  218.     SET @ErrorMessage = '%u - ' + ERROR_MESSAGE() + ' Line Number: %u'
  219.     SET @ErrorSeverity = ERROR_SEVERITY()
  220.     SET @ErrorState = ERROR_STATE()
  221.     SET @errorNumber = ERROR_NUMBER()
  222.     SET @errorLine = ERROR_LINE()
  223.  
  224.     RAISERROR (@ErrorMessage, @errorSeverity, @errorState, @errorNumber, @errorLine)
  225. END CATCH
RAW Paste Data