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

[Stuart McLachlan]

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