Microsoft KB Archive/286239

= BUG: Replay Tool Uses LoginName Column for SETUSER Instead of DatabaseUserName Column =

Article ID: 286239

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q286239



BUG #: 234862 (SHILOH)



SYMPTOMS
If the user id in a database is different than the login id (for example, for the database owner) any SQL Server Profiler replay that uses traces captured for that id is run in the context of system administrator (sa) instead of in the context of the username.



CAUSE
SQL Server 2000 Profiler replay always executes a SETUSER statement after the connection is established to impersonate the particular user that issued the query during the trace capture. Replay uses the LoginName column in the trace file for the SETUSER statement.



WORKAROUND
To work around this behavior, use either of the following:
 * If security does not matter for the purposes of the replay, let the replay default to sa.


 * When you capture a file for replay, only use ids where the login id and the user id are the same.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
The SETUSER command operates within the context of a database and takes a username from its current context as its argument. The argument that is passed to the SETUSER command should be the value in the DatabaseUserName column of the trace file. However, replay is using the LoginName column for SETUSER instead.

Any of the following scenarios cause this problem to occur if the logins are involved in traced activity.   use master exec sp_addlogin Login1 use pubs exec sp_changedbowner Login1 exec sp_helpuser dbo go The sp_helpuser stored procedure shows that the login name is Login1 and that the database username is dbo. The statement issued by replay is SETUSER Login1 when it should be: SETUSER dbo

  use master exec sp_addlogin Login2 use pubs exec sp_adduser Login2, DifferentUserName exec sp_helpuser DifferentUserName go The sp_helpuser stored procedure shows that the login name is Login2 and that the database username is DifferentUserName. The statement issued by replay is: SETUSER Login2 when it should be: SETUSER DifferentUserName



Because the failure for SETUSER is only a replay internal error, the replay tool continues to run subsequent queries as sa when SETUSER fails.

Keywords: kbbug kbpending KB286239

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.