Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SQL Server Agent jobs running under SA Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
jasona.work (6/20/2013)
OK, I don't know if this might help, but I found a similar issue elsewhere:
Link to topic
...
(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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1466145
Posted Friday, June 21, 2013 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1466146
Posted Friday, June 21, 2013 6:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 907, Visits: 4,467
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.
Post #1466155
Posted Friday, June 21, 2013 7:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:39 PM
Points: 289, Visits: 1,625
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.
Post #1466196
Posted Friday, June 21, 2013 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1466200
Posted Friday, June 21, 2013 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:39 PM
Points: 289, Visits: 1,625
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.
Post #1466220
Posted Friday, June 21, 2013 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1466227
Posted Friday, June 21, 2013 9:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 7,072, Visits: 6,230
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1466249
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse