Here are some of the errors that we ran into while configuring our SQL Server Agent Jobs to run with an Account that has streamlined permission sets.
Error – Script does not destroy all the objects
The command script does not destroy all the objects that it creates. Revise the command script
Look through your code and please make sure that you have corresponding release statements for all your create objects.
We had a lone createActiveXObject and we forgot the corresponding set object to Nothing.
Error – Error authenticating proxy …. The user name or password is incorrect
Unable to start execution of step 1 (reason: Error authenticating proxy … system error: The user or password is incorrect)
Specify valid user credentials for your credential
USE [master] GO ALTER CREDENTIAL [credentialBISSQL] WITH IDENTITY = N'HRDBMirr\BISDBSQLSvc' , SECRET = N'786544' GO
Error – Proxy is not allowed for subsystem “ActiveScripting”
Proxy (37) is not allowed for subsystem "ActiveScripting" and user "LABDB\DBSQLSvc". Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy. (.Net SqlClient Data Provider)
In our case we already granted our proxy access to the ActiveScripting subsystem.
Additionally, we needed to grant access our login access to the Proxy, as well.
USE msdb ; GO declare @loginName sysname declare @proxy sysname declare @commit bit set @loginName = 'LABDB\BISDBSQLSvc' set @proxy = 'proxyBISSQL' EXEC dbo.sp_grant_login_to_proxy @login_name = @loginName , @proxy_name = @proxy ;
Error – ActiveScripting
Executed as user. Error code: 0. Error Source = Microsoft VBScript runtime error. Error Description: Permission Denied. Error on line 89.
While running our ActiveXObject Script, the system ran into a couple of potholes.
In our case, it was File System permissions.
File System – Permission – Original
Here is our original File System Permission set
File System – Permission – Revised
- As the folder is a log folder, we granted write full permissions to it.
- A more careful Admin will likely only grant create, modify, list folder contents, and Read