| Jeffrey Yao 2005-09-11, 1:23 pm |
|
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?
Note: My environment is SQL Server Enterprise 2000 + MS 2000 Server both
patched with SP4
|