Introduction

Database Administrators experience many common issues. Some of them will be resolved quickly, but a few might take some time to find a solution. I faced one such issue recently and wanted to share my experience, which will save time for others if they encounter a similar issue.

Scenario

I had a SQL Server job that runs a simple procedure, sp_spaceused. But the job kept on failing with an error. Here is my job step code:

EXEC msdb.dbo.sp_send_dbmail @recipients=’<email_id>',
    @subject = 'Space Usage TestDB’,
@execute_query_database  ='Test',
@query = 'exec sp_spaceused',
@profile_name = 'TestMail'

The error returned was:

“Executed as user: <SQLServiceAccount>. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).  The step failed.”

The same code worked fine in Management Studio.  I  then assumed that the SQL Agent setup wasn’t done properly. I tried setting the database mail profile again in the SQL Agent settings. I unchecked the box beside “Enable mail profile” and then checked it again. I then restarted the SQL Server Agent service for the changes to take effect.

The job was still failing with the same error.

I tried dropping and recreating the Database Mail profile under the Management tab this time. I had to restart the SQL Agent service again. I then tried to re-run the jobs, but again I had no luck as they were failing with the same error.

I ran Profiler as a last resort to find out exactly what was happening at the backend. It was quite strange to find the below error.

“The server principal " SQLServiceAccount " is not able to access the database "test" under the current security context.”

I was left clueless after seeing the above error and then realized that SQL Server was not installed using this service account. After the SQL Server was installed, the service account was changed, and the new account didn't get permissions to the database during the process. All I did was grant access to the service account again, and guess what, the job worked as expected.

At times the errors might be misleading and doesn’t really mean what it says.

Conclusion

I have seen the same error in three of our servers which had SQL Server 2014 installed on them.  I thought this was worth sharing with others as it would be helpful for those who face this error on SQL Server 2014.