SQL, SQL Server 2005 Default Schema for user set, but selecting from table without schema qualifier fails

Posted on 2007-08-08
Last Modified: 2013-11-05

I am trying to run an ARAS demo. I have a sample database that I've restored to SQL Server 2005. They use a schema called innovator, so all the tables are prefixed ie innovator.Vendors.

There is also a user called innovator. The default schema for the user is set to innovator.

If I connect (enterprise manager) as user innovator, I should be able to say "select id from vendor" and get a result. However, this fails (Invalid object name 'USER'.), I have to say "select id from innovator.vendor", but this is not how their app is coded, and so I can't use the app.

What must I do so that, if I connect as user innovator, I don't have to specify the qualifier, and the table specified in a query will still be found?

Question by:newbreedwcc
    LVL 75

    Accepted Solution

    usually when u connect as a user 'innovator', the query "SELECT * FROM Users" will fetch the records from "Innovator.Users" if there is already a table named "Users" owned by the user "Innovator" otherwise it will fetch the records from "dbo.innovator"
    In this case you can have two approaches
    1. specify the object owner in those queries, which is the BEST approach
    2. change the owneship of existing objects using sp_ChangeObjectOwner

    Author Comment

    * There is a table called User, since select * FROM innovator.[user] returns results for me
    * I won't have the option to change the queries, since it's not my code
    * It looks like sp_ChangeObjectOwner was deprecated for SQL Server 2005 (works for SQL Server 2003)

    From what I've been reading, it's all about the schema (2005 doesn't assign direct ownership for a table, but uses different schemas instead) - but how do I make it pick up the schema it should use for a given user? I've set the "Default Schema" for the user but this doesn't seem to help.

    Here's what I have checked:
    Database --> Security --> Users --> innovator:
    - Default Schema = innovator
    - Owned Schmas: innovator (will it mess things up if the user owns the dbo schema also? )
    - Role Membership: db_owner

    When I go to Security --> Schemas, there isn't much I can edit other than the schema owner, which is set to innovator.

    Thanks again

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now