I got a very interesting issue, I may guess the reason but not sure why it is this way.
Symptom: Logon to a SQL Server (Server_A) through QA with Windows Authentication (my domain account Domain\JeffYao has sa priviledge), then try to run the following statement
-- the following is SQL_Statement_1
exec master.dbo.xp_sendmail @recipients= 'jeffyao'
, @message ='Test only, please see attached for details'
, @Subject= 'SQL mail test, pls ignore'
, @attachments = 'abc.txt'
, @query = 'select au_id, au_lname from pubs.dbo.Authors where au_fname = ''Ken'''
, @no_header = 'true'
, @attach_results = 'true'
, @width = 220
and I got error message:
Server: Msg 18024, Level 16, State 1, Line 0
xp_sendmail: failed with operating system error 5
But if I do not send email with attachement, such as the following one
-- the following is SQL_Statement_2
exec master.dbo.xp_sendmail @recipients= 'jeffyao'
, @message ='Test only, please see inside for details'
, @Subject= 'SQL mail test, pls ignore'
, @query = 'select au_id, au_lname from pubs.dbo.Authors where au_fname = ''Ken'''
, @no_header = 'true'
It worked perfectly. On the other hand, if I log on Server_A with QA using sa and password (not Windows authentication), I can still run the statement SQL_Statement_1 without any error.
I tried to figure out why by googling, but to no avail. So I started my own investigation, I used terminal service client to log on to Server_A(with another different domain account Domain\NetAdmin ), and check the event log (security log), and I see there are some "Failure Audit" errors relating to my domain account Domain\JeffYao, on the other other hand, on Server_A, Domain\JeffYao is not listed in the user list. So I add Domain\JeffYao to the local "administror" group, and now when I connect to QA again with Domain\JeffYao account, I can run the SQL_Statement_1.
Can anyone shed some lights on why my Domain\JeffYao account (with system administrators role in SQL Server) cannot run SQL_Statement_1 (but can run SQL_Statement_2) while "sa" can run both SQL_Statement_1 and SQL_Statement_2?