SQL, SQL Server 2005 Default Schema for user set, but selecting from table without schema qualifier fails
Posted on 2007-08-08
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?