Oh Noes! I have a job that requires me to run an SSIS package, cmdshell script, etc. And I need them to have specific file level permissions! What do I do! I know I need to run my job/job step as a specific AD/Windows user but I don’t see how to do that.

It’s not all that hard really. You just need to associate a proxy with the job step.
So I need a proxy?

Yes. You need to create a proxy. And you’ll need to associate it with the type of job step you’re using.
Uh hu

Of course to create a proxy you need a credential.
Ok, you’ve completely lost me. What’s a credential?

And a proxy. What’s a proxy?

Oh, and how do I associate a proxy with a job step???

Let’s start with a credential.

Per BOL:

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.

There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.

In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.

-- Code to create the credential
CREATE CREDENTIAL MyCredential
WITH IDENTITY = 'Kenneth-Laptop\Dopey'
     , SECRET = 'StrongPassword'

Now that we have a credential we can create the proxy.
Hey, definitions first! What’s a proxy!

Per BOL

A SQL Server Agent proxy manages security for job steps that involve subsystems other than the Transact-SQL subsystem. Each proxy corresponds to a security credential. A proxy may have access to any number of subsystems.

If it helps, subsystems include such things as CmdExec and SSIS. The different types of job steps.

Now can we create the proxy?
Sure

-- Code to create a proxy
USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MyProxy',@credential_name=N'MyCredential', 
@enabled=1
GO
-- subsystem 3 is CmdExec
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11
GO
-- subsystem 11 is SSIS
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11
GO

Now that we have a proxy we can assign it to the appropriate job step.

Now if the person creating the job isn’t sysadmin then they will have to be granted permission to the proxy.

-- Code to add permissions for a proxy
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'MyProxy', @login_name=N'Kenneth-Laptop\Doc'
GO

Filed under: Microsoft SQL Server, Security, SQL Agent Jobs, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, security, SQL Agent Jobs, SSMS