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

[Ryan W]

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