CODEWITHSUNDEEP
sql-serverservice
QFirstLast
QFirstLast

Reputation: 239

How do I display the "real userid" on whose behalf a service is making the connection to the SQL Server?

We have a three tier application where the UI connects to a service and the service then uses a standard account to connect to the SQL Server using integrated authentication. On a busy system, it would be helpful to know the application user on whose behalf the service account is making a connection to the SQL Server. Is there a standard mechanism for doing so ? I am thinking of (mis)using the SqlConnection properties ApplicationName or WorkstationID for this purpose. The service layer would set these properties to the application user on the SqlConnection and the sp_who2 output would then display the user information. Thus if 'UserX' logged into the application and Service account 'ServiceUser' connected to the SQL Server, 'UserX' would show under ProgramName and 'ServiceUser' under Login. Would this have a negative impact on connection pooling ? Are there any disadvantages of using approach ?

Upvotes: 0

Views: 37

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88971

Would this have a negative impact on connection pooling ?

Yes. The connection pool is partitioned by connection string, so you would have a connection pool per user. But if you limit the connection pools size, the impact may not be material.

Alternatively you can call sp_set_session_context after connecting to add data to the session identifying the end user.

Upvotes: 2

Related Questions