uhm .... are you using existing tables or you are using web2py to
create them ??
dbo shouldn't affect at all the query, but a few hints nonetheless :
master is a really nasty place to create tables .... "master" db
should be left untouched .... in web2py you define a database when you
istantiate db object, and all the query done on that db won't be able
to "see" other databases
that stated, if you have your connection string as
"mssql://username:passw...@localhost/master"
the query "select * from [master].[dbo].[User] where [master].[dbo].
[User]=s" should be accomplished doing:
db.define_table('user',
.....
Field('email', 'string'),
.....
)
and db(db.user.email == s).select()
anyway, I'm not sure (I don't have MSSQL installed to try out, but I
can reply you on monday at work) that there are a few places where
this implementation could not work:
- web2py creates tables and fields with lowercase letters, so "User"
get selectable only if database is set to be case insensitive
- "dbo" stands for "dbowner" and it represent the schema which the
object (in this case, a table) belongs. if your user has not the
"db_owner" role on the db you won't be able to "see" it
A few words also on this. Having objects beloging to different schemas
under the same database it's definitely an option, but web2py
(actually the library it uses, and many others like that) can't
"choose" different schemas... they simply ignore it and the database
defaults to whatever the default is for the user querying the database
itself.
You can have different schemas to separate between permissions on
different objects in the same database, but to avoid headaches it's
always better to create a separate user that "owns" a single database
and put every data inside it.
If the user in the connection string is db_owner of the database,
"select * from dbo.table" is perfectly equivalent to "select * from
table"
If you have any other question please ask, I'll be glad to test it out
on Monday
