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

ID: Q123330

The information in this article applies to:
BUG# NT: 936 (4.21a)

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.

This error appears regardless of satisfying the following condition that is specified on page 233 of "Transact-SQL Reference":
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.

NOTE: The special case of UPDATE STATISTICS in a stored procedure is not covered here.


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

Additional query words: Windows NT 


Keywords          : kbprg SSrvStProc kbbug4.21a SSrvWinNT 
Version           : 4.21a
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 19, 1999