May 18, 2012 at 12:19 pm
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.
May 18, 2012 at 12:53 pm
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
May 18, 2012 at 1:08 pm
Nah, no DENY on sp_send_dbmail...
May 18, 2012 at 1:32 pm
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'
May 21, 2012 at 8:09 am
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?
May 21, 2012 at 8:19 am
there isnt any sort of impersonation going on is there, like EXECUTE AS LOGIN?
May 21, 2012 at 8:25 am
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
May 21, 2012 at 9:11 am
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 😎 ):
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.
May 21, 2012 at 9:41 am
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.
December 19, 2012 at 7:57 am
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
'
July 1, 2016 at 9:11 am
cunningham (12/19/2012)
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 belowEXEC 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
'
I know this is an old thread but setting the database in the dropdown to 'msdb' fixed the same problem for me. Most of the solutions you find when you try to google this reference most of the items found earlier in this thread.
Also, I have @execute_query_database='MyDatabaseName' added to sp_send_dbmail to insure the database is switched to the location of my stored procedure.
August 21, 2019 at 8:14 pm
Ok, just to add to this topic since this was really good information, but still didn't completely solve my problem.
Make sure the sid for the owner in your DB matches the owner sid in the master DB:
--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'
Even though I had given access to the msdb and execute rights on the sp_send_dbmail, it was still having issues related to the database being untrustworthy and that the owner sids didn't match.
Consequently, I had to the Trustworthy on for the database I was running in and fix the ownership issue:
ALTER DATABASE my_db SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON Database::my_db TO [domain\user];
I had to go through a lot of ferreting around to finally find this write -up which is much more enlightening.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply