sql 2008 dbmail

[tdrclan]

our DBmail app was working up until two weeks ago.

not sure what caused it.
--
the unsent queue is load with the 10 emails that did not get sent.
SELECT * FROM msdb.dbo.sysmail_unsentitems
go

---
db mail is started
USE msdb;
go
EXEC sysmail_help_status_sp;
go


---
broker is active (1)
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
go

--
the last time the msg
"databasemail process is started "
was on 1/20 four days before it stopped working.

---
I've
1) Check the permission
2) recreated the email account and profile via studio mgr.
3) I've checked and open the permission to the msdb for the admin account (as far as I can tell)
4) ran a trace via the profiler and I'm getting the msg
" exec dbo.sysmail_logmailevent_sp @event_type=2,@description=N'The read on the database failed. Reason: The EXECUTE permission was denied on the object ''sp_readrequest'', database ''msdb'', schema ''dbo''.
The EXECUTE permission was denied on the object ''sp_readrequest'', database ''msdb'', schema ''dbo''.',@process_id=5444"
--- see step 3 - granted exec permission ---
5) admin is part of the sysadmin and databasemailuserrole groups.
6) restartedthe service

what am I missing?
would AD cause a issue on the sql server?
do I need to restart the server? ( due to AD security changes)

TIA
Tim

 

[mrdenny]

Restarting the instance may fix the problem. That would be a good start. Run another trace and see what errors are coming up after restarting it.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[tdrclan]

I rebooted the server and same error:

here is the trace info
event 1:
declare @mailid int

EXECUTE [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Omega DBmail'
,@recipients = 'tdr@test.com'
,@body = 'This is a test e-mail sent from Database Mail on OMEGA\OMEGA.'
,@subject = 'Database Mail Test'
,@mailitem_id = @mailid OUTPUT

select @mailid

event 2:
exec dbo.sysmail_logmailevent_sp @event_type=1,@description=N'DatabaseMail process is started',@process_id=3888

event 3:
exec sp_readrequest @receive_timeout=600000

event 4:
exec dbo.sysmail_logmailevent_sp @event_type=2,@description=N'The read on the database failed. Reason: The EXECUTE permission was denied on the object ''sp_readrequest'', database ''msdb'', schema ''dbo''.
The EXECUTE permission was denied on the object ''sp_readrequest'', database ''msdb'', schema ''dbo''.',@process_id=3888




TIA
Tim

 

[mrdenny]

Run this script and see if any rights have been denied to that stored procedure.

select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
    sys.database_permissions.permission_name,
    sys.database_permissions.state permission_state,
    sys.database_permissions.state_desc,
    state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id =
    sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
    sys.database_principals.principal_id
order by 1, 2, 3, 5

I've checked one of my database servers and I have no rights granted or denied to that stored procedure.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[tdrclan]

the only user with a deny is TargetServersRole

example
Object sp_update_jobstep
username TargetServersRole
permissions_type EX
permission_name EXECUTE
permission_state D
state_desc DENY

but nothing denied on sp_readrequest for any one and only the grants that I added.

 

[mrdenny]

Is that the only procedure with a DENY assigned to it? Are there any DENY rights on the procedure sp_readrequest?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[tdrclan]

these are the only denied permissions on the database.
they are all for one user and none are for sp_readrequest.

sp_add_job TargetServersRole EXECUTE DENY
sp_add_jobschedule TargetServersRole EXECUTE DENY
sp_add_jobserver TargetServersRole EXECUTE DENY
sp_add_jobstep TargetServersRole EXECUTE DENY
sp_addtask TargetServersRole EXECUTE DENY
sp_delete_job TargetServersRole EXECUTE DENY
sp_delete_jobschedule TargetServersRole EXECUTE DENY
sp_delete_jobserver TargetServersRole EXECUTE DENY
sp_delete_jobstep TargetServersRole EXECUTE DENY
sp_droptask TargetServersRole EXECUTE DENY
sp_post_msx_operation TargetServersRole EXECUTE DENY
sp_start_job TargetServersRole EXECUTE DENY
sp_stop_job TargetServersRole EXECUTE DENY
sp_update_job TargetServersRole EXECUTE DENY
sp_update_jobschedule TargetServersRole EXECUTE DENY
sp_update_jobstep TargetServersRole EXECUTE DENY

TIA
Tim

 

[mrdenny]

I'm not sure why you're getting the error then. None of those procedures should effect database mail. At this point I'd need to run a profiler trace to see what's going on and where the actual failure is happening.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[SQLBill]

One thing I would check, in case this is a misleading error message, is the Exchange Mailbox used by SQL Server full? We have had issues with emails not being sent due to the mailbox being at its limit.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[tdrclan]

1) do you want me to run another trace?

2) the email server is off site at an ISP. (ex. mail.ISP.net)
fyi. I can sent an email from the server using the outlook express.

3) would active directory cause this problem?

 

[SQLBill]

When you send an email from the server, what 'From' is it using? Sending one from SQL Server uses the login that the SQL Server Agent service uses, so sending it from the server may be using a different account.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[tdrclan]

i'm not sure how to verify which security account is being used for outlook express and the sql mail, but they look like they are using the same info, and I think they are both using the administrator account.

i'm not sure but it seem that the problem started after changes were made to our active directory (AD) security, not sure if this would effect the sql mail pgm, and not sure how to verify what AD setting are.

was working fine until the 20th can not tell what is preventing the sql mail from working.

TIA
Tim

 

[tdrclan]

here is some more info.

I have a sql 2005 server and just setup the dbmail and it works.
not sure what the difference is between 2008 and 2005 that would cause 2008 to stop working, but I wonder if dbmail needs to be re-installed on the 2008 sql server.
I can look at the two servers and compare them, if needed.

anyway I think the problem is that 2008 can't or won't start the dbmail engine that checks the outbound queue because I don't see the log msg that the pgm starts like I use to.

Tim