MINIMUM permissions needed to execute an SSIS job?

• ericb1

  SSCrazy

  Points: 2354

  More actions

  January 27, 2009 at 8:06 am

  #128267

  Using sql server 2005, I have another office that needs to connect to my sql server via VPN and start an SSIS job that I have running here. This is the only task they need to perform, so I'm trying to limit the access to my sql server.

  Can someone please tell me the best way to add a SQL account that do just this one task?

  Any help is greatly appreciated, thanks!

• Darren Wallace

  Right there with Babe

  Points: 774

  More actions

  January 27, 2009 at 10:05 am

  #933331

  In order to run SSIS packages you need to be a member of db_dtsoperator, db_dtsltduser, or db_dtsadmin. See http://msdn.microsoft.com/en-us/library/ms141053(SQL.90).aspx

  Rather than granting the user this kind of permissions (probably more than you trust them with) I'd suggest using a signed stored procedure.This might be overkill for your environment but I've found it lets me sleep better at night in mine.

  Assuming you store your SSIS packages in MSDB then the following should work as a general outline.

  Overview:

  Basically you create a user in MSDB with permission to run dts/ssis packages (member of db_dtsoperator role). This user is mapped to an asymetric key so that you can create a signed stored procedure in a separate database that executes as the user in MSDB without giving your remote user any extra permissions. Once you've got it setup you create a regular user in the user database and grant them execute permission on the signed procedure. Now the user that you give out only has permissions to execute one stored procedure (which runs the SSIS package) and nothing else. The package will actually run as the asymetric key user.

  Steps:

  System Setup:

  Create the strong name file (asymmetric key pair) using the Visual Studio 2005 signing tool (sn.exe).

  H:\> sn -k 2048 SSIS_EXECUTION_KEY.SNK

  Import the asymmetric key into the MSDB database on SQL Server, create a user mapped to the key, and add the user to the db_dtsoperator role.

  USE MSDB

  GO

  CREATE ASYMMETRIC KEY SSIS_EXECUTION_KEY

  FROM FILE = N'C:\DevDatabases\SSIS_EXECUTION_KEY.SNK'

  ENCRYPTION BY PASSWORD = N'ADFB$W%&SJE%^dxme56';

  GO

  CREATE USER SSIS_EXECUTION_USER

  FOR ASYMMETRIC KEY SSIS_EXECUTION_KEY;

  GO

  EXEC sp_addrolemember N'db_dtsoperator', N'SSIS_EXECUTION_KEY'

  GO

  Individual Database Setup (USE MyDB):

  Import the asymmetric key into the database.

  CREATE ASYMMETRIC KEY SSIS_EXECUTION_KEY

  FROM FILE = N'C:\DevDatabases\SSIS_EXECUTION_KEY.SNK'

  ENCRYPTION BY PASSWORD = N'ADFB$W%&SJE%^dxme56';

  Create the sproc to execute the SSIS package:

  CREATE PROCEDURE usp_execute_package…

  Sign the sproc:

  ADD SIGNATURE TO usp_execute_package BY ASYMMETRIC KEY SSIS_EXECUTION_KEY