SQL Server Performance

how to create a table using schema other than DBO?

Discussion in 'SQL Server 2005 General DBA Questions' started by cutiepie, Sep 27, 2005.

  1. cutiepie New Member

    Hi all,
    i m using sql server2005 CTP...i created a database called TEL and in that database i created a user(in security) as
    USE [TEL]
    GO
    /****** Object: User [COLL_DB] Script Date: 09/27/2005 15:38:51 ******/
    GO
    CREATE USER [COLL_DB] FOR LOGIN [loginName] WITH DEFAULT_SCHEMA=[COLL_DB]

    Now,when i m trying to create a table in the database TEL as
    CREATE TABLE [COLL_DB].abc (c numeric)
    commit;

    it gives me error saying
    The specified schema name "COLL_DB" either does not exist or you do not have permission to use it.

    Now,can someone tell me...what i have to do to fix this error?????????
    thanks...
  2. chopeen Member

    I've just found this.

    It looks to me like Microsoft changed the way fully qualified names are constructed. [?]

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  3. chopeen Member

    OK, I know what the problem is.

    Read this - it says that 'if DEFAULT_SCHEMA is left undefined, the database user will use dbo as its default schema'. So it means that you should use the WITH DEFAULT_SCHEMA clause with CREATE USER.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  4. cutiepie New Member

    Thank you very much...it worked...thnks for your suggestions..i really appreciate it..

Share This Page