Post reply

Issue with agent job while taking backup

  • December 18, 2017 at 7:20 am

    #1972497

    In my Code where is the 3 parts.. i am referring only 2 parts , please help me in understanding in my code where i mentioned 3 parts

    Server 2 is 2016(where the code is not working)

    Server 1 is 2012 (where is code is working)

  • December 18, 2017 at 7:34 am

    #1972499

    GA_SQL - Monday, December 18, 2017 7:20 AM

    In my Code where is the 3 parts.. i am referring only 2 parts , please help me in understanding in my code where i mentioned 3 parts

    Server 2 is 2016(where the code is not working)

    Server 1 is 2012 (where is code is working)

    Go check all the objects on Server1 where it is working, check the databases they live in, jobs how they execute, syntax etc.

    Go check all the objects on Server2 where it is not working, make the necessary changes on Server2 so that all the objects are like that on Server1, then they are like for like, as you know Server1 works.

    The 3 part naming will be changing
    @query='EXECUTE [dbo].[DatabaseBackup_Differential]',
    to
    @query='EXECUTE [master].[dbo].[DatabaseBackup_Differential]',

  • December 18, 2017 at 9:15 am

    #1972531

    I found the issue, Executed as user (shown in the view history) ,The EXECUTE permission was denied on the object 'sp_send_dbmail'  [SQLSTATE 42000] (Error 229).

    i have given  execute permission also   

  • December 18, 2017 at 9:23 am

    #1972534

    GA_SQL - Monday, December 18, 2017 9:15 AM

    The EXECUTE permission was denied on the object 'sp_send_dbmail'  [SQLSTATE 42000] (Error 229).

    That's strange.  You said in your first post that the e-mail was sent?

    John

  • December 18, 2017 at 9:28 am

    #1972536

    yes sir, email sent in the case SP executed directly

  • December 18, 2017 at 9:34 am

    #1972540

    GA_SQL - Monday, December 18, 2017 9:28 AM

    yes sir, email sent in the case SP executed directly

    So is this resolved?  Because if the e-mail was sent, then lack of permissions on sp_send_dbmail can't have been the problem.

    John

  • December 18, 2017 at 10:03 am

    #1972546

    no not yet resolved

    NAME    PERMISSION_NAME    STATE_DESC
    TargetServersRole    EXECUTE    GRANT
    SQLAgentUserRole    EXECUTE    GRANT
    DatabaseMailUserRole    EXECUTE    GRANT
    Email    EXECUTE    GRANT

    i have given all the permission still the same error

    The EXECUTE permission was denied on the object 'sp_send_dbmail'

  • December 18, 2017 at 1:55 pm

    #1972587

    GA_SQL - Monday, December 18, 2017 9:15 AM

    I found the issue, Executed as user (shown in the view history) ,The EXECUTE permission was denied on the object 'sp_send_dbmail'  [SQLSTATE 42000] (Error 229).

    i have given  execute permission also   

    And does that user have access to the mail Profile being used? I'd compare them between the two servers as well as the logins executing the job.

    Sue

  • December 19, 2017 at 1:12 am

    #1972635

    GA_SQL - Monday, December 18, 2017 10:03 AM

    The EXECUTE permission was denied on the object 'sp_send_dbmail'

    And do you get that error whether it runs from the job or whether you run it yourself?

    John

  • December 19, 2017 at 2:49 am

    #1972642

    No sir,still is not working has agent job , access issue for sp_send_dbmail

  • December 19, 2017 at 2:58 am

    #1972645

    Then it's your SQL Server Agent service account that needs to have permission to run the stored procedure.

    John

  • December 19, 2017 at 3:59 am

    #1972647

    grant execute on sp_send_dbmail to public 

    now i am able to get emails...thanks for your help sir

  • December 19, 2017 at 4:15 am

    #1972651

    I would advise you not to grant access to public.  That means that anyone who can get on the server can run the stored procedure.  Grant access only to the users that need it.

    John

  • December 19, 2017 at 4:21 am

    #1972654

    Ok sir, i will change it

    Thanks sir

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply