June 20, 2013 at 1:28 pm
Well, I have to say I'm stumped.
Just to clarify, I understood from what you've posted that the SQL Agent service startup account is a Windows domain account that is part of the sysadmin role, that the SQL Agent job is owned by a Windows domain account that is part of the sysadmin role, and that you run the job manually under your Windows domain account that is part of the sysadmin role. I can't think of any reason that the context would switch to sa for any reason.
Jason Wolfkill
June 20, 2013 at 1:45 pm
This may be a bit of a DUHHHHH, but who owns these jobs???
I seem to remember, a long time ago, in a galaxy far away, the same issue when the user who owned the job ran it manually.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 20, 2013 at 2:34 pm
From the first post. Easy to overlook with all the discussion though. 🙂
They are owned by the same domain account that the SQL Server Agent uses.
Good thought though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 21, 2013 at 12:02 am
I'd like to question if it is actually running as sa.
Is it the same if you use sys.dm_exec_sessions rather than the old sysprocesses?
Also can you write the current security context info to a table from the job? Or add a jobstep with print SUSER_SNAME() and tick "include step output in history"
June 21, 2013 at 4:43 am
foxxo (6/21/2013)
I'd like to question if it is actually running as sa.Is it the same if you use sys.dm_exec_sessions rather than the old sysprocesses?
Also can you write the current security context info to a table from the job? Or add a jobstep with
print SUSER_SNAME()and tick "include step output in history"
You know, that's a very good question (about the sys catalog thing). I need to check that out.
I did add the PRINT code to one of the other jobs (which has a similar setup) and got back the domain account instead of SA. We have several of these restore jobs, which we only need to run manually if they fail for some reason. We've gotten the SA account notification for those other jobs on intermittent occasions too.
So I'll test this on the job I am having to run manually today and compare the two table views.
June 21, 2013 at 5:48 am
jasona.work (6/20/2013)
OK, I don't know if this might help, but I found a similar issue elsewhere:...
(PS, I do see on one of my servers in sysprocesses an NTLogin showing a LoginName of SA right now. This is for the "FSAgent Task" which some quick Googleing shows as being used by the FileStream feature (which we are using))
OMG. That is exactly the same issue I'm having with SQL Server.
I don't think we're using the FileStream feature. But since you bring it up, how would I check to be sure?
EDIT: Interesting comment on that thread is that the procs were created by the SA account. My boss (who is also sysadmin) created the proc we are using. So far as I know, he didn't log in as SA to do it either. So maybe the recompile comment is on the money? Then again, as you mentioned, this is sybase, not SQL Server.
Does anyone know if the creator of procs are listed anywhere? I know that sysobjects has the schema ID. I'm looking for the identity of the login that actually ran the CREATE PROCEDURE statement.
June 21, 2013 at 5:54 am
wolfkillj (6/20/2013)
Well, I have to say I'm stumped.Just to clarify, I understood from what you've posted that the SQL Agent service startup account is a Windows domain account that is part of the sysadmin role, that the SQL Agent job is owned by a Windows domain account that is part of the sysadmin role, and that you run the job manually under your Windows domain account that is part of the sysadmin role. I can't think of any reason that the context would switch to sa for any reason.
Your clarification is correct and I'm just as stumped as you are.
BTW, I'm running that "other similar job" manually and it's running under the context of the SQL Server Agent domain account (the job's owner).
Interesting note. There are multiple threads going on for it. I ran SP_WHO2 and SELECT * FROM sys.dm_exec_sessions and the code for pulling from sysprocesses.
In the catalog view, everything looks correct. It has the domain account as the loginame and only one line in the view for the SPID (not multiple lines).
In sysprocesses, it shows multiple lines with the first one having the proper loginame while the second one has a blank value for loginame.
Not sure that means anything but throwing it out there anyway. I've got a second job that I'll need to run manually in a minute. I'll see if that does the same or different. BTW, this first job failed on multiple manual runs before I posted this, so it could have reset like the link Jason gave me mentioned.
We'll see what the second job does.
June 21, 2013 at 6:28 am
Likely if you don't think you're using it, you're probably not (Filestream that is), but easy enough to check:
exec sp_configure 'filestream access level'
If the config value is 0, then Filestream is off. 1 or 2 would indicate it's turned on at one of the two possible access-levels.
June 21, 2013 at 7:55 am
I may be a little out of depth here, but is there any chance someone set up a proxy and put that in the Run As option for the Job Step? And if, so can SA be used as that proxy?
Sorry if this is more confusing than helpful, but I haven't actually used this feature, I just know it exists.
June 21, 2013 at 8:06 am
Steve Thompson-454462 (6/21/2013)
I may be a little out of depth here, but is there any chance someone set up a proxy and put that in the Run As option for the Job Step? And if, so can SA be used as that proxy?
Steve, thanks for responding. My first post does actually state there are no proxies being used.
June 21, 2013 at 8:37 am
Brandie Tarvin (6/21/2013)
Steve Thompson-454462 (6/21/2013)
I may be a little out of depth here, but is there any chance someone set up a proxy and put that in the Run As option for the Job Step? And if, so can SA be used as that proxy?Steve, thanks for responding. My first post does actually state there are no proxies being used.
Oops, sorry about that, Brandie. I didn't pay enough attention.
June 21, 2013 at 8:49 am
Well, neither of the jobs did what I needed them to do. They both used the domain account properly. So that test is a bust. I'll have to hope I can catch something the next time one of these jobs does the SA thing.
In the meantime, other investigative suggestions are welcome.
June 21, 2013 at 9:19 am
jasona.work (6/21/2013)
Likely if you don't think you're using it, you're probably not (Filestream that is), but easy enough to check:
exec sp_configure 'filestream access level'If the config value is 0, then Filestream is off. 1 or 2 would indicate it's turned on at one of the two possible access-levels.
Config_value is 0. Thanks for this. At least I can rule this out.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply