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