[FEATURE] SQL Server Connector expects Dbo (i.e ROOT) privilege work - it lists only the databases created by user who is issqluser, Most prod deployment have DB created by user which is not issqluser

[ssenathi]

Is your feature request related to a problem? If yes, please describe. We are trying to query SQL Server where we have given a user who has all the query access, but when we connect thru SQL Server connector i get no databases listed . Upon looking into the code listDatabaseNames function it is filtering the databases where principle_id as user id and sqluser. Most of the production installation wont have this setup and thus make this connector useless.

Describe the solution you'd like listDatabasenames should list all the databases without checking issqluser =1

Describe alternatives you've considered Alternative is to clone this repo and create own which though works, want to understand why the author has created this filter.

Additional context Add any other context or screenshots about the feature request here.

[ssenathi]

The function is in SQLServerMetadataHandler.java and function code is below and issue with the highlighted one

private Set listDatabaseNames(final Connection jdbcConnection) throws SQLException { String queryToListUserCreatedSchemas = "select s.name as schema_name from " + "sys.schemas s inner join sys.sysusers u on u.uid = s.principal_id " + "where u.issqluser = 1 " + "and u.name not in ('sys', 'guest', 'INFORMATION_SCHEMA') " + "order by s.name"; try (Statement st = jdbcConnection.createStatement(); ResultSet resultSet = st.executeQuery(queryToListUserCreatedSchemas)) { ImmutableSet.Builder schemaNames = ImmutableSet.builder(); while (resultSet.next()) { String schemaName = resultSet.getString("schema_name"); schemaNames.add(schemaName); } return schemaNames.build(); } } }

[ssenathi]

Sorry closed by mistake

[josiahpeters]

@ssenathi Thank you for opening up this issue.

We have the same problem. Our databases were created by Windows Auth Accounts not SQL Auth Accounts. This prevents the metadata from being extracted by the lambda.

I can manually use the athena cli tool to look up the metadata in the tables for the database with the dbo schema. However we're prevented from seeing the tables and schema in the Athena Query Console.

Would you be available to share your forked version of the sql server federation lambda that solves this?

[hdinizribeiro]

Any news on this topic? I'm having the same issue