BUG: Replay Tool Uses LoginName Column for SETUSER Instead of DatabaseUserName Column (286239)

Microsoft SQL Server 2000 (all editions)

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.

Modification Type:

Major

Last Reviewed:

10/3/2003

Keywords:

kbBug kbpending KB286239