SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Schema issues
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/30/2013 :  09:15:11  Show Profile  Reply with Quote
The tables I've been creating were previously saving as dbo.tablename but now they're saving as UERNAME.tablename. Why? How do I change it back to .dbo? My DTS package is not working properly because it can not form connections to the USERNAME shcema and I dont know how this happened.

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/30/2013 :  09:30:04  Show Profile  Reply with Quote
This is happening because the default schema of the user who is creating the table is not dbo, but is the username. If you change the default schema (in SSMS object explorer, databasename -> Security -> Users), any new table created will use the new schema.

Alternatively, you can explicitly specify the schema name while creating the table. (i.e., dbo.MyTableName instead of just TableName).

You can transfer the tables already created to dbo as well. For example this transfers the table to dbo:
ALTER SCHEMA dbo TRANSFER [USERNAME].[TestTable]
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/30/2013 :  09:48:22  Show Profile  Reply with Quote
So I went into my username (as I am the user) and I changed the default schema to dbo. Is that all I need to do?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 01/30/2013 :  09:49:56  Show Profile  Reply with Quote
Going forward, that is all you should have to do. Try creating a one test table as that user and test it out to be sure. That, of course, would not have changed the ownership of any tables that have already been created.
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/30/2013 :  15:18:34  Show Profile  Reply with Quote
Thank you James. Again :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000