I have created a
testuser login set as
sysdamin, and used it for the clause
execute as in the following function created in a test database other than the
CREATE FUNCTION[dbo].[MyTestFunction](@job_name SYSNAME) RETURNS INT WITH EXECUTE AS 'TestUser' AS BEGIN DECLARE @id INT SELECT TOP 1 @id = schedule_id FROM msdb.dbo.sysjobschedules RETURN @id END
When I execute
MyTestFunction I get the following error:
The SELECT permission was denied on the object 'sysjobschedules', database 'msdb', schema 'dbo'.
In change, if I directly log in with the
TestUser login and straight execute the
SELECT statement using the test database, it works fine.
TestUser can directly access
msdb from the test database, but can't access
msdb if used in the
execute as clause of a function of the test database.
Why this happens?
How can I access the
msdb database from a function created in another database?
Thanks in advance,