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

Ryan W wrwehler at gmail.com
Fri Apr 9 13:03:02 CDT 2021


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?


More information about the AccessD mailing list