CODEWITHSUNDEEP
sqlsql-server
jac
jac

Reputation: 9726

Getting the logged in user name inside a stored procedure on a linked server

The standard in our company is to log the user name that inserts or updates a record at the time the record is inserted or updated. We inset and update the tables calling stored procedures from applications. We use SUSER_SNAME() in the stored procedures to get the name. Now we've added inserting records in a table on a linked server. The SUSER_SNAME function not is returning the same login name as it does on the native database. Instead it is returning the name of the account created to link with. I know I can pass the user name as another parameter to the query, but it would be easier if there is a built-in function that works both when connected to the server directly, and when connecting to a linked server. Is there a built-in function that does this?

Stored procedure

INSERT INTO Account (ForeignKey, AccountNumber, IsProcessed,
                     Origin, Updated, LoginName, Total)
VALUES (@ForeignKey, @AccountNumber, @IsProcessed,
        GETDATE(), NULL, SUSER_SNAME(), @Total)

RETURN SCOPE_IDENTITY()

Upvotes: 0

Views: 1007

Answers (1)

DeadZone
DeadZone

Reputation: 1690

No, there's no function to do this. When you create a linked server, the linked server "opens a connection" to the 2nd server. You, the user, have a connection to the 1st server. So when you run suser_sname() on the 1st server, you get your username that you are connected with. When you run it on the 2nd server, you STILL get the user name that you are using to connect to the second server. The difference is that the connection to the 2nd server is controlled by the administrator that configured the Linked Server. In your case, it sounds like he created a special account that is used for a linked server connection. Another option is to map accounts from the current server to accounts on the 2nd server. (If your DBA had done that, you might be getting the results that you want right now and not had any issues.) There are different ways to configure a linked server, and security should generally be a priority when doing so.

So, if you want to execute a query on the 2nd server and use your user name from the 1st server, then you need to pass it to the 2nd server, because it has no way of knowing who you are on a different machine.

Upvotes: 1

Related Questions