SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Permission denied on the object sp_send_dbmail (but, not what you think)


Permission denied on the object sp_send_dbmail (but, not what you think)

Author
Message
sickpup
sickpup
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 267
OK, so I'm trying to send an e-mail from within a job, using sp_send_dbmail. The job is pretty simple - just query a table and send the results as an attachment in an e-mail.

The job works on all other servers except for one.

On this particular server, I get the following error:

Message
Executed as user: COMPANY\xxxxxxxxx. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.


So, I've tried granting EXEC on sp_send_dbmail. I've tried sp_addrolemember to DatabaseMailUserRole. I've tried setting trustworthy ON.

Basically, everything I've found online has been tried.

It's driving me crazy... am I missing something simple?

Thanks.
Daxesh Patel
Daxesh Patel
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 1047
Can you run following under msdb and see if you find DENY on sp_send_dbmail and
GranteeName is not the user or any associates role?

If you have GRANT and DENY, DENY will take precedence

SELECT
dp.class_desc, dp.permission_name, dp.state_desc,
ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id


sickpup
sickpup
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 267
Nah, no DENY on sp_send_dbmail...
Daxesh Patel
Daxesh Patel
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 1047
Another possibility, you do not have permission to use mail profile?
according to http://msdn.microsoft.com/en-us/library/ms190307.aspx

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.



To grant permission
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Email_Profile',
@principal_name = 'UserORrole'


sickpup
sickpup
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 267
Still no luck...

I ran:

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQL Mail',
@principal_name = 'COMPANY\xxxxxxxx',
@is_default = 0 ;


And got the same error.

Anything else I might be missing?
anthony.green
anthony.green
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22884 Visits: 6519
there isnt any sort of impersonation going on is there, like EXECUTE AS LOGIN?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67387 Visits: 40891
since it's just this one server,
could it be something like database mail is not emabled, even though you've created the profile?

it sounds like maybe you scripted the database mail settings out and ran them, but not the configure?


http://msdn.microsoft.com/en-us/library/ms191189.aspx


use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go




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!
sickpup
sickpup
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 267
OK... I figured it out... and it was ridiculously simple.

As I mentioned, the query that is being run is pretty straight-forward and simple. Here's a slightly modified version (to protect super-secret company info Cool ):

declare @BodyText nvarchar(max)

SET @BodyText = 'Attached is the quarterly audit.

Regards,

SQL Administrators'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients='joe.schmoe@company.com; billy.bob@company.com',
@copy_recipients='admins@company.com',
@subject='Quarterly Audit',
@body=@BodyText,
@query='USE Product_Database
SELECT SUBSTRING(description, 1, 30) as Name,
CASE
WHEN privilege = ''1''
THEN (''Admin'')
WHEN privilege = ''0''
THEN (''System User'')
ELSE ''''
END AS ''Role''
FROM [dbo].[user_table]
WHERE enabled = ''1''
AND item_type = ''7765''
ORDER BY Role'
,@attach_query_result_as_file = 1
,@query_attachment_filename = 'User_Accts.txt'
,@exclude_query_output = 1;



So, the key here is the 'USE Product_Database' line. In the Job Step Properties page, I was leaving the "Database:" option as "master", which is what it defaults to. I figured that was fine, since the code explicitly states to use the necessary database.

On all the other servers, this worked without any problems. On this particular server, though, I had to switch the "Database:" option to Product_Database, even though it seems a bit redundant since the code does so as well.

Regardless, it worked like a charm. My guess is that it's some esoteric permission issue of some type, and since I call sp_send_dbmail before I switch to the Product_Database, that's what is causing the problem.

Anyhow, thanks for the help, everyone.
mtassin
mtassin
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7020 Visits: 72521
I think you could have also used

@execute_query_database = Product_Database


instead. it would have caused the sp_send_dbmail to change the database before executing the query.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
cunningham
cunningham
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 856
Thanks for this, put me on the right track. but I had to change the database in the drop down to MSDB rather than master or any other. my query just called fully qualified stored proc names. similar to below

EXEC msdb.dbo.sp_send_dbmail @profile_name='mickey profile',
@recipients='mickey.mouse@disney.com;',
@subject='Subject of email',
@query= '
print ''Description of email''
print ''''
exec DBNAME.dbo.SPName
print ''''
print ''---------''
etc
etc
'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search