Microsoft KB Archive/255067

= PRB: Owner of a Table Cannot Access It Directly If Made Sysadmin After Creation =

Article ID: 255067

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q255067



SYMPTOMS
If a user, with a specific login ID, creates and owns a table, then that user may receive an error message when attempting to access that table without a qualified name. For example, if a user named "Hugo" creates and owns table T1 and then executes the statement: SELECT * FROM T1 He/she may receive the following error message:

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'T1'.

However, executing the following statement returns the expected results: SELECT * FROM Hugo.T1



CAUSE
This problem may occur if the login (Hugo in the preceding example) is granted the System Administrators role after the object is created. To continue the example, suppose user Hugo creates table T1 and then another administrator grants Hugo the System Administrators role. Hugo is now mapped to database owner (dbo) by default, so the ownership chain has been broken.



WORKAROUND
To work around this problem, use either of the following:
 * Have each user explicitly create objects as dbo. For example, instead of creating table t1, create table dbo.t1.

-or-
 * Do not grant the System Administrators role to the user.



MORE INFORMATION
The following script reproduces the problem: -- Create login and user: exec sp_addlogin N'Hugo', N'Hugo', N'pubs' go use pubs go exec sp_adduser N'Hugo', N'Hugo' go grant CREATE TABLE to Hugo

-- Impersonate Hugo and create the table: setuser N'Hugo' go create table T1 (id int) go insert T1 values (-1) select * from T1 go setuser go

-- Promote Hugo to a SysAdmin: exec sp_addsrvrolemember N'Hugo', N'sysadmin' go

-- Impersonate Hugo and select from the table: setuser N'Hugo' go select * from T1 go setuser go

Additional query words: sysadmin sa

Keywords: kbprb KB255067

-

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

© Microsoft Corporation. All rights reserved.