BUG: A SET USER command run by using an Xp_cmdshell or a CmdExecJob job step returns an incorrect value for the service logon account in a SQL Server 2000 cluster (821270)

SYMPTOMS

When you run the following extended stored procedure to retrieve the security context on a SQL Server 2000 cluster, you may receive the cluster service log on account instead of the SQL Server 2000 service log on account:

EXEC master..xp_cmdshell('SET USER')

You may also notice that when you run a CmdExecJob job step that has the SET USER command on a SQL Server 2000 cluster that the SET USER command returns the cluster service log on account instead of the SQL Server Agent service log on account.

WORKAROUND

To work around this problem, use the whoami command-line utility to retrieve information about the security context of your SQL Server 2000 cluster. The whoami command-line utility is available in the Windows 2000 Resource Kit. To download the whoami command-line utility, visit the following Microsoft Web site:

Windows 2000 Resource Kit Tool: Whoami.exe

When you run the whoami command by using the xp_cmdshell extended stored procedure, the command-line utility returns the SQL Server Service log on account. Where as, when you run the whoami command-line utility in a CmdExecJob job step of a SQL Server Agent job, the utility returns the SQL Server Agent service log on account.

MORE INFORMATION

When you run the SET USER command, the command queries the %USERNAME% windows environment variable to retrieve the security context of the computer.

When the xp_cmdshell extended stored procedure is run by a user who is a member of the sysadmin fixed server role, the xp_cmdshell stored procedure runs under the security context in which the SQL Server service is running. When a sysadmin role member runs the CmdExecJob job step, the job runs under the security context of the SQL Server Agent service.

Steps to Reproduce the Behavior

Set the log on user of the SQL Server Service to a different user account from the log on user of the cluster service.

Note To set the log on user account of a service running on your computer, follow these steps:
1. On the Taskbar, click Start, and then click Run.
2. In the Open dialog box, type Services.msc, and then click OK.
3. In the Services window, under the Name list, right-click the name of the service for which you want to set the log on user account, and then click Properties.
  
  Note The service name for the default instance of Microsoft SQL Server 2000 is MSSQLSERVER.
4. In the <Service Name> Properties dialog box, click the Log On tab.
5. Under the Log on as section, click to select the This account option, and then provide the log on information.
6. Right-click <Service Name>, and then click Restart.
Start SQL Server 2000 Query Analyzer, and then connect to the server that is running SQL Server for which you just changed the log on account.
Run the following Transact-SQL statement:
```
EXEC master..xp_cmdshell('SET USER')
```
You may notice that the USERNAME that is returned is the Logon user account of the cluster service and not that of the SQL Server service.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

248407 PRB: Using xp_cmdshell with Non-Sysadmin Accounts in a Clustered Environment

For additional information about xp_cmdshell , visit the following Microsoft Web site:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp