sp_send_dbmail - Error when passing @query parameter.

  • 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'

  • 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!

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply