Sql-server – Problem with unmasking a database user during a sproc

Start out with getting all the procedures from sys.procedures

SELECT  * FROM sys.Procedures 

Then use EXEC sp_helptext on each to load the text of the procedures.

Search for text to add, add if needed,

Then load the updated text to a variable and execute dynamically.

Most likely the primary reason is that Table-Valued Functions return a Result Set, just like Tables and Views. This means that they can be used in the FROM clause (including JOINs and APPLYs, etc) of SELECT, UPDATE, and DELETE queries. You cannot, however, use a Scalar UDF in any of those contexts.

Secondarily, you can also EXECUTE a Scalar UDF. This syntax is quite handy when you have default values specified for input parameters. Take the following UDF, for example:

CREATE FUNCTION dbo.OptionalParameterTest (@Param1 INT = 1, @Param2 INT = 2)
RETURNS INT
AS
BEGIN
    RETURN @Param1 + @Param2;
END;

If you want to treat any of the input parameters as "optional", you still need to pass in the DEFAULT keyword when calling it like a function since the signature is fixed:

DECLARE @Bob1 INT;

SET @Bob1 = dbo.OptionalParameterTest(100, DEFAULT);

SELECT @Bob1;
-- Returns: 102

On the other hand, if you EXECUTE the function, then you can treat any parameters with a default value as truly optional, just like you can with Stored Procedures. You can pass in the first n parameters without specifying parameter names:

DECLARE @Bob2 INT;

EXEC @Bob2 = dbo.OptionalParameterTest 50;

SELECT @Bob2;
-- Returns: 52

You can even skip the first parameter by specifying parameter names, again, just like with Stored Procedures:

DECLARE @Bob3 INT;

EXEC @Bob3 = dbo.OptionalParameterTest @Param2 = 50;

SELECT @Bob3;
-- Returns: 51

UPDATE

Why might you want to use the EXEC syntax to call a scalar UDF just like a Stored Procedure? Occasionally there are UDFs that are great to have as UDFs since they can be added to a query and operate over the set of rows returned, whereas if the code were in a Stored Procedure then it would need to be placed into a cursor in order to iterate over a set of rows. But then there are times that you want to call that function on a single value, possibly from within another UDF. Calling a UDF for a single value can be done as either:

SELECT dbo.UDF('some value');

in which case you get a return value in a result set (a result set won't work). Or it could be done as follows:

DECLARE @Dummy INT;

SET @Dummy = dbo.UDF('some value');

in which case you need to declare the @Dummy variable;

HOWEVER, with the EXEC syntax, you can avoid both of those annoyances:

EXEC dbo.UDF 'some value';

ALSO, scalar UDFs have their executions plans cached. This means that it is possible to run into parameter sniffing issues if there are queries in the UDF that have execution plans. For scenarios where it is feasible to use the EXEC syntax, then it is possible to also use the WITH RECOMPILE option to ignore the plans compiled value for that execution. For example:

SETUP:

GO
CREATE FUNCTION dbo.TestUDF (@Something INT)
RETURNS INT
AS 
BEGIN
   DECLARE @Ret INT;
   SELECT @Ret = COUNT(*)
   FROM   sys.indexes si
   WHERE  si.[index_id] = @Something;

   RETURN @Ret;
END;
GO

TEST:

DECLARE @Val INT;

SET @Val = dbo.TestUDF(1);
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 -- uses compiled value of (1)
SELECT @Val;

EXEC @Val = dbo.TestUDF 0 WITH RECOMPILE; -- uses compiled value of (0)
SELECT @Val;

EXEC @Val = dbo.TestUDF 3 -- uses compiled value of (1)
SELECT @Val;