sp_send_dbmail - Error when passing @query parameter.

Question

sp_send_dbmail - Error when passing @query parameter.

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

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

dbadotraghu SSC-Addicted Points: 438 More actions · Answer 1

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: 323518 More actions · Answer 2

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!