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

[Paul Wolstenholme]

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