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


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?

Who is Participating?
Aneesh RetnakaranDatabase AdministratorCommented:
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
newbreedwccAuthor Commented:
* 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.