We have a database that was accessed as user MARY on the old SQL Server. All the tables are:
MARY.<tablename>
The application assumes it can access tables by name only. E.g. select * from <tablename>
rather than select * from MARY.<tablename>
We have a new userid that the software uses to access the database: APPSERVER
.
When APPSERVER logs in, it has full rights to the DB, but can only access the tables as MARY.<tablename>
.
So the code will not run.
How do I either:
-
get new user APPSERVER to magically map to
MARY.
to access tablesor
-
update all tables so they are now:
APPSERVER.<tablename>
.
(This is all via SQL native auth, not integrated.)
Best Answer
Solved by adjusting the SQL user id to have the appropriate default schema... and tada!