Blog Post

about xp_sendmail

,

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?

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating