Microsoft KB Archive/123330

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

Article ID: 123330

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q123330



BUG# NT: 936 (4.21a)



SYMPTOMS
If a user executes a stored procedure with UPDATE STATISTICS 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: kbbug kbprogramming KB123330

-

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

© Microsoft Corporation. All rights reserved.