locked
creating a table with non dbo schema RRS feed

  • Question

  • I am using MS SQL Server 2005. I created a database test, a user test with sql server authentication, default database test and default schema test.

    The login properties for user test show database test, default schema test with data reader, datawriter, ddladmin and public membership roles for database test.

    Now if I login as user test and create a table t1 using the following script, i get a table t1 created in database test with dbo schema (dbo.t1), but I was expecting it to create table t1 with test schema(test.t1). How do I make this user create tables under test (non dbo) schema without specifically putting it in the sql statement?

     

    create table t1t (id varchar(200), name varchar(200));

     

    Thanks for your help.

    Friday, April 9, 2010 4:39 PM

Answers

  • Hi,

    I have tested and its working fine. Just create the database and login using the script below (may be run it using admin connection)

    --CREATE Test database
    CREATE DATABASE TEST
    GO
    
    --Create Test Login
    CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[test], DEFAULT_LANGUAGE=[us_english]
    , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    
    --Adding the login test
    USE [TEST]
    GO
    CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[test]
    GO
    CREATE SCHEMA [test] AUTHORIZATION [test]
    GO
    EXEC sp_addrolemember 'db_owner','test'
    GO

    After creating the user, try the same code to create a object

    USE TEST
    GO
    CREATE TABLE t1(id INT)

    After creating the object run the script below from test credential to verify the schema name

    SELECT OBJECT_SCHEMA_NAME(OBJECT_ID('t1'))

    Vidhya Sagar. Mark as Answer if it helps!
    • Proposed as answer by David Dye Saturday, April 10, 2010 1:19 PM
    • Marked as answer by Tom Li - MSFT Friday, April 16, 2010 9:00 AM
    Friday, April 9, 2010 8:10 PM

All replies

  • Hi,

    I have tested and its working fine. Just create the database and login using the script below (may be run it using admin connection)

    --CREATE Test database
    CREATE DATABASE TEST
    GO
    
    --Create Test Login
    CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[test], DEFAULT_LANGUAGE=[us_english]
    , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    
    --Adding the login test
    USE [TEST]
    GO
    CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[test]
    GO
    CREATE SCHEMA [test] AUTHORIZATION [test]
    GO
    EXEC sp_addrolemember 'db_owner','test'
    GO

    After creating the user, try the same code to create a object

    USE TEST
    GO
    CREATE TABLE t1(id INT)

    After creating the object run the script below from test credential to verify the schema name

    SELECT OBJECT_SCHEMA_NAME(OBJECT_ID('t1'))

    Vidhya Sagar. Mark as Answer if it helps!
    • Proposed as answer by David Dye Saturday, April 10, 2010 1:19 PM
    • Marked as answer by Tom Li - MSFT Friday, April 16, 2010 9:00 AM
    Friday, April 9, 2010 8:10 PM
  • Did u try this on sql server 2005? I tried this script on sqlserver 2005 and it created the table t1 with dbo schema(dbo.t1) instead of test schema (test.t1).

     

    How do I make it create new table wilt test (non dbo) schema?

     

    Thanks

     

     

    Sunday, April 11, 2010 3:41 AM
  • Hi PatD1,

    Vidhyasagar is correct - this should be test shcema. But make sure you are running the create table statement under the TEST login and not under a sysadmin account or anyother dbo account.


    Thanks, Leks
    Monday, April 12, 2010 8:53 AM
    Answerer
  • Hi PatD1,

    If an user is member of db_owner database role, its schema will be set to dbo by default.

    So, please replace this statement:
    EXEC sp_addrolemember 'db_owner','test'
    with:
    GRANT ALL TO test
    from VidhyaSagar's script.

    For more information:
    "Ownership and User-Schema Separation in SQL Server (ADO.NET)": http://msdn.microsoft.com/en-us/library/bb669061.aspx
    "GRANT Database Permissions (Transact-SQL)": http://msdn.microsoft.com/en-us/library/ms178569.aspx

    Regards,
    Tom Li - MSFT
    Monday, April 12, 2010 12:14 PM
  • Hi Tom,

     

    When a db_owner role member has its own schema it wouldn’t use dbo anymore.

    As per Vidhya’s script, this will create a table with test schema (when we login with test account and create the table) and your argument is only true for a sysadmin account.


    Thanks, Leks
    Monday, April 12, 2010 10:28 PM
    Answerer
  • Hi Tom \ Pat,

    In the script I'm explicitly creating a schema as test and assigning it as default schema, so when s\he creates a object it should create it with test schema.

    I have tested the script in SQL 2005 Ent Ed SP3 and in SQL 2008 Ent SP1, both servers gives the desired output, i.e creates the object with test schema.

    Pat,

    As Lekss said make sure you are connected as test login and then create a new query window using this credential, probably you might using the sa login

    Wednesday, April 14, 2010 6:34 PM