Question about EXECUTE AS USER

Can anyone tell me why I'll receive a error via following steps? Thanks in advance!

1. Create a database “TESTDB” and a table “Table1”

2. Create a login “TestLogin”, which is db_owner roles of both msdb and TESTDB

3. Create a DML trigger for Table1 by following script

CREATE TRIGGER TRG1

ON Table1

FOR INSERT, UPDATE, DELETE

AS

SELECT * FROM msdb..sysjobs

GO

4. Open SSMS, login as TestLogin, execute following statement

SELECT * FROM msdb..sysjobs

It will succeed to select data in msdb..sysjobs

5. Open another SSMS, login as sa, execute following statement

use TEST

execute as user='TestLogin'

select * From msdb..sysjobs

I receive an error about permission to select data from sysjobs, why?

Add the EXECUTE AS to the end of the Query.

SELECT *

FROM msdb..sysjobs

EXECUTE AS user='TestLogin'

|||

The reason why “SELECT * FROM msdb..sysjobs”fails under an impersonated context (EXECUTE AS USER) is because the impersonation mechanism you are calling is (by default) bound only to the current database (TESTDB), but you are trying to access data from a different DB (msdb).

As Arnie suggested, one potential solution may be use the current execution context to gether the information from msdb, and after that impersonate, but it would really depend on what you are trying to accomplish on this task.

I would recommend the following topics from BOL:

· Understanding Context Switching (http://msdn2.microsoft.com/en-us/library/ms191296.aspx)

· Extending Database Impersonation by Using EXECUTE AS (http://msdn2.microsoft.com/en-us/library/ms188304.aspx )

My guess s that the trigger you are trying to create is intended to have a controlled escalation of the privileges of the invoker in order to gather information from msdb and accomplish some task, correct?

If this is the case, I would suggest evaluating using digital signatures for this task. I have an example in my blog that probably may help you to get started (not exactly the same scenario, but I hope it will be useful):
http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

If you have further questions, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine