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

• Friday, October 21, 2011 3:42 PM

   

   
  

  0

  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
  •  
  • Reply
  • Quote