Sql-server – set right permission to get connection ip for a user mapped to login

The state 38 error is probably a red herring: focus on the state 10 errors.

While KB Article #925744 says it is for SQL Server 2005, that is simply because of the date it was written. The symptom it causes would affect all versions of SQL Server that have been released since then in similar or identical ways.

Now, if user is a Windows login, perhaps you are somehow trying to authenticate as a Windows login without specifying the domain, or perhaps you are specifying the domain but the domain controller is unreliable or on an unreliable network connection. You should post your actual connection string in the question (fudging our real names/passwords of course).

You should also consider creating a new, separate login with the same permissions, and see if you intermittently get the same error with that other user as well - my guess this is an architectural problem in some way as opposed to a problem with just this user.

Another possibility is if your .net application is using Windows auth and the underlying password has been changed. Seems like a long shot, but check out the article and there is a hotfix available: KB Article #2545850.

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;