January 22, 2014 at 1:03 am
Hi all
I am running SQL Server 2012 ENT SP1.
The SQL Server Agent Service is running under account Domain\SQLServerAgent. Our SQL service accounts have no rights in SQL, not event a login to the server.
I have a job that has been running fine for 11 days and suddenly failed yesterday with the error below:
Executed as user: Domain\SQLServerAgent. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050) Query execution failed: Msg 916, Level 14, State 1, Server SERVER01, Line 1 The server principal "Domain\SQLServerAgent" is not able to access the database "Database1" under the current security context. [SQLSTATE 42000] (Error 14661). The step failed.
The job owner is Domain\SQLJobs, thus the job should run under Domain\SQLJobs right?
So why is the job suddenly wanting to execute the stored procs as Domain\SQLServerAgent?
Domain\SQLJobs is sysadmin in SQL
I ran the stored proc as Domain\SQLJobs successfully
I have made no changes to the server, but it's not impossible that there were changes as there are other DBAs.
Any ideas, please help.
Thank you,
TDP
January 22, 2014 at 1:52 am
Some more info after lots of testing:
The problem is with the email part of the stored proc. And because it is in an if statement, it is the first time the if is true, this is why it was running successful previously as the email part below was never run. I am still at a loss though....
So, after lots of testing, the job runs successfully if I comment out the @query parameter. Thus, with the @query part commented out, the email is sent successfully.
So the assumption is that the login running the job does not have permission to the table in the @query, but the login is sysadmin on the server and when I execute the email part (including the @query) manually as the login running the job in SSMS, it runs successfully and the email is sent successfully, thus the login has the correct permission to execute the query including the @query part.
IF (SELECT COUNT(1) FROM Database1.dbo.tblServStatsDBFileAlert)>0
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alerts Profile',
@recipients = '*********',
@body = 'Databases without 2 data files',
@query = 'SELECT CAST(databasename AS char(50)) FROM database1.dbo.tblServStatsDBFileAlert',
@subject = 'SERVER1 databases with without 2 files'
END
What am I missing???
Please, any comments are welcome, even if it sounds stupid - it's usually the small things we overlook.
TDP
January 22, 2014 at 1:58 am
TDP (1/22/2014)
Hi allI am running SQL Server 2012 ENT SP1.
The SQL Server Agent Service is running under account Domain\SQLServerAgent. Our SQL service accounts have no rights in SQL, not event a login to the server.
I have a job that has been running fine for 11 days and suddenly failed yesterday with the error below:
Executed as user: Domain\SQLServerAgent. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050) Query execution failed: Msg 916, Level 14, State 1, Server SERVER01, Line 1 The server principal "Domain\SQLServerAgent" is not able to access the database "Database1" under the current security context. [SQLSTATE 42000] (Error 14661). The step failed.
The job owner is Domain\SQLJobs, thus the job should run under Domain\SQLJobs right?
So why is the job suddenly wanting to execute the stored procs as Domain\SQLServerAgent?
Domain\SQLJobs is sysadmin in SQL
I ran the stored proc as Domain\SQLJobs successfully
I have made no changes to the server, but it's not impossible that there were changes as there are other DBAs.
The job owner has nothing to do with the security context under which job steps are executed.
If you open the jobstep, you will see a Run As dropdown menu. This will default to the SQL Server Agent service account (Domain\SQLServerAgent).
If you want the jobstep to run as Domain\SQLJobs, you need to create a proxy.
Creating SQL Server Agent Proxies
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2014 at 2:03 am
OK sure, but if there is no proxy set and the AgentServiceAccount Domain\SQLServerAgent does not have access to SQL, what account does the job then run under when it successfully runs? This cannot be Domain\SQLServerAgent login as the login has not rights in SQL.
January 22, 2014 at 2:06 am
The service account will probably have permissions in MSDB, and maybe that is all it needs to run the job.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2014 at 2:09 am
Nope, service account has no permissions at all.
The job is executing a stored proc that is not in msdb anyway, thus would need more than permissions to msdb anyway.
January 22, 2014 at 2:16 am
TDP (1/22/2014)
Nope, service account has no permissions at all.The job is executing a stored proc that is not in msdb anyway, thus would need more than permissions to msdb anyway.
According to this technet page, the service account is sysadmin:
Select an Account for the SQL Server Agent Service
I'm not entirely sure that it is true. I'm sure though it has permissions on MSDB, otherwise jobs wouldn't be able to run.
If you open the job history and look at the history of an individual jobstep, you can see which account it used. The message usually starts with "Executed as user: domain\user".
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2014 at 2:45 am
Cool, thanks for the article.
If this were true, then the job would not fail giving the error 🙂
The server principal "Domain\SQLServerAgent" is not able to access the database "Database1" under the current security context.
This is all terribly confusing...
Also, creating a proxy will not work as you cannot run a Transact SQL step as a proxy.
TDP
January 22, 2014 at 2:55 am
TDP (1/22/2014)
Cool, thanks for the article.If this were true, then the job would not fail giving the error 🙂
The server principal "Domain\SQLServerAgent" is not able to access the database "Database1" under the current security context.
This is all terribly confusing...
Also, creating a proxy will not work as you cannot run a Transact SQL step as a proxy.
TDP
Right. You indeed cannot specify a proxy in a TSQL jobstep. Quite lame.
And if it fails with that error, it surely isn't an SA.
I checked with a job of mine, and the TSQL jobstep runs under the Agent account, not the job owner.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2014 at 4:01 am
All very odd 🙂
Thanks for your comments.
I've solved it by adding the proc to one of my SSIS packages, so it's definitely running as Domain\SQLJobs now and running fine.
Would love to know what rights the SQLServerAgent service account has to the server by default.
January 22, 2014 at 4:32 am
This is an interesting article:
Does the SQL Agent Service Account Must be a SysAdmin?[/url]
It mentions the service account doesn't even need a SQL Server login.
I'm curious though under which account the job history is logged in the MSDB database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply