1

I have a query that will impersonate as a specific domain user to send email with attachment. This impersonation is required as it needs to read a file at some other server to be able to attach the file to the email.

The query is as follows

EXECUTE AS login='domain\user' -- To impersonate
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile_name',
    @recipients = @recipient,
    @copy_recipients = @cc,
    @blind_copy_recipients = @bcc,
    @subject = @subj,
    @body = @mail_body,
    @body_format = 'HTML',
    @from_address = 'email@address.com',
    @file_attachments = @files;
REVERT

Sometimes I get error when running the query with error message as:

Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b.

The weird thing is that, if I execute this query multiple times, it will work and it will send out the email.

What could possibly be wrong?

SQL Server version: 11.0.2100

closed as off-topic by hot2use, Erik Darling, McNets, Mr.Brownstone, LowlyDBA Dec 14 '17 at 16:29

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – hot2use, Erik Darling, McNets, Mr.Brownstone, LowlyDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Try to give sysadmin privilege to this domain user and execute the query. – Bhuvanesh Dec 14 '17 at 3:36
  • Check here, may be it's your case: techsupport.osisoft.com/Troubleshooting/KB/KB00604 – sepupic Dec 14 '17 at 8:25
  • 1
    That error indicates that the specified domain does not exist. I would check that you are not having communications issues with your domain controllers. Check the system event log locally, and work with your domain admins to check the DCs for errors. – Nic Dec 14 '17 at 16:15
-1

I would strongly advise against using sysadmin privileges to simply send an email... have you ever hear of the principle of least privilege!? This wouldn't solve your problem, just provide a work around! Similarly, you would be enabling the login to drop databases, change passwords, and create all sorts of chaos!

Have you checked the SQL error logs, or the event logs. It may also be a good idea to check the logs on your DC, to see if there is any information there.

Are you having any problems with the account that the agent job is being execute as? Presumably this has the appropriate permissions for what you are asking? when you are executing the script you are executing it under your context, not the context of the agent. This is likely why it works for you all the time.

Without any additional information, preferably from your logs, it's difficult to help any better than this

Not the answer you're looking for? Browse other questions tagged or ask your own question.