SQL Agent Mess

2 Dec 2005 7:58 PM

Mark

Environment: SQL 2000 SP3a

I have tired various things but not sure which route to go from now.

I have a SQL Job which should be owned by non SA account as some of the
members in the other team want to look at this job and by making this job
owned by SA, they lose this previlege.

This job does various things:
1. Retrieves data from local server
2. Retrieves data from linked server.
3. Calls a DTS package
4. Run OLAP refresh.

This job is owned by a SQL ID which is not a member of sysadmin SQL server
Role but it is db_owner in the database where it tries to run a SQL Against.
This user also exists on remote server that is used as a linked server in
the query and this user has the same password on the linked server and is a
member of db_owner role on the database where this query is tryign to retieve
data from. Error I am getting is always ACCESS DENIED error and not sure
where this access denied coming from.

I am getting access denied error and not sure what is causing it.

Question(s)
When a job is owned by non-sysadmin, does the job run uder the context of
PROXY Account or the account SQL Server Agent is running under?

Is there is a way that even though this job is owned by non sa account but
when it runs, it should run under an account which is local admin on SQL box?

Any help will be appreciated.

3 Dec 2005 10:49 AM

Tibor Karaszi

> When a job is owned by non-sysadmin, does the job run uder the context of
> PROXY Account or the account SQL Server Agent is running under?

It depends on the jobstep. For CmdExec and Active script, the proxy account is used. For TSQL, SQL
Server essentially executes SETUSER to "emulate" the user context of the owner. For the latter part,
no server wide activity is performed (since a login cannot be emulated, only a user).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Mark" <M***@discussions.microsoft.com> wrote in message
news:797A2E80-F4F6-402A-B37D-D3B26F09786D@microsoft.com...
> Environment: SQL 2000 SP3a
>
> I have tired various things but not sure which route to go from now.
>
> I have a SQL Job which should be owned by non SA account as some of the
> members in the other team want to look at this job and by making this job
> owned by SA, they lose this previlege.
>
> This job does various things:
> 1. Retrieves data from local server
> 2. Retrieves data from linked server.
> 3. Calls a DTS package
> 4. Run OLAP refresh.
>
> This job is owned by a SQL ID which is not a member of sysadmin SQL server
> Role but it is db_owner in the database where it tries to run a SQL Against.
> This user also exists on remote server that is used as a linked server in
> the query and this user has the same password on the linked server and is a
> member of db_owner role on the database where this query is tryign to retieve
> data from. Error I am getting is always ACCESS DENIED error and not sure
> where this access denied coming from.
>
> I am getting access denied error and not sure what is causing it.
>
> Question(s)
> When a job is owned by non-sysadmin, does the job run uder the context of
> PROXY Account or the account SQL Server Agent is running under?
>
> Is there is a way that even though this job is owned by non sa account but
> when it runs, it should run under an account which is local admin on SQL box?
>
> Any help will be appreciated.