Granting SSIS Package Administration Access to Developers

In many instances there is a development team or even a warehouse team that needs to run SSIS packages and you want to store those packages in SQL Server and run them through the SQL Agent Service.  You may not; however, want to grant those users/group sysadmin rights to the machine.  The following describes my experience in setting this up.

SSIS_DB1First, you must add the user/group to the DCOM Configuration for “Microsoft SQL Server Integration Services 11.0” (SQL 2012), which can be found in the Component Services MMC.  Right click and choose properties.

 

 

SSIS_DB2You can now add the groups to two security sections–“Launch and Activation Permissions” and “Access Permissions”.  It was my experience I had to have them in both groups.  I granted full rights in each group.

The final component is to add each user/group to the local group “Distributed COM Users”.  It seems like it won’t even authenticate a user for the groups you set earlier unless they are in the local group.  This will allow users to connect the Integration Services Instance.SSIS_DB3

At this point you should restart the Integration Service services.

If you haven’t already, you can go ahead and create your Catalog Database.  We won’t go over those steps here–perhaps in another post.

The last step is to allow them to deploy and manage the packages on the servers.  I add the group to the SSISDB and grant the role of ssis_admin.  Now your teams should be able to function completely within the SSIS parameters and not have permissions to drop database objects or modify security settings.

SSIS_DB4

Sizing the SQL Server Error Log

Sometimes SQL Server generates so many messages you think it actually enjoys it.  While there may be some other factors in creating a new log by default, it appears to me the most common scenario is when SQL Server restarts.  If your systems are anything like mine, you want SQL Server to stay up and this can cause the logs to get very large and this makes finding specific message more painful than it needs to be.

To me it makes sense to separate the logs by day. EXEC sp_cycle_errorlog will create a new SQL Server error log.  I normally create a job that runs a midnight and creates a new error file.  By default, SQL Server only keeps 6 error log files and I normally increase that number to 30.

Files To Keep
If the server has already been set up, I normally use the GUI by right clicking on SQL Server Logs and choose configure. If I am setting up the server, I have this piece of code in my scripts.Screen Shot 08-01-14 at 01.59 PM

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
GO

Cycling the Logs
This code will create a new job to run at midnight and cycle the logs.


USE [msdb]
GO

/****** Object:  Job [Cycle Error Log]    Script Date: 08/01/2014 13:31:39 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/01/2014 13:31:39 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Cycle Error Log',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Cycle Error Log]    Script Date: 08/01/2014 13:31:40 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle Error Log',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'EXEC sp_cycle_errorlog',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Midnight Ride',
  @enabled=1,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20140801,
  @active_end_date=99991231,
  @active_start_time=0,
  @active_end_time=235959
 -- @schedule_uid=N'bca9d15e-e47f-40a4-a236-e14020f77b76'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO