In the good old days of SQL Server, all we had to do was make our service account a member of the local administrators group and we were all set. Of course, those days have gone and that practice is now rightly regarded as a security risk of elevated proportions. The SQL Server setup.exe will now give your ordinary domain user account the bare minimum permissions in order to run the service – nothing more, nothing less. This is good practice but it has its own implications.
For instance, running something as simple as the Copy Database Wizard may be a challenge. The CDW allows you to copy a database from one SQL Server to another along with associated Logins. It generates a Job that executes an SSIS package that, by default, uses the SQL Server Agent Service Account for its security context. Now those minimum permissions generated by setup.exe are no longer enough to do the job, excuse the pun.
One solution would be to elevate the permissions of the service account but that would introduce those same old security vulnerabilities just so a single job could succeed. That is not really an option. Thankfully there is a more elegant solution, namely using Proxy and Credential objects.
A Credential allows you to define a Windows account with a certain permission set that you want to map to a SQL Server Login. This effectively gives additional permissions to the login. A Credential can also be used to give permissions to a SQL Server Agent Proxy. The Proxy can then be used instead of the SQL Server Agent Service Account when running, for instance, an SSIS package in a Job.
So in our CDW case, we could create a Credential (under SSMS Security folder) that identifies an account that does have the required permissions.
Then create a Proxy (under SQL Server Agent Proxies folder), and make sure it references the Credential you created earlier. A Proxy can be defined for many types of operations, in our case for SSIS Package Execution.
Finally, when the CDW prompts for the Integration Services Proxy account, you can use the drop-down to select your new Proxy and the Job will succeed instead of getting access-denied errors. This strategy can also be used for any Job which executes an SSIS package that requires temporary elevated permissions.
So you get to run your Job successfully while leaving the service account to do its job – securely.