none
Schema name not beign prefixed with table

    Question

  • Hi,

    In SQL Server 2005, I have created a login in from Windows (Mydomain/USR95) and created user in my Test database with same name  (Mydomain/USR95). Also when I created user added Mydomain/USR95 as default schema to the user. The user has db_reader, db_writer and db_owner database roles granted. My issue is when the user creates a table without mentioning any schema, the table gets created with dbo prefix, I want it get created with user schema like - [Mydomain/USR95].MyNewTable. Anyting went wrong when I created user or Can you suggest a way for getting it corrected?

    Thanks,

    Ashru


    MCTS, http://asharafaliptb.wordpress.com


    • Edited by Ashru Monday, April 01, 2013 5:47 AM
    Monday, April 01, 2013 4:12 AM

Answers

All replies

  • Is that login is member of sysadmin server role? Is dbo is a default schema?

    http://msdn.microsoft.com/en-us/library/aa905208(v=sql.80).aspx

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, April 01, 2013 6:39 AM
  • Yes the user is member of sysadmin server role. The default schema is user created one(not the dbo). But when I remove sysadmin server role and grant all the database role, still user is not ablve to select records from tables. That is the reason I granted sysadmin. My requirement is The user should have all the permission on the the Test database(should be able to see data of other schema also) and when create table it should be created as  [Mydomain/USR95].MyNewTable

    Thanks Uri Dimant

    Ashru


    MCTS, http://asharafaliptb.wordpress.com

    Monday, April 01, 2013 7:54 AM
  • If the login is a member of sysadmin, the login is dbo in all databases, and thus always have dbo as the default schema.

    I don't know exactly what you did wrong, put probably you used the GUI where it easy to get lost. It's better to use T-SQL statements, or at least use the Script button to script what the GUI generates.

    The script below shows that it works as you want it to. For the test I created a user without a login and impersonate that user, but it is the same if you create a Windows login.

    CREATE SCHEMA nisse
    go
    CREATE USER nisse WITHOUT LOGIN WITH DEFAULT_SCHEMA = nisse
    EXEC sp_addrolemember 'db_owner', 'nisse'
    go
    EXECUTE AS USER = 'nisse'
    go
    CREATE TABLE nisse (a int NOT NULL)
    go
    REVERT
    go
    SELECT * FROM nisse.nisse
    go
    DROP TABLE nisse.nisse
    go
    DROP USER nisse
    DROP SCHEMA nisse


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 01, 2013 8:29 AM