Create table ownership problem

[mkal]

I have an user who's been added to the dbo role in a sql 2000 database. When using Sql Server 2005 Management Studio to create the table the table always has the name domain\login_name as the owner of the table. It doesn't make any difference if he adds the dbo to the name before he goes to save it.

Example: He saves the table as test1, the full name is domain\user_name.test1. If he saves the table as dbo.test1, the full name is domain\user_name.dbo.test1

However, if he uses T-sql to create the table like this:
CREATE TABLE dbo.test1 (...) then the table is created with the name dbo.test1 not domain\user_name.test1.

Anybody come across this before. I've tried removing him as an user from the database and adding him back in but nothing changes.

 

[intelwizrd]

I have had the same problem. I don't remember why it happens but if the user is not a member of the SysAdmin role it uses the username.tablename format. We now limit who can make tables to the sysadmin users.

 

[mrdenny]

This is a known issue with SQL 2000. Unless the user's login is mapped to the dbo user when creating a table with the designer the table will always be created with the user as the owner. (When the user is dbo its still created with the user as the owner, but the user is now dbo.)

The work around is to make the user a sysadmin (a really bad solution) or have them use a T/SQL script to create the table.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog