According to the Selecting an Account for the SQL Server Agent Service, the SQL Server Agent service account should be able to authenticate with the instance and also be a memeber of the sysadmin fixed server role.
Agent jobs are usually created by a DBA and by default will execute in the context of the service account. Implementing the Principle of Least Privilege requires that an alternate account is used to execute each job (or groups of jobs) depending on the functionality required. A job will definitely execute when using a member of the sysadmins server role however this privilige level might be too high for the task in hand. This is especially true when the DBA has to schedule say, a stored procedure written by a database developer or third parties.
SQL Server allows that jobs run in an alternate execution context whose permissions are limited only to those required to execute the job. Creating such a proxy requires the following steps:
Create a domain or local user account with a Strong Password - for the sake of this article a domain user account will be used in the examples.
Create a Login for the Windows account; create a database USer for the Login, and add the User as a member of a database role.
USE [master] GO CREATE LOGIN [DOMAIN\appuser001] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase] GO USE [MyDatabase] GO CREATE USER [DOMAIN\appuser001] FOR LOGIN [DOMAIN\appuser001] GO EXEC sp_addrolemember [CustomDatabaseRole], [DOMAIN\appuser001] GO
Create a Credential for the Login as shown. Note that the WIndows account password is required at this stage.
USE [master] GO IF NOT EXISTS(SELECT * FROM sys.credentials WHERE [name] = 'appuser001') CREATE CREDENTIAL [appuser001] WITH IDENTITY = N'DOMAIN\appuser001', SECRET = N'[email protected]' GO
Create an SQL Server Agent Proxy linking it to the Credential.
USE [msdb] GO EXEC msdb.dbo.sp_add_proxy @proxy_name=N'appuser001', @credential_name=N'appuser001', @enabled=1, @description=N'SQL Server Agent sample proxy account' GO
Finally the Proxy is granted access to a subsystem. More information about subsystems can be found in the sp_grant_proxy_to_subsystem (Transact-SQL) documentation.
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'appuser001', @subsystem_id=11 -- SSIS package execution GO
In the above example the Proxy is being granted the privilige to execute SSIS packages. In addition to this procedure, the Login has to be granted access to the msdb database in order to execute SSIS packages.
USE [msdb] GO CREATE USER [DOMAIN\appuser001] FOR LOGIN [DOMAIN\appuser001] GO EXEC sys.sp_addrolemember @rolename = 'db_dtsoperator', @membername = [DOMAIN\appuser001]; GO
The last step is to choose the Proxy account in the RunAs dropdown when createing the SQL Server Agent job step.