Inline-table valued function execute as 'user'

Question

Is it possible to execute inline-table valued function as some user? e.g.

CREATE FUNCTION fun()
WITH EXECUTE AS owner
RETURN(
...
)

They are inlined into the outer query so run in the same execution context as that. So the answer is "no" — Martin Smith, Oct 21 '21 at 10:29
Bear in mind that part of the point of inline table-valued functions is that they're, well, inlined into a larger query and optimized with it as part of the whole. Which means that parts of the query may be re-written and moved around. It would be difficult to do that *and* attach different permissions to the relational operators. — Damien_The_Unbeliever, Oct 21 '21 at 10:30
Is there some reason normal ownership chaining doesn't work for you? If the objects referenced by `func` are owned by the same user (e.g. dbo), permissions on tables referenced by the function are not checked; users with `SELECT` permission on the function do not need permissions on the tables too. No need for `EXECUTE AS`. — Dan Guzman, Oct 21 '21 at 10:52
@DanGuzman normal ownership works. The problem is that the function is calling objects from another DB, on which this specific user should not have reading permissions. "Execute as" is possible in MTVF, but because of performance issues this is not an option. — Dana, Oct 22 '21 at 06:24

score 2 · Answer 1 · answered Oct 21 '21 at 10:26

From the syntax section of EXECUTE AS Clause (Transact-SQL):

Syntax

-- SQL Server Syntax  
Functions (except inline table-valued functions), Stored Procedures, and DML Triggers  
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

So no you cannot, inline table value functions are explicitly denoted as not able to have the WITH EXECUTE AS option defined.

score 0 · Answer 2 · answered Oct 22 '21 at 12:11

EXECUTE AS is not permitted in functions as @Larnu and @MartinSmith mentioned in answers and comments. Additionally, even if it were allowed, the EXECUTE AS security context is sandboxed to the current database unless you set the database TRUSTWORTHY, which is something to be done only when absolutely necessary.

Ownership chaining is an alternative to EXECUTE AS. Because objects exists in other databases, the databases involved need to have the DB_CHAINING ON option set and have the same owner (assuming dbo schema objects). The caller needs to be defined as a user in all databases involved but no object permissions need be granted on objects used indirectly.

One should enable DB_CHAINING only when trusted users have permissions to create objects to avoid elevation of privileges.

Example script for cross-database chaining:

ALTER DATABASE DB1 SET DB_CHAINING ON;
ALTER DATABASE DB2 SET DB_CHAINING ON;
GO
USE DB1;
GO
--user needs only SELECT on function
CREATE USER YourUser;
GRANT SELECT ON dbo.Fun TO YourUser;
GO
--user needs a security context in other database but no object permissions
USE DB2;
CREATE USER YourUser;
GO

Inline-table valued function execute as 'user'

2 Answers2

Syntax