October 10, 2014 at 4:24 am
Can anyone help with this SQL Agent running a Trace issue:
I have scripted out a SQL Profiler Trace where I want to schedule the execution time. The T-SQL Trace script execute fine when I execute it directly with my Domain account, the output and Trace file generate successfully.
I create a SQL Agent job and set the Job to execute under my same Domain account, the Job fails with error:
"You do not have permission to run 'SP_TRACE_CREATE'. "
My Domain user obviously has the GRANT ALTER TRACE TO [domain]\User as the script runs fine outside of the SQL Agent Job. The SQL Agent service is running under a Domain account but is not a Login on this Instance.
Environment:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Nothing comes up in Error Log, Event Viewer (system or application).
October 10, 2014 at 6:02 am
The domain account that Agent is running in will need to have permissions set for it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 10, 2014 at 6:58 am
Thanks Grant. The Domain user which the SQL Agent is running under doesn't have a Login to the instance (it is connecting via NT SERVICE account to the Instance). Other Jobs succeed, but obviously not this one because it's running a Trace directly on the Database Engine.
Also, the job itself is set to run under my Domain account, should the SQL Agent Domain need a Login of its own?
UPDATE: I've created a Login for the same Domain user which the SQL Agent is running under and GRANT ALTER TRACE TO that user. <-- this made no difference 🙁
October 10, 2014 at 7:38 am
I got this Job working by removing my own User account under the RUN AS credentials and having the job executed by the SQL Agent alone.
My own account is SYSADMIN role with GRANT ALTER TRACE permission.
I cannot work this one out, it looks pretty weird to me.
October 10, 2014 at 7:52 am
RUN AS is a secondary set of permissions you'd need as well. Security is fun.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply