[AccessD] DSNLess remote tables return 'dbo' for user_name()

[Ryan W]

I just checked the big cheeses machine (he barely does anything on it) and
does NOT have the ODBC Driver 17 loaded... so apparently even Windows 10
just comes with the base driver.  Which is probably the reason I've kept
using DRIVER=SQL Server for so long, because it's the least common
denominator and does not add dependencies when rolling out the app when we
upgrade workstations.

I'll have to consider whether the move is worth it, if it's just for
compatibility with new datatypes, at this time it's not a problem since
we're still on SQL Server 2008... but have plans to move to SQL Server 2019
within the year.



On Sat, Apr 10, 2021 at 8:30 AM Ryan W <wrwehler at gmail.com> wrote:

> Thanks Stuart. Does look like I’ve got the odbc driver for 17 installed.
> Have to check my other workstations to make sure that isn’t a byproduct of
> having SSMS or some other app installed.
>
>
> Sent from my iPhone
>
> > On Apr 10, 2021, at 8:24 AM, Stuart McLachlan <stuart at lexacorp.com.pg>
> wrote:
> >
> > The latest MS ODBC drivers for SQL Server are ver 17:
> >
> >
> https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view
> > =sql-server-ver15
> >
> > Note, the link says ver15 but it does point to ver17.
> >
> > TYpe ODBC in the WIndows serarch bar and it will point you to "ODBC Data
> Sources (32 bit
> > and 64 bit)  which is actaully the ODBC Manager.   The  Drivers tab will
> tell you what you
> > currently have installed.
> >
> >> On 10 Apr 2021 at 7:17, Ryan W wrote:
> >>
> >> Thanks,
> >>
> >> Is there a way to determine the most recent ODBC driver packages with
> >> windows ? We are mostly on Windows 10 now. But don´t have any of the
> >> SQLNCLI drivers or anything like that loaded.
> >>
> >> Sent from my iPhone
> >>
> >>> On Apr 9, 2021, at 11:25 PM, Paul Wolstenholme
> >>> <Paul.W at industrialcontrol.co.nz> wrote:
> >>>
> >>> Ryan,
> >>>
> >>> I see that the driver you specify in your connection string supports
> >>> SQL Server 2000 and older.  That will not support features
> >>> implemented in newer SQL Server versions (for example the old
> >>> datatime data type is no longer recommended and the preferred
> >>> datetime2 data type is presented by that old driver as text).  It
> >>> may even be related to the problem you observe. You would be well
> >>> advised to use a driver at least as new as your SQL Server (although
> >>> if you choose too new a driver you might have to download it to each
> >>> client computer).
> >>>
> >>> For the ultimate on connection strings see
> >>> https://www.connectionstrings.com/sql-server/
> >>> MSAccess seems to require "ODBC:" at the start of the string - the
> >>> website omits that.
> >>>
> >>> Paul Wolstenholme
> >>>
> >>>
> >>>> On Sat, 10 Apr 2021 at 15:46, Paul Wolstenholme <
> >>>> Paul.W at industrialcontrol.co.nz> wrote:
> >>>>
> >>>> Ryan,
> >>>>
> >>>> Each SQL Server instance records a default database for each user
> >>>> in the login properties. You can edit this choice in SSMS or you
> >>>> can use the ALTER LOGIN command.
> >>>>
> >>>> When your connection string does not specify the database, the
> >>>> default database is the one you connect to. I hope this helps.
> >>>>
> >>>> Paul Wolstenholme
> >>>>
> >>>>
> >>>>> On Sat, 10 Apr 2021 at 06:03, Ryan W <wrwehler at gmail.com> wrote:
> >>>>>
> >>>>> I've run into a weird issue where DSN-Less ODBC connections to
> >>>>> remote tables return 'dbo' for user_name() function instead of the
> >>>>> actual username.
> >>>>>
> >>>>>
> >>>>> I found this post:
> >>>>>
> >>>>> https://microsoft.public.sqlserver.programming.narkive.com/mI2I7Mt
> >>>>> U/user-always-returns-dbo-when-using-ms-access-pass-thru
> >>>>>
> >>>>>
> >>>>> Which did in fact, "fix" the issue, the problem is if I drop the
> >>>>> DATABASE=DBNAME out of the ODBC string once I close the
> >>>>> application and re-open it, the tables cannot be opened again
> >>>>> (presumably because the connection does not tell which database
> >>>>> the table is in?)
> >>>>>
> >>>>>
> >>>>> If I convert this to a DSN connection it's fine?   What am I
> >>>>> missing here?
> >>>>>
> >>>>>
> >>>>> current string looks like:
> >>>>>
> >>>>> ODBC;DRIVER=SQL Server;SERVER=SVR1;WSID=RYAN;DATABASE=DB1;Persist
> >>>>> Security Info=false;TABLE=dbo.UserRoles
> >>>>>
> >>>>> I've also removed the Persist Security Info option and that did
> >>>>> not seem to make a difference (and Access seems to add that
> >>>>> itself)
> >>>>>
> >>>>>
> >>>>> if I do a complete passthrough query in vba and drop the
> >>>>> DATABASE=DB1 out of the string, the function select user_name()
> >>>>> does indeed return the connecting username instead of dbo? --
> >>>>> AccessD mailing list AccessD at databaseadvisors.com
> >>>>> https://databaseadvisors.com/mailman/listinfo/accessd Website:
> >>>>> http://www.databaseadvisors.com
> >>>>>
> >>>>
> >>> --
> >>> AccessD mailing list
> >>> AccessD at databaseadvisors.com
> >>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>> Website: http://www.databaseadvisors.com
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> https://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>