none
EXECUTE permission was denied on the object ‘sp_send_dbmail’,database’msdb’,schema’dbo’

    Question

  • Hiya,

    Have created a trigger which fires an email if a User records data incorrectly (in a business sense). I know this

    is a not generally an adviseable technique but analysis of occurrences over last 12 months shows that its incidence

    is relatively rare and we want the User to be notified instantaneously.

    Anyhow, I have tested recording data incorrectly and the trigger fires and sends me an email. However, when I went

    live a user contacted the Helpdesk stating was getting following error:

    EXECUTE permission was denied on the object 'sp_send_dbmail’,database’msdb’,schema’dbo’

    This has perplexed me as domain users are users in the msdb database and have been made a member of the

    DatabaseMailUserRole database role in the msdb database.

    Does anyone have any idea why user is still getting that error message?

    The trigger code is:

    /****** Object:  Trigger [dbo].[Incorrect_AppDate_Data]    Script Date: 10/21/2011 16:01:02 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER TRIGGER [dbo].[Incorrect_AppDate_Data] ON [dbo].[TAT_TIME] AFTER INSERT 
    
    AS
    
    DECLARE @ApptDate datetime, 
    @TranDate datetime, 
    @Action_Code varchar(6), 
    @Clnt_Matt_Code varchar(20)
    
    SELECT @ApptDate = m._Appointment_date, @TranDate = INSERTED.tran_date, 
    @Action_Code = INSERTED.Action_Code, @Clnt_Matt_Code = m.clnt_matt_code
    FROM INSERTED 
    INNER JOIN 
    HBM_matter m
    ON m.matter_uno = INSERTED.Matter_uno 
    
    IF @TranDate < @ApptDate AND (@Action_Code <> '1PRE' or @Clnt_Matt_Code NOT LIKE '%PRE%')
    
    BEGIN 
    
    DECLARE @email varchar(100),
    @msg varchar(1500),
    @Case_Code varchar(15),
    @Matter_Uno INT,
    @tran_date DATETIME,
    @Action_Desc VARCHAR(50),
    @Rank_Desc VARCHAR(50),
    @tobill_hrs MONEY, 
    @tobill_amt MONEY,
    @Base_rate MONEY,
    @Narrative VARCHAR(250),
    @Matter_name VARCHAR(20),
    @Client_name VARCHAR(50),
    @Appointment_date DATETIME
    
    SELECT @email = p.email, @Case_Code = m.client_code, @Matter_Uno = m.matter_uno, @tran_date = t.tran_date, 
    @Action_Desc = a.action_desc, @Rank_Desc = r.rank_desc, @tobill_hrs = t.tobill_hrs, @tobill_amt = t.tobill_amt, 
    @Base_rate = (t.tobill_amt / nullif(t.tobill_hrs, 0)), @Narrative = tx.txt1, @Matter_name = m.matter_name, 
    
    @Client_name = c.client_name, 
    @Appointment_date = m._appointment_date
    FROM dbo.HBM_Persnl p
    INNER JOIN INSERTED t ON p.empl_uno = t.entry_empl_uno
    INNER JOIN HBM_matter m ON m.matter_uno = t.Matter_uno
    INNER JOIN tbl_rank r ON t.rank_code = r.rank_code
    INNER JOIN tal_action a ON a.action_code = t.action_code
    INNER JOIN tat_text tx ON t.nar_text_id = tx.text_id
    INNER JOIN HBM_Client c ON c.client_uno = m.client_uno
    WHERE m.client_code <> '03RH017'
    and matter_code <> 'LPA'
    
    SET @msg = 'Dear User,
    
    You have recorded time incorrectly on "' + @Case_Code + '".  
    
    The incorrect entry is : Matter Uno: "' + CAST(@Matter_Uno AS VARCHAR(10))+'" ' + 
    					  ' Client Matter Code: "' + @Clnt_matt_code +'" ' + 
    					  ' Transaction Date: "' + CAST(@tran_date AS VARCHAR(20)) + '" ' + 
    					  ' Action Code: "'  + @action_code +'" ' + 
    					  ' Action Description: "' + @Action_Desc +'" ' + 
    					  ' Rank Description: "' + @Rank_Desc +'" ' + 
    					  ' ToBill Hours: "' +  CAST(@tobill_hrs AS VARCHAR(10)) +'" ' + 
    					  ' ToBill Amount: "' + CAST(@tobill_amt AS VARCHAR(10)) +'" ' + 
    					  ' Base Rate: "' + CAST(@Base_rate AS VARCHAR(10)) +'" ' + 
    					  ' Narrative: "' + @Narrative +'" ' + 
    					  ' Matter Name: "' + @Matter_name +'" ' + 
    					  ' Client Name: "' + @Client_name +'" ' + 
    					  ' Appointment Date: "' + CAST(@Appointment_date AS VARCHAR(20)) +'"
    
    The time incorrectly recorded relates to work undertaken in the period prior to appointment and should be recorded 
    
    against the ‘pre-appointment work’ time code and on the ‘pre-appointment file’ in accordance with the firm’s time 
    
    recording policy (see intranet). 
    
    Regards,
    
    Technical & Compliance Team' 
     
    -- CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail 
    @recipients = @email,
    @copy_recipients = 'john.smith@somewhere.com', 
    @body= @msg,  
    @subject = 'Time Error Email', 
    @profile_name = 'Recording_Errors'
    END
    
    ELSE IF   @TranDate > @ApptDate AND (@Action_Code = '1PRE' OR @Clnt_Matt_Code LIKE '%.PRE%')
    
    BEGIN 
    
    DECLARE @email1 varchar(100),
    @msg1 varchar(1500),
    @Case_Code1 varchar(15),
    @Matter_Uno1 INT,
    @Clnt_Matt_Code1 VARCHAR(20), 
    @tran_date1 DATETIME,
    @Action_Code1 varchar(6),
    @Action_Desc1 VARCHAR(50),
    @Rank_Desc1 VARCHAR(50),
    @tobill_hrs1 MONEY, 
    @tobill_amt1 MONEY,
    @Base_rate1 MONEY,
    @Narrative1 VARCHAR(250),
    @Matter_name1 VARCHAR(20),
    @Client_name1 VARCHAR(50),
    @Appointment_date1 DATETIME
    
    SELECT @email1 = p.email, @Case_Code1 = m.client_code, @Matter_Uno1 = m.matter_uno, @Clnt_Matt_Code1 = 
    
    m.clnt_matt_code, @tran_date1 = t.tran_date, 
    @Action_Code1 = t.Action_Code, @Action_Desc1 = a.action_desc, @Rank_Desc1 = r.rank_desc, @tobill_hrs1 = 
    
    t.tobill_hrs, @tobill_amt1 = t.tobill_amt, 
    @Base_rate1 = (t.tobill_amt / nullif(t.tobill_hrs, 0)), @Narrative1 = tx.txt1, @Matter_name1 = m.matter_name, 
    
    @Client_name1 = c.client_name, 
    @Appointment_date1 = m._appointment_date
    FROM dbo.HBM_Persnl p
    INNER JOIN INSERTED t ON p.empl_uno = t.entry_empl_uno
    INNER JOIN HBM_matter m ON m.matter_uno = t.Matter_uno
    INNER JOIN tbl_rank r ON t.rank_code = r.rank_code
    INNER JOIN tal_action a ON a.action_code = t.action_code
    INNER JOIN tat_text tx ON t.nar_text_id = tx.text_id
    INNER JOIN HBM_Client c ON c.client_uno = m.client_uno
    WHERE m.client_code <> '03RH017'
    and matter_code <> 'LPA'
    
    SET @msg1 = 'Dear User,
    
    You have recorded time incorrectly on"' + @Case_Code1+ '".  
    
    The incorrect entry is : Matter Uno: "' + CAST(@Matter_Uno1 AS VARCHAR(10))+'" ' + 
    					  ' Client Matter Code: "' + @Clnt_matt_code1 +'" ' + 
    					  ' Transaction Date: "' + CAST(@tran_date1 AS VARCHAR(20)) + '" ' + 
    					  ' Action Code: "'  + @action_code1 +'" ' + 
    					  ' Action Description: "' + @Action_Desc1 +'" ' + 
    					  ' Rank Description: "' + @Rank_Desc1 +'" ' + 
    					  ' ToBill Hours: "' +  CAST(@tobill_hrs1 AS VARCHAR(10)) +'" ' + 
    					  ' ToBill Amount: "' + CAST(@tobill_amt1 AS VARCHAR(10)) +'" ' + 
    					  ' Base Rate: "' + CAST(@Base_rate1 AS VARCHAR(10)) +'" ' + 
    					  ' Narrative: "' + @Narrative1 +'" ' + 
    					  ' Matter Name: "' + @Matter_name1 +'" ' + 
    					  ' Client Name: "' + @Client_name1 +'" ' + 
    					  ' Appointment Date: "' + CAST(@Appointment_date1 AS VARCHAR(20)) +'"
    
    The time incorrectly recorded relates to work undertaken after the date of appointment and should therefore be 
    
    recorded against a time code other than ‘pre-appointment work’ time code on the appointment file in accordance with 
    
    the firm’s time recording policy (see intranet).
     
    Regards,
    
    Technical & Compliance Team'
    
    EXEC msdb.dbo.sp_send_dbmail 
    @recipients = @email1,
    @copy_recipients = 'john.smith@somewhere.com', 
    @body = @msg1,  
    @subject = 'Time Error Email', 
    @profile_name = 'Recording_Errors'
    END

     


    Cheers, AndySugs
    • Edited by Sugsy Friday, October 21, 2011 4:17 PM
    Friday, October 21, 2011 3:42 PM

Answers

All replies