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 <object> and is not the object owner or the dbo of the database the object is in, SQL Server returns error 2710:
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
- 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.
- 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