asked on

username instead of dbo while creating the table

https://www.experts-exchange.com/questions/26855708/Unable-to-view-jobs-in-sql-server-2000.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]

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anilkullam

ASKER

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...

Guy Hengel [angelIII / a3]

so, in the DTS, they have to prefix the table names also by the schema name ...

Lowfatspread

what have you got the users default schema set to?

anilkullam

ASKER

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

anilkullam

ASKER

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

anilkullam

ASKER

@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

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anilkullam

ASKER

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.