I have a simple query that I run to send email notifications to our AD account administrators when accounts have been disabled for more than 30 days. It works perfectly fine when I run it on my own, logged in as SA, but it fails when run inside SQL Server Agent jobs.
Here's the query, with business-specific items and object names replaced:
DECLARE @QueryString varchar(max) SET @QueryString = 'Select TrackingTable.Username FROM dbName.Schema.TrackingTable inner join dbName.Schema.viewName on DisabledAccounts.username = viewName.username WHERE DATEDIFF(dd,DateDisabled,GETDATE()) > 25 AND viewName.OU = ''InactiveAccounts''' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile', @body = 'This is the body text. Nothing interesting here. ', @recipients = 'firstname.lastname@example.org', @subject='Account status update', @query = @QueryString, @importance = 'High'
When I run it as SA, the message gets sent. Inside a SQL Server Agent job, I get this error:
Executed as user: DOMAIN\MemberOfDomainAdmins. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
The domain user the job executes under is assigned the db_owner role on msdb and the database used for the query attached to the message. Its default schema on both databases is dbo.
It's also assigned the sysadmin role on the server and is a member of the DatabaseMailuserRole on msdb. It also has private and public access to the Database Mail profile used by the query.
I've seen dozens of examples of the same issue online, but I've already taken the steps that remedied this problem in the examples I saw. What else can I try?