BUG: 2710 Error When Executing St. Proc. w/ UPDATE STATISTICS

• Microsoft SQL Server, version 4.2
• Microsoft Windows NT Server, version 3.5

SYMPTOMS

If a user executes a stored procedure with UPDATE STATISTICS <object> and is not the object owner or the dbo of the database the object is in, SQL Server returns error 2710:

   Msg 2710, Level 16, State 2
   You are not the owner specified for the object 'xyz' in this command
   (CREATE, ALTER, TRUNCATE, UPDATE STATISTICS, or BULK INSERT). The
   database user can use the SETUSER command to assume the identity
   of another user.

   Inside a stored procedure, object names used with certain statements
   (the so-called utility statements) must be qualified with the object
   owners' name if other users are to use the stored procedure. The
   utility states are ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE
   TABLE, CREATE INDEX, DROP INDEX, UPDATE STATISTICS, and DBCC.

WORKAROUND

1. Write a stored procedure that accepts the user's userid and password, authenticates it, and executes a SQL script using xp_cmdshell. The SQL script logins as dbo and executes the UPDATE STATISTICS.

2. Write a DB-Library (DB-Lib) application and execute it using xp_cmdshell.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.21a. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

If TRUNCATE TABLE is used instead of UPDATE STATISTICS, then the user gets 4710 error. This seems to be a problem with all utility statements inside a stored procedure.

On page 446 of the "Transact-SQL Reference," it states:

   UPDATE STATISTICS permission defaults to the table owner and is not
   transferable, except to the database owner, who can impersonate the
   table owner by running the SETUSER statement.

To Verify

   login as sa
   use pubs
   go
   create procedure bug as
   update statistics dbo.roysched
   go
   grant execute on bug to dick /* dick is a member of public group only
   in pubs */
   go

   login as dick
   use pubs
   go
   exec bug
   :2710 error