Problems Sending DB Mail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Problems Sending DB Mail

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2006-09-07 : 10:29:57

I'm stuck on a problem using the DB Send mail in SS2005.

==== Some pre-information, see further down for main question ====

Our ERP front end software updates a field in a table when a part is completed.. I've setup a trigger to send an email when this table is updated with critera I've set (When the part is complete, send some emails..) This worked 100% fine in SS 2000.

I've set up the mail profile and account, and I am able to send emails using management studio under the ERP's SYSADM login (the ERP sets up its rights).. I am able to login to management studio under any login, and also send mail properly. INFACT - I've just mimicked what the ERP system does, I've ran an update statement on the table to "complete" the part, and the email runs fine under any login.

However.. when I log into the ERP system as any login besides SYSADM.. I get this error when attempting to complete the part,


EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I am able to get "Details" on the error, that gives me this - 


select SYSDATETIME from APPLICATION_GLOBAL
into :dtCurrentTime

ERROR Number: 229

Microsoft SQL Server: No additional information available.  Backend specific error.

===== Above is pre-information ====

Those errors are from the ERP front end when attempting to run the update statement (which I can run in Studio). I feel my error is coming from some security settings not set up right, I need help figuring out *where* these are.

I've manually checked security settings for the users to match this SYSADM account to my SHOP account.

SYSADM details - Security -> Logins


Server Roles : sysadmin, security admin
User mapping : msdb - SYSADM (user) - dbo (default schema)
Click on msdb for roles: DatabaseMailUserRole, public

SHOP details - Security -> Logins


Server Roles : sysadmin, security admin
User mapping : msdb - SHOP(user) - DatabaseMailUserRole (default schema.. I've tried dbo here also)
Click on msdb for roles: DatabaseMailUserRole, public, db_owner

SYSADM Details - Databases > msdb > security > users


Default Schema: dbo
Owned Schemas: none
Database Role Membership: DatabaseMailUserRole
No Securables or Extended Properties..

SHOP Details - Databases > msdb > security > users


Default Schema: DatabaseMailUserRole
Owned Schemas: db_owner (not checked, but filled box)
Database Role Membership: DatabaseMailUserRole, db_owner
No Securables or Extended Properties..

DBO Schema - Databases > msdb > security > Schemas


General, schema owner: dbo
Permissions, users or roles: DatabaeMailUserRole
-> Explicit permissions = all checked

It would seem to me that SHOP has permissions, infact even more permissions than SYSADM.. why am I getting this error when trying to send mail as SHOP?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-07 : 15:20:33

have a look at this http://msdn2.microsoft.com/en-us/library/ms188719.aspx

-ec

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2006-09-12 : 15:11:48

If you read my whole post, you'd see I've done all of that.
Databases > msdb > security > users
Right click user SHOP

Default Schema: DatabaseMailUserRole
Owned Schemas: db_owner (not checked, but filled box)
Database Role Membership: DatabaseMailUserRole, db_owner
No Securables or Extended Properties..

SHOP is already a member of the msdb, and is in the role that the link talks about.

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2006-09-21 : 13:50:01

Still having problems with this, need advice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources