- When you execute a job and change the data sources the information is NOT stored and the next execution resets the connection information. Using a config file is definitely the way to go :-)
- Proxy accounts with the public role assigned to it have a problem removing this role.
CREATE CREDENTIAL [myCredentials] WITH IDENTITY = N'svcaccount',
SECRET = N'xxxxxxxxx'
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'myProxy',
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'myProxy',
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'myProxy',
EXEC msdb.dbo.sp_revoke_login_from_proxy @proxy_name=N'myProxy',
This results in the following error message: "Msg 14523, Level 16,
State 1, Procedure sp_revoke_login_from_proxy, Line 63 "public" has not been granted permission to use proxy "myProxy"."
Also notice the @enabled parameter in the sp_add_proxy call. We can't find this in the interface but when this parameter is set to 0 the proxy is disabled. The problem is that you CAN select
disabled proxies as the account to run the job with but the job fails on execution.