Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sp_send_dbmail permissions issue

Status
Not open for further replies.

Ikatiemarie

Technical User
Feb 24, 2020
5
US
I am beating my head against a wall, I am trying to set up a job to run and produce an xls to send to certain members.
If I take the "--" away from "Use DBName" and "Execute as user" I get a MSG 229,Lvl 14,State5,Procedure sp_send_DBmail Line 1 - Execute permission was denied on object
If I put in the "--" for "Use DBName" and keep "Execute as user" I get a MSG 15517,Lvl 16,State 1, Line 2 - Cannot execute as the db principal because the "" doesn't exsist.
I've tried changing/adding/permissions in all dbs, unchecking collation in OE, Checking the SIDs match and I feel I am at a loss. Someone help
Below is my query:
--Use DBNAME
--Execute as user = 'DOMAIN\svc_ACCTNAME'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBNAME@ABC.com',
@recipients = 'Name@ABC.com',
@subject = 'Report',
@body='testmail',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'REPORTNAME.xls',
--DECLARE @msg VARCHAR(250);
--declare @query varchar(2048);
--SELECT @msg = 'Please refer to the attached spread sheet for the report.';
@query='Use DBNAME
SELECT "JobOperations"."jmoProcessID" as "Resource ID",
"JobOperations"."jmoProcessShortDescription" as "Resource Description",
"Employees"."lmeEmployeeName" as "Employee Name",
"Jobs"."jmpPartID" as "Part ID",
"Jobs"."jmpJobID" as "Job ID",
"JobOperations"."jmoJobOperationID" as "Seq",
"Jobs"."jmpOrderQuantity" as "WO QTY",
"JobOperations"."jmoQuantityComplete" as "LBR QTY",
("JobOperations"."jmoOperationQuantity" - "JobOperations"."jmoQuantityComplete") as "QTY Delt",
"JobOperations"."jmoEstimatedProductionHours" as "Standard Run Hours",
"JobOperations"."jmoActualProductionHours" as "Actual Hours"
FROM (("DBNAME"."dbo"."Jobs" "Jobs"
LEFT OUTER JOIN "DBAME"."dbo"."JobOperations" "JobOperations" ON "Jobs"."jmpJobID"="JobOperations"."jmoJobID")
LEFT OUTER JOIN "DBNAME"."dbo"."TimecardLines" "TimecardLines" ON (("JobOperations"."jmoJobID"="TimecardLines"."lmlJobID") AND ("JobOperations"."jmoProcessID"="TimecardLines"."lmlProcessID")) AND ("JobOperations"."jmoWorkCenterID"="TimecardLines"."lmlWorkCenterID"))
LEFT OUTER JOIN "DNAME"."dbo"."Employees" "Employees" ON "TimecardLines"."lmlEmployeeID"="Employees"."lmeEmployeeID"
WHERE "timecardlines"."lmlCreatedDate" <= DATEADD(day, DATEDIFF(dd, 0, getdate()-7), 0) AND
"timecardlines"."lmlCreatedDate" > DATEADD(dd, DATEDIFF(dd, 0, getdate()-14), 0)'
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top