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

[Ryan W]

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/mI2I7MtU/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