0

I am trying to create a SQL Server Agent job from simple windows console application but I'm receiving an The EXECUTE permission was denied on the object 'sp_add_operator', database 'msdb', schema 'dbo'. error. Steps are, define & create operator, job, job steps & job schedule. I am receiving an error on creating operator step. I've checked into SQL where I can see that access are granted to me but not sure why I am getting this error.

Code:

static void Main(string[] args)
{
    Server srv = new Server();
    srv.ConnectionContext.ConnectionString = "MyConnectionString";
    srv.ConnectionContext.Connect();

    Operator op = new Operator(srv.JobServer, "Test_Operator");
    op.NetSendAddress = "Network1_PC";
    op.Create(); // Error

    Job jb = new Job(srv.JobServer, "Test_Job");
    jb.OperatorToNetSend = "Test_Operator";
    jb.NetSendLevel = CompletionAction.Always;
    jb.Create();

    JobStep jbstp = new JobStep(jb, "Test_Job_Step");
    jbstp.Command = "Test_StoredProc";
    jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
    jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;
    jbstp.Create();

    //Define a JobSchedule object variable by supplying the parent job and name arguments in the constructor. 
    JobSchedule jbsch = new JobSchedule(jb, "Test_Job_Schedule");
    jbsch.FrequencyTypes = FrequencyTypes.Daily;
    jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Minute;
    jbsch.FrequencySubDayInterval = 30;
    TimeSpan ts1 = new TimeSpan(9, 0, 0);
    jbsch.ActiveStartTimeOfDay = ts1;

    TimeSpan ts2 = new TimeSpan(17, 0, 0);
    jbsch.ActiveEndTimeOfDay = ts2;
    jbsch.FrequencyInterval = 1;

    System.DateTime d = new System.DateTime(2003, 1, 1);
    jbsch.ActiveStartDate = d;

    jbsch.Create();
}

Error:

Microsoft.SqlServer.Management.Smo.FailedOperationException was unhandled
  HelpLink=http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Operator&LinkId=20476
  HResult=-2146233088
  Message=Create failed for Operator 'Test_Operator'. 
  Source=Microsoft.SqlServer.Smo
  Operation=Create
  StackTrace:
       at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
       at Microsoft.SqlServer.Management.Smo.Agent.Operator.Create()
       at JobUtilityTester.Program.Main(String[] args) in c:\Personal\Sample Projects\SQLServerJobUtility\JobUtilityTester\Program.cs:line 56
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: Microsoft.SqlServer.Management.Common.ExecutionFailureException
       HResult=-2146233087
       Message=An exception occurred while executing a Transact-SQL statement or batch.
       Source=Microsoft.SqlServer.ConnectionInfo
       StackTrace:
            at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
            at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
            at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
            at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
            at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
            at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
       InnerException: System.Data.SqlClient.SqlException
            HResult=-2146232060
            **Message=The EXECUTE permission was denied on the object 'sp_add_operator', database 'msdb', schema 'dbo'.**
            Source=.Net SqlClient Data Provider
            ErrorCode=-2146232060
            Class=14
            LineNumber=1
            Number=229
            Procedure=sp_add_operator
            Server=10.1.201.164
            State=5
            StackTrace:
                 at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
                 at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
            InnerException:

Screen Shots :

enter image description here

GThree
  • 2,708
  • 7
  • 34
  • 67
  • And have you tried manually creating a job from within Management Studio? Do you connect to the database from within Mgmt Studio using the same credentials that your program uses to connect? – Jim Mischel Jan 29 '16 at 20:35
  • The 'aspuser' sql login is the one which requires execute permssions on the sp_add_operator sp. p.s. your password is also visible in the screenshot. – benni_mac_b Jan 29 '16 at 20:37
  • Yes. I tried both approaches and it worked. – GThree Jan 29 '16 at 20:37
  • Did you try `GRANT EXECUTE ON sp_add_operator TO SQLAgentUserrole` – Conrad Frix Jan 29 '16 at 20:40
  • `USE msdb; GRANT EXEC ON sp_add_operator TO PUBLIC`. This works for me. Thank you all for your suggestions. – GThree Jan 29 '16 at 20:53
  • 1
    @Robinhood - that will work as you are saying anyone can execute that sp - probably not a good idea in a live environment. – benni_mac_b Jan 29 '16 at 21:00

0 Answers0