Solved

username instead of dbo while creating the table

Posted on 2011-03-02
9
700 Views
Last Modified: 2012-05-11
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26855708.html

This is on continuation to the Q that the link refers to:
When the user is trying to create a table..it is showing as [username].[tablename] instead of [dbo].[tablename]
0
Comment
Question by:anilkullam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 35016447
you have to specify
CREATE TABLE dbo.tablename
to get dbo as owner.
other wise, it will be the username as default.

unless the username has the db_owner role, in that case, the default is dbo indeed, and not the username
0
 

Author Comment

by:anilkullam
ID: 35016687
yeah i did conveyed the same to the user, but since they are running DTS packages wherein some tables are created (it is here that the schema is becoming a problem) and when comparing it with tables with dbo schema the dts is failing....previously when the users complained that they were not able to see jobs in the server, I have added the user under msdb targetserverrole n nothing else...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35016780
so, in the DTS, they have to prefix the table names also by the schema name ...
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35016897
what have you got the users default schema set to?

0
 

Author Comment

by:anilkullam
ID: 35017054
nothing...they were existing users on the server....prefixing the names to the DTS i think no...they were just executing them and no inputs and it is after getting the error  that they can get to know that something is wrong
0
 

Author Comment

by:anilkullam
ID: 35017280
how do I change schema for this particular user in sql server 2000.
starting 2005 we will be able to do this the following way but I need to do it in SQL Server 2000... help guys
USE AdventureWorks2008R2;
ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;
GO
0
 

Author Comment

by:anilkullam
ID: 35033168
@lowfatspread:Im not sure if we can set default schema in sql server 2000 since schema is introduced in 2005, however can u pls share ur views on the same

Also sorry for the extended delay
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 35037132
sql 2000 no i don't think you can either...

what are your users creating tables for?

why give them that ability?
0
 

Author Closing Comment

by:anilkullam
ID: 35110068
finally I gave admin rights since they were also part of the group and guess it fixed the prob since they didnt complain further and also with sa rights the schema will be default as dbo even in sql server 2000.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question