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
