Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Permission denied on the object sp_send_dbmail (but, not what you think) Expand / Collapse
Author
Message
Posted Friday, May 18, 2012 12:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:18 AM
Points: 34, Visits: 256
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.
Post #1302747
Posted Friday, May 18, 2012 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 11:58 AM
Points: 114, Visits: 999
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

Post #1302767
Posted Friday, May 18, 2012 1:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:18 AM
Points: 34, Visits: 256
Nah, no DENY on sp_send_dbmail...
Post #1302778
Posted Friday, May 18, 2012 1:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 11:58 AM
Points: 114, Visits: 999
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'

Post #1302800
Posted Monday, May 21, 2012 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:18 AM
Points: 34, Visits: 256
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?
Post #1303394
Posted Monday, May 21, 2012 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1303410
Posted Monday, May 21, 2012 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 12,962, Visits: 32,501
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1303414
Posted Monday, May 21, 2012 9:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:18 AM
Points: 34, Visits: 256
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.
Post #1303444
Posted Monday, May 21, 2012 9:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #1303471
Posted Wednesday, December 19, 2012 7:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 69, Visits: 496
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
'
Post #1398424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse