SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Agent jobs running under SA


SQL Server Agent jobs running under SA

Author
Message
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38082 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38082 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
jasona.work
jasona.work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10204 Visits: 12545
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.
Steve Thompson
Steve Thompson
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 2180
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.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38082 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Steve Thompson
Steve Thompson
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 2180
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.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38082 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38082 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search