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

Paul Wolstenholme Paul.W at industrialcontrol.co.nz
Fri Apr 9 22:46:10 CDT 2021


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
>


More information about the AccessD mailing list