Prev: SQL Server Service User Account
From: Toni on 13 Aug 2010 16:44

Impersonation question

I need to have a user (DOMAIN\USERA) connect to server01 using their own
rights they already have by virtue of whatever Active Directory group
they are in, then I want them to impersonate USERB. USERB has rights to
run sp_start_job on SERVER02. Then they need to start a SQL Server Agent
job.

The setup I have is:

The DOMAIN\USERA login is created on SERVER01 with all the necessary
permissions to DB1 to be able to run their app.
The SQL Auth id USERB is set up on SERVER01 with USE permissions to DB1.
(I'm not sure what additional permissions it needs here.)

The DOMAIN\USERA login has IMPERSONATE rights to the USERB id.

I have a linked server set up from SERVER01 to SERVER02. All domain ids
link with their same domain id.
The SQL Auth id USERB is set to link with the same id and password on
both servers.


The SQL Auth id USERB is set up on SERVER02 with db_owner permission to
MSDB. (originally permissions were EXECUTE on sp_start_job only but we
changed to db_owner to see if it would help with troubleshooting.)

On SERVER01, DOMAIN\USERA is running this:

EXECUTE AS User = 'USERB';

EXEC SERVER02.msdb.dbo.sp_start_job N'Test Job';

REVERT;


The error they get is:

Msg 15274, Level 16, State 1, Line 3
Access to the remote server is denied because the current security
context is not trusted.

So, I have multiple questions. First, is this the right approach? If
it is, what am I doing wrong?

Thank you,
Toni

*** Sent via Developersdex http://www.developersdex.com ***
From: Toni on 13 Aug 2010 17:19
I think I figured out my problem.

I needed to grant IMPERSONATE rights on the LOGIN. I did that and can
now execute the SQL Agent job without any errors.

Toni

*** Sent via Developersdex http://www.developersdex.com ***
 | 
Pages: 1
Prev: SQL Server Service User Account