MS SQL Server role db_create_dts

/*************************************************************************
** Name: T-SQL_role_db_create_dts.sql
** 
** Parameters: None
** 
** Returns: Nothing
** 
** Pre-requisites: N/A
**
** Command line: N/A
**
** Description: It will create the msdb role db_create_dts. 
**    This role it will allow to each role's member to create, edit 
**    and schedule their own DTSs.
**
** Programmer: Paulo Gonçalves
** Contact: pal_soft@hotmail.com
** Date Written: 2005/04/14
** Version: 1.1
**
** Change History: 
**    version 0.9 - 2005/01/12 - Sandra Faria- Discover the needed SPs. 
**    version 1.0 - 2005/04/05 - Paulo Gonçalves - First writen.
**    version 1.1 - 2005/04/14 - Paulo Gonçalves - More SPs added (before only 12).
**
** Obs.: After running this script it will be need to add the users 
**    or group to the msdb database and to the db_create_dts role.
**
*************************************************************************/
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


USE msdb
GO
 

/**********************************************************************************//****** Object:  DatabaseRole db_create_dts Script Date: 14-04-2005 15:58:03 ******//****** Create db_create_dts msdb's role                                     ******//**********************************************************************************/if not exists (select * from dbo.sysusers where name = N'db_create_dts' and uid > 16399)
EXEC sp_addrole N'db_create_dts'
GO


/*************************************************************************************************//****** Object:  DatabaseRole db_create_dts Script Date: 14-04-2005 15:58:13                ******//****** Grant db_create_dts msdb's role to execute the specified extended stored procedures ******//*************************************************************************************************/GRANT
EXECUTE
ON [dbo].[sp_set_sqlagent_properties]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_has_server_access]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_get_perf_counters]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_get_startup_info]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_notify]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_add_dtspackage]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_drop_dtspackage]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_get_dtspackage]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_enum_dtspackages]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_generate_target_server_job_assignment_sql]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_log_dtspackage_begin]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_log_dtspackage_end]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_log_dtsstep_begin]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_log_dtsstep_end]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_log_dtstask]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_add_jobserver]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_enum_sqlagent_subsystems]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_get_jobstep_db_username]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_delete_jobstep]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_add_jobschedule]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_update_jobschedule]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_add_job]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_update_job]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_delete_job]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_get_job_alerts]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_start_job]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_stop_job]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_get_chunked_jobstep_params]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_check_for_owned_jobs]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_check_for_owned_jobsteps]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_refresh_job]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_log_jobhistory]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_check_msx_version]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_sqlagent_probe_msx]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_addtask]
TO db_create_dts
GRANT
EXECUTE
ON [dbo].[sp_updatetask]
TO db_create_dts
GO