Issue with agent job while taking backup

Question

Issue with agent job while taking backup

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

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

GA_SQL Hall of Fame Points: 3166 More actions · Answer 1

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)

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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]',

GA_SQL Hall of Fame Points: 3166 More actions · Answer 3

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

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

GA_SQL Hall of Fame Points: 3166 More actions · Answer 5

yes sir, email sent in the case SP executed directly

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

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

GA_SQL Hall of Fame Points: 3166 More actions · Answer 7

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'

Sue_H SSC Guru Points: 90860 More actions · Answer 8

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

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

GA_SQL Hall of Fame Points: 3166 More actions · Answer 10

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

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

John

GA_SQL Hall of Fame Points: 3166 More actions · Answer 12

grant execute on sp_send_dbmail to public

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

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

GA_SQL Hall of Fame Points: 3166 More actions · Answer 14

GA_SQL

Hall of Fame

Points: 3166

December 19, 2017 at 4:21 am

#1972654

Ok sir, i will change it

Thanks sir