June 21, 2011 at 5:28 pm
hello im trying to use database mail to send emails with SQL but im with a big error atm don't know what's hapening with my SQL server:
i already put all my domain settings on data base mail wizard and activated the database...
When i use this procedure to sent an email:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Data base mail profile 1',
@recipients = 'blabla@hotmail.com',
@subject = 'Esfdfsfg ',
@body = 'dadsa'
It says the mail were queued... and if i do this query i can see all the mails i tryed to send:
SELECT * FROM msdb.dbo.sysmail_allitems
But when i try to execute this query bellow to see if i got any errors by sending the email :
SELECT * FROM msdb.dbo.sysmail_event_log
i only get 1 row
"The mail queue was started by login "user".
which was when i started the database service i think... but all those mails who are in the table (on the first query) doesn't show here and i can't see... I checked my mail box but didn't got any mail...
well i have some other problems which can be related to this my SQL jobs are currectly working and executing... but they don't post on the tables fields like "last run" and i can't see when job run...
At SQL agent logs i got this errors maybe this is related but i already tryed to search on the web and didn't saw any solution for this:
298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_log_jobhistory', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)
[298] SQLServer Error: 229, The UPDATE permission was denied on the object 'sysjobservers', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)
Message
[298] SQLServer Error: 229, The SELECT permission was denied on the object 'sysjobservers', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)
Message
[364] The Messenger service has not been started - NetSend notifications will not be sent
Any help it's really appreciated
Ty Ricardo
June 22, 2011 at 5:00 pm
Check to make sure service broker is enabled?
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
June 22, 2011 at 5:02 pm
returns 1 :S it means it is enable right?
June 22, 2011 at 5:10 pm
Yep. Anything in the Application event logs related to mail or the mail queue?
June 22, 2011 at 5:26 pm
yes i was searching the logs and realized i didn't look at Windows NT logs and get this errors from database mail it seems its a permission problem like on SQl jobs from SQL AGENT how i can grant all this permissions?
Date,Source,Severity,Log ID,Message,Process ID,Mail Item ID,Account ID,Last Modified,Last Modified By,Category,Event,User,Computer
06/23/2011 00:15:55,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:55,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:55,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:<nl/><nl/>1) Exception Information<nl/>===================<nl/>Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException<nl/>Message: The read on the database failed. Reason: The EXECUTE permission was denied on the object 'sp_readrequest'<c/> database 'msdb'<c/> schema 'dbo'.<nl/>Data: System.Collections.ListDictionaryInternal<nl/>TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)<nl/>HelpLink: NULL<nl/>Source: DatabaseMailEngine<nl/><nl/>StackTrace Information<nl/>===================<nl/> em Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.QueueDataReader.GetQueueItemFromCommand(SqlCommand c)<nl/> em Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.QueueDataReader.GetQueueData(Int32 receiveTimeoutSec)<nl/> em Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da<c/> Int32 lifetimeMinimumSec)<nl/><nl/>2) Exception Information<nl/>===================<nl/>Exception Type: System.Data.SqlClient.SqlException<nl/>Errors: System.Data.SqlClient.SqlErrorCollection<nl/>Class: 14<nl/>LineNumber: 1<nl/>Number: 229<nl/>Procedure: sp_readrequest<nl/>Server: \\RICARDO-TOSH\pipe\sql\query<nl/>State: 5<nl/>Source: .Net SqlClient Data Provider<nl/>ErrorCode: -2146232060<nl/>Message: The EXECUTE permission was denied on the object 'sp_readrequest'<c/> database 'msdb'<c/> schema 'dbo'.<nl/>Data: System.Collections.ListDictionaryInternal<nl/>TargetSite: Void OnError(System.Data.SqlClient.SqlException<c/> Boolean)<nl/>HelpLink: NULL<nl/><nl/>StackTrace Information<nl/>===================<nl/> em System.Data.SqlClient.SqlConnection.OnError(SqlException exception<c/> Boolean breakConnection)<nl/> em System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)<nl/> em System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior<c/> SqlCommand cmdHandler<c/> SqlDataReader dataStream<c/> BulkCopySimpleResultSet bulkCopyHandler<c/> TdsParserStateObject stateObj)<nl/> em System.Data.SqlClient.SqlDataReader.ConsumeMetaData()<nl/> em System.Data.SqlClient.SqlDataReader.get_MetaData()<nl/> em System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds<c/> RunBehavior runBehavior<c/> String resetOptionsString)<nl/> em System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior<c/> RunBehavior runBehavior<c/> Boolean returnStream<c/> Boolean async)<nl/> em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior<c/> RunBehavior runBehavior<c/> Boolean returnStream<c/> String method<c/> DbAsyncResult result)<nl/> em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior<c/> RunBehavior runBehavior<c/> Boolean returnStream<c/> String method)<nl/> em System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior<c/> String method)<nl/> em System.Data.SqlClient.SqlCommand.ExecuteReader()<nl/> em Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ExecuteReader(SqlCommand command),,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:49,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:44,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:39,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:34,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:30,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:27,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:24,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:21,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:19,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:18,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
06/23/2011 00:15:18,DatabaseMail,Error,,Database Engine Instance=RICARDO-TOSH;Mail PID=7144;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp'<c/> database 'msdb'<c/> schema 'dbo'.,,,,,,(0),0,,Ricardo-TOSH
June 22, 2011 at 5:54 pm
This page has all the grant information needed http://msdn.microsoft.com/en-us/library/ms188371.aspx
for the procedures
GRANT EXECUTE ON [procedure] TO user
and this is probably easier for the tables than doing grants though it will be effective on all tables in the db and not specific to the tables where you're getting the errors.
EXEC sp_addrolemember N'db_datareader', N'aspadonet'
EXEC sp_addrolemember N'db_datawriter', N'aspadonet'
June 22, 2011 at 5:55 pm
Have you configuered Database Mail?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 22, 2011 at 6:44 pm
Thanks all guys i fixe the problem with thsi query:
EXEC sp_addsrvrolemember [NT AUTHORITY\Serviço de rede]
, @rolename = 'sysadmin'
For some reason [NT AUTHORITY\Serviço de rede] was the user which i need to give permissions i don't know why ... i added the permissions to the wrong user before that was why everything was going wrong =)
One more time ty all for your time and help
June 22, 2011 at 7:05 pm
Good to hear!
June 22, 2011 at 7:08 pm
sysadmin will work but it will assign excessive permissions. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 22, 2011 at 7:15 pm
hum what do you mean with excessive permissions? sorry im still new to sql 😛
June 22, 2011 at 7:28 pm
The sysadmin role is a very powerful role in that it can perform any activity in sql server including deleting databases, logins, changing objects, shutting the server down, etc. NT AUTHORITY\Serviço de rede can be used by multiple services and making it sysadmin will be opening up a potential security threat. It would be safer to run SQL Server Agent under a different account and set permissions for that new account.
June 22, 2011 at 7:42 pm
oh i see... How i can change Sql Agent default user?
June 22, 2011 at 7:48 pm
You should use the configuration manager.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply