sp_send_dbmail - Error when passing @query parameter.

• dbadotraghu

  SSC-Addicted

  Points: 438

  More actions

  March 15, 2021 at 10:07 pm

  #3858205

  Hello All, looks like this issue is still not marked as "Answered" some of the suggestions are valid but i am leaning towards something related to active directory/ Windows user issue. Reason for my theory is take example from above it throws error when you login as windows user, to troubleshoot further I used 'sa'  and ran the same query and works great. so the question is why not a windows login and what's preventing it from using @query parameter. if any one found a solution please post it here.

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'MyProfile'

  ,@recipients = 'myemail@mycompany.com'

  ,@subject = 'test'

  ,@Body = 'test message'

  ,@query= 'SELECT 1'

• Lowell

  SSC Guru

  Points: 323522

  More actions

  March 16, 2021 at 2:49 am

  #3858292

  i suspect you are missing two pieces of the puzzle.

  to use database mail, any non-sysadmin active directory user (or more preferably, the AD group they belong to, have to be added as users in the msdb database, and added to a specific role that exists in the msdb database: the role DatabaseMailUserRole

  additionally, you need to grant the user access to the specific profile in one of two ways:

  make the role public, so any user in that role has access, or grant the user permission to  restricted private profile, for  just that AD group.

  Configure DatabaseMail>>Manage profile Security>>Private Profiles>>Click Grid with Existing Profile>>Select Available user From DropDown List and Finish

  you can easily test a specific AD users permissions like this:

  EXECUTE AS LOGIN = 'MyDomain\dbadotraghu'
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'MyProfile'
  ,@recipients = 'myemail@mycompany.com'
  ,@subject = 'test'
  ,@Body = 'test message'
  ,@query= 'SELECT top 3 * from master.sys.objects';
  REVERT --change back to myself

  

  

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!