PRB: Owner of a Table Cannot Access It Directly If Made Sysadmin After Creation (255067)

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