Inline-table valued function execute as 'user'-CodePudding

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

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

CodePudding user response：

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.

CodePudding user response：

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