Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.


Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.


  1. Change Job_Owner to Sa, If its not possible then you have to try 2nd option Create proxy account to execute SSIS package
  2.  Create Proxy account for SSIS package and assign same while executing job.



USE MASTER
GO
CREATE CREDENTIAL [SSISProxyCredentials]
WITH IDENTITY = N'Any Sysadmin Domain Account',
SECRET = N'Password'
GO
-----------------------------------------------------

USE msdb
GO
--Drop the proxy if it is already existing
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxy')
BEGIN
EXEC dbo.sp_delete_proxy
@proxy_name = N'SSISProxy'
END
GO
--Create a proxy and use the same credential as created above
EXEC msdb.dbo.sp_add_proxy
@proxy_name = N'SSISProxy',
@credential_name=N'SSISProxyCredentials',
@enabled=1
GO
--To enable or disable you can use this command
EXEC msdb.dbo.sp_update_proxy
@proxy_name = N'SSISProxy',
@enabled = 1 --@enabled = 0
GO
---------------------------------------------------------
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'SSISProxy',
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image
GO
--View all the proxies granted to all the subsystems
EXEC dbo.sp_enum_proxy_for_subsystem

------------------------------------------------------
USE msdb
GO
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are not allowed to use any proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'SSISProxy'
,@login_name=N'Any nonsysadmin account'
GO
--View logins provided access to proxies
EXEC dbo.sp_enum_login_for_proxy
GO
------------------------------------------------------
--EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSISPackageCall',
--@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'SSIS',
--@command=N'/FILE "C:\Package.dtsx" /CHECKPOINTING OFF /REPORTING E',
--@database_name=N'master',
--@flags=0,
--@proxy_name = N'SSISProxyDemo';

1 comment: