locked
sp_send_dbmail - Getting error for permissions RRS feed

  • Question

  • 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)'
    Friday, July 17, 2020 2:28 PM