Metadata from SQLServer error - No tables SQL provided

[petersonjr]

Issue

Hello, I am using SchemaCrawler to get metadata info from a SQL Server DB.
When I run SchemaCrawler against a database via commandline, I get the following error:

schemacrawler.schemacrawler.SchemaCrawlerException: No tables SQL provided

The command is as follows:

schemacrawler.exe -url 'jdbc:sqlserver://server:port' -u 'user' -password 'password' -server 'sqlserver' -fmt 'json' -schemas 'DBINFO\..*' -c schema -loglevel=ALL

The user that I connect to DB with has only permissions to view definition of the database (grant view definition to user).
I tried using the Java API to achieve same goal, and notice a few things:

• When I use "metadata" as Metadata Retrieval Strategy , or "metadata_all", I get no errors but also no table information. Seems that the sql server jdbc does not return metadata definitions, even the user having granted view definitions.
• When I use "data_dictionary_all" as Metadata Retrieval Strategy I get the same error as in command line: "No tables SQL provided"
• I noticed that schemacrawler-sqlserver lib has no TABLES.sql resource file. Is this the cause of the error?

Therefore, I kindly ask a few questions:

• What does the 3 Metadata Retrieval Strategies mean? And how they work?
• Why schemacrawler-sqlserver lib has no query to get tables information? Is it a limitation of SQL Server?
• To get table information through metadata retrieval strategy in sql server is necessary to have select permission on the database? Why just view definition permission is not enough? (I know this last question is a little specific)

Thank you for the time and effory you put in this useful tool,

Péterson

Environment

INFORMAÃıES: Environment:
SchemaCrawler 15.06.01
Windows 7 6.1
"Oracle Corporation" Java HotSpot(TM) 64-Bit Server VM 10.0.1+10

[sualeh]

Péterson,

In order for me to look into this further, please use -loglevel=ALL, and give me the logs. Also, please send me instructions on how to set up a SQL Server user with just grant view definition permissions, and I will try to reproduce.

• What does the 3 Metadata Retrieval Strategies mean? And how they work?
  • metadata - uses JDBC metadata calls to retrieve information one table at a time; each JDBC driver decides how to honor these requests
  • metadata_all - uses JDBC metadata calls to retrieve information for all tables together; each JDBC driver decides how to honor these requests
  • data_dictionary_all - use the INFORMATION_SCHEMA or database-specific data dictionary queries to retrieve information for all tables together
• Why schemacrawler-sqlserver lib has no query to get tables information? Is it a limitation of SQL Server?
  This is because the JDBC metadata queries were sufficient for getting table information.
• To get table information through metadata retrieval strategy in sql server is necessary to have select permission on the database? Why just view definition permission is not enough? (I know this last question is a little specific)
  I am not a SQL Server expert, but I can help you investigate further. I think you would need SELECT permissions on the database, but I can check if you can help me reproduce the issue.

Sualeh.

[petersonjr]

Hello Sualeh,

thank you for the answer.

I just sent you the logs via email, since I don't want them to be public.

I am also not an expert in SQL Server, but here what we did was to create a user and grant it just view definition authorization, since we wanted the user not to be able to query any database, but collect the metadata from them. The command is something like:

USE AdventureWorks 
GO 
GRANT VIEW Definition TO User1

To reproduce the error it is important to make sure User1 does not have select permission on database, but has view definition.

---

To complement the thread, I did a little investigation on SQL Server JDBC database, and found this:

getTables Method (SQLServerDatabaseMetaData) - SQL Server | Microsoft Docs

For more information about the data returned by the getTables method, see "sp_tables (Transact-SQL)" in SQL Server Books Online.

And the source code of the JDBC driver:

mssql-jdbc/SQLServerDatabaseMetaData.java at 7f67d20becc17bb72148239f09d34f0e34ad16cc · microsoft/mssql-jdbc

    enum CallableHandles {
        SP_COLUMNS("{ call sp_columns(?, ?, ?, ?, ?) }", "{ call sp_columns_100(?, ?, ?, ?, ?, ?) }"),
        SP_COLUMN_PRIVILEGES("{ call sp_column_privileges(?, ?, ?, ?)}", "{ call sp_column_privileges(?, ?, ?, ?)}"),
        SP_TABLES("{ call sp_tables(?, ?, ?, ?) }", "{ call sp_tables(?, ?, ?, ?) }"),
        SP_SPECIAL_COLUMNS("{ call sp_special_columns (?, ?, ?, ?, ?, ?, ?)}", "{ call sp_special_columns_100 (?, ?, ?, ?, ?, ?, ?)}"),
        SP_FKEYS("{ call sp_fkeys (?, ?, ?, ? , ? ,?)}", "{ call sp_fkeys (?, ?, ?, ? , ? ,?)}"),
        SP_STATISTICS("{ call sp_statistics(?,?,?,?,?, ?) }", "{ call sp_statistics_100(?,?,?,?,?, ?) }"),
        SP_SPROC_COLUMNS("{ call sp_sproc_columns(?, ?, ?,?,?) }", "{ call sp_sproc_columns_100(?, ?, ?,?,?) }"),
        SP_STORED_PROCEDURES("{call sp_stored_procedures(?, ?, ?) }", "{call sp_stored_procedures(?, ?, ?) }"),
        SP_TABLE_PRIVILEGES("{call sp_table_privileges(?,?,?) }", "{call sp_table_privileges(?,?,?) }"),
        SP_PKEYS("{ call sp_pkeys (?, ?, ?)}", "{ call sp_pkeys (?, ?, ?)}");
        // stored procs before Katmai ie SS10
        private final String preKatProc;
        // procs on or after katmai
        private final String katProc;

        private CallableHandles(String name, String katName) {
            this.preKatProc = name;
            this.katProc = katName;
        }

        CallableStatement prepare(SQLServerConnection conn) throws SQLServerException {
            return conn.prepareCall(conn.isKatmaiOrLater() ? katProc : preKatProc);
        }
    }

I guess the information above means that SQL Server JDBC driver uses sp_tables command to get tables information. And here's what sp_tables documentation says:

sp_tables (Transact-SQL) - SQL Server | Microsoft Docs

Returns a list of objects that can be queried in the current environment. This means any table or view, except synonym objects.

Therefore, I think the cause of the error is that in order for the SQL Server Jdbc Driver to return table information, the user must have permission to query the database.
I also think that if the user does not have this permission, schemacrawler would have to use data_dictionary_all method to retrieve information, and thus a tables.sql would be necessary.

Is this correct?

Thank you again,

Péterson

[sualeh]

Péterson,
I cannot reproduce the issue.
I created a new user like this:

USE BOOKS;
CREATE LOGIN schemacrawler1 WITH PASSWORD = 'schemacrawler1';
CREATE USER schemacrawler1 FOR LOGIN schemacrawler1;
GRANT VIEW DEFINITION TO schemacrawler1;

Then I ran SchemaCrawler like this:
./schemacrawler.sh --url jdbc:sqlserver://database-1.cu6lnuk9rqla.us-east-1.rds.amazonaws.com:1433;databaseName=BOOKS;applicationName=SchemaCrawler --schemas=BOOKS.dbo --user=schemacrawler1 --password=schemacrawler1 --info-level=standard --command=schema

and it worked.

Sualeh. 

[petersonjr]

Hello Sualeh,

I will check it out again and get back to you.
In the meantime can you please try to run SP_TABLES command in SQL Server with the user schemacrawler1? I think this is related to the issue. If the user has permission to execute this command, it will get tables information.

Also, can you force schemacrawler to use data_dictionary_all strategy? The issue is also related to that, since schemacrawler-sqlserver does not have a tables.sql file which should contain queries to return tables information from the data dictionary.

Péterson

[sualeh]

@petersonjr I followed your instructions on how to create a database user, and the new user has permissions to run sp_tables. Please send me your script, so I can create a new user in the same way that you did.

There is development work needed to support the data_dictionary_all strategy for Microsoft SQL Server. I want to reproduce the issue to see whether it will be worth the work to add this functionality.

[sualeh]

@petersonjr I am closing this issue. Please send me more information when you have it.