asked
ODBC dsn not showing new tables
Server: SQL Server 2012 SP3 / Windows Server 2012 Client: Windows 7 Pro 64 bit, SQLCLN 11 I have a user trying to retrieve tables from a SQL 2012 default instance. User has db_datareader permissions on the database. After setting up the ODBC connection, if I add a new table (WAREHOUSE.NewTable1), it does not show in the list of available tables in the ODBC connection. The user can select and view all tables in the database, including all tables in the WAREHOUSE schema, except this particular table. What I've tried so far: 1. Have the user connect via SSMS - Success, user can view the table 2. Have the user recreate the ODBC connection - Fail, user cannot view table 3. Add new table other than WAREHOUSE.NewTable1 - Success, user can select from added table, in any schema 4. Explicitly add the schema permissions to the user - Fail, user cannot view table I am convinced that it is not on the SQL Server side (security / permissions). I believe it to be in the ODBC connection, but don't know where, since it is pretty much a black box without a ton of configuration. I have seen a problem with 32 bit ODBC vs 64 bit ODBC but that was connecting to a legacy AS400 data source and I'm pretty sure that isn't a problem here. Any ideas?
Figured it out…..sort of.. The tables names are returned in alphabetical order to the ODBC connection, first by schema then by table name. The table we are adding is WAREHOUSE.STUDENT_DEGREE, which is the last table in the list (not the last object or schema or whatever, just the last name alphabetically in the database). The ODBC is dropping the last table name in the list. If we add a new table, either in the middle or at the end (with respect to alphabetical order), the last viewable table in the ODBC connection changes. Why?
17 People are following this question.
