Create table using SQL 2005 on SQL 2000 box 1

[Catadmin]

Here's an odd one.

We have SQL 2000 Servers across the board. One of our developers installed 2005 client tools on his machine so he could play with the new interface. Now he's trying to create tables via the 2005 Object Browser interface on a 2000 development box. The 2005 interface keeps creating the tables as <username>.<tblName> even though he's DBO on the database in question.

In SQL 2000, a db_owner of a database automatically has all of his objects created as dbo.<tblName> instead of <userName>.<tblName>. 2005 takes advantage of Schema naming, but we don't have any Schemas because we're using 2000 which doesn't support Schema naming.

Does anyone have a clue why the SQL 2k5 client tools would be doing this? We've opened up the Create Table SQL template, but changing it to use DBO doesn't help. He doesn't want to write the T-SQL to create his table (which I've already suggested). He just wants to right-click Tables, Click on New Table and enter his column names and data types.

Any additional thoughts on how to get the right-click -> New Table functionality to create a table with the DBO.<tblName> schema would be greatly appreciated. I've double-checked his permissions/roles on the database in question and he is indeed listed as db_owner.

Thanks!




Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[mrdenny]

If order to get the dbo by default he needs to have one of two things.

1. Be the actual owner of the database (not just be a member of the db_owner fixed database role).
2. Have sysadmin rights on the server.

The 2005 GUI is looking for his default owner, which is actually his login not dbo.

As a work around after he creates his table, he can click the "Generate Change Script" button in the button bar at the top, take the script and modify it to use the correct object owner.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Denny,

Thanks for the advice. I'm going to give that a shot.

As far as the scripting, that's what the Developer has been doing and he hates it. So, since this is a Dev box, I'm going to see about making him the actual owner of the DB and see if that makes his life easier.

I thought about the sysadmin option, but there's no reason to give him that much power. And if I do it for him, the rest of the Dev team will start begging for those rights.

I'll let you know how it works out. Thanks again!



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[mrdenny]

Yep, that's the down side of giving one of them admin rights.

When I started stripping out our developers access they started yelling about stuff like that. They told there boss, and he came to me. I told him that his developers didn't need sysadmin rights to do there job and that this was one of the little inconviences that they were going to need to just live with.

There manager told them to live with it. They stopped the b*tching.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

I also mentioned something about they were going to need to get in the habbit of using schema names for SQL 2005 anyway so now was a good time to start.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

If you are only using SQL Server 2000, why does this developer have 2005 tools installed?

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

This developer was one of the people writing the new SSIS services book for WROX. He needed the client interface installed so he could do his research. Don't know why he didn't just use the developer installation on our sandbox server via Remote Desktop instead...

The downside is, of course, that the 2k5 interface keeps telling him he only needs "X" permissions to do what he wants to, regardless of the fact that he's connected to a 2000 db that doesn't support those permissions. @=/

Of course, he still hasn't told me whether making him the literal database owner has helped him out or not. His one comment was "Will this work for any other developers who happen to be working in this database?" Yeesh! I can't win this one. @=)

Still, Star for you Denny for coming up with that solution. I greatly appreciate the assist.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com