Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

Trying to access unowned databases?

Help
Alex
2010-01-21
2012-09-09
  • Alex
    Alex
    2010-01-21

    After using the sa login for a while I finally got around to creating a login
    for individual databases. In this case I made a user "TestLogin" and made him
    the owner of a database called "DATASTORE." I'm setting using the default
    properites that come from the sample file and I'm setting the login properties
    like so:

    sqlserver.host=localhost

    sqlserver.port=1433

    sqlserver.database=DATASTORE

    sqlserver.schemapattern=dbo

    sqlserver.user=TestLogin

    sqlserver.password=LoginsPassword

    And I'm getting the following exception:

    com.microsoft.sqlserver.jdbc.SQLServerException: The server principal
    "TestLogin" is not able to access the database "TESTDATABASE" under the
    current security context.

    (I was not only getting this for that database but for system databases like
    model) TestLogin shouldn't be able to access any other database, so this make
    sense. Any ideas what I'm doing wrong?

     
  • Alex
    Alex
    2010-01-21

    I should also note that I'm on SC 7.7 and SQL Server 2005 with the MS type 4
    JDBC drivers. I also tried crawling with just the default options ( crawl(new
    SchemaCrawlerOptions(), handler) ) and got the same exception.

     
  • Alex
    Alex
    2010-01-22

    OK, now things are just getting wierd. As a sanity check I dropped the simple
    sample code from the project home page into a main class and gave it a
    connection like so:

    SQLServerDataSource dbSource = new SQLServerDataSource();
                dbSource.setServerName("localhost");
                dbSource.setUser("sa");
                dbSource.setPassword("sapassword&&");
                dbSource.setPortNumber(1433);
                dbSource.setDatabaseName("TESTDATASTORE");
    
                Connection connection = dbSource.getConnection();
    
                final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
                final Database database = SchemaCrawlerUtility.getDatabase(connection, options);
                for (final Catalog catalog: database.getCatalogs())
                {
                  for (final Schema schema: catalog.getSchemas())
                  {
                    System.out.println(schema);
                    for (final Table table: schema.getTables())
                    {
                      System.out.print("o--> " + table);
                      for (final Column column: table.getColumns())
                      {
                        System.out.println("     o--> " + column);
                      }
                    }
                  }
                }
    

    And even that's throwing the following exception:

    schemacrawler.schemacrawler.SchemaCrawlerException: Exception retrieving
    tables: Could not locate entry in sysdatabases for database 'dbo'. No entry
    found with that name. Make sure that the name is entered correctly.

    at schemacrawler.crawl.DatabaseSchemaCrawler.crawlTables(DatabaseSchemaCrawler
    .java:455)

    at schemacrawler.crawl.DatabaseSchemaCrawler.crawl(DatabaseSchemaCrawler.java:
    163)

    at schemacrawler.utility.SchemaCrawlerUtility.getDatabase(SchemaCrawlerUtility
    .java:47)

    at com.dwu.database.Main.main(Main.java:84)

    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Could not locate
    entry in sysdatabases for database 'dbo'. No entry found with that name. Make
    sure that the name is entered correctly.

    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLSe
    rverException.java:196)

    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:246)

    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:83)

    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:39)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecu
    te(SQLServerConnection.java:1457)

    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerCo
    nnection.java:1416)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServe
    rConnection.java:1462)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.setCatalog(SQLServerConnec
    tion.java:1767)

    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.switchCatalogs(SQLSe
    rverDatabaseMetaData.java:313)

    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetFromStor
    edProc(SQLServerDatabaseMetaData.java:264)

    schemacrawler.schemacrawler.SchemaCrawlerException: Exception retrieving
    tables: Could not locate entry in sysdatabases for database 'dbo'. No entry
    found with that name. Make sure that the name is entered correctly.

    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetWithProv
    idedColumnNames(SQLServerDatabaseMetaData.java:291)

    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getTables(SQLServerD
    atabaseMetaData.java:441)

    at schemacrawler.crawl.TableRetriever.retrieveTables(TableRetriever.java:326)

    at schemacrawler.crawl.DatabaseSchemaCrawler.crawlTables(DatabaseSchemaCrawler
    .java:352)

    What's odd is that I had this code working back in December. I haven't touched
    SQLServer beyond adding a few test databases and users. I've left the schemas
    alone. Has anyone seen this before? Any clues what I might have done to cause
    SchemaCrawler to stop working in this way?

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2010-01-22

    Alex,

    It has been a while since I tested with the Microsoft SQL Server JDBC driver.
    To investigate, can we back up a little bit. Please try this:

    1. Download SchemaCrawler for SQL Server, which comes packaged with the open source jTDS driver for Microsoft SQL Server
    2. Using the command line for SchemaCrawler for SQL Server, try to see if you can output your schema to a text file

    Let me know how you do, and we can go to the next step. Thanks.

    Sualeh.

     
  • Alex
    Alex
    2010-01-22

    Thanks Sualeh. I'll give that a shot tonight and see what happens.

     
  • Alex
    Alex
    2010-01-23

    And it looks like the Microsoft JDBC driver was the culprit. Running the SC
    command line utility (and my own code) with the jTDS driver gets me much
    further and doesn't require me to use SA. The one problem is that I'm
    consistently running out of heap space. I've got to run out of the house so I
    can't play with the Java options right now. Is that normal with default Java
    settings and a database with around 500 tables?

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2010-01-23

    Alex,

    Frankly, I am not surprised. I am glad that you got much further with jTDS.
    Try tuning some of the heap space settings, and let me know what you find out.
    Thanks.

    Sualeh.

     
  • Alex
    Alex
    2010-01-23

    Unfortunately, I do seem to be back where I started. Before I was using a
    login that had a server role of sysadmin. When I go to a login that is only a
    user for an individual database I get the same error that I was getting
    before. That is, it's trying to open databases it doesn't have access to. In
    this case I'm trying to crawl DATASERVICESDB I'm getting the following
    exception:

    schemacrawler.schemacrawler.SchemaCrawlerException: Exception retrieving
    tables: The server principal "TestLogin" is not able to access the database
    "TESTDATASTORE" under the current security context.

    at schemacrawler.crawl.DatabaseSchemaCrawler.crawlTables(DatabaseSchemaCrawler
    .java:455)

    at schemacrawler.crawl.DatabaseSchemaCrawler.crawl(DatabaseSchemaCrawler.java:
    163)

    at com.dwu.database.DatabaseSchema.<init>(DatabaseSchema.java:135)

    at com.dwu.database.Main.main(Main.java:43)

    Caused by: java.sql.SQLException: The server principal "TestLogin" is not able
    to access the database "TESTDATASTORE" under the current security context.

    at
    net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)

    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:
    477)

    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedSt
    atement.java:780)

    at net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData.getTables(JtdsDatabaseMetaDa
    ta.java:1823)

    at schemacrawler.crawl.TableRetriever.retrieveTables(TableRetriever.java:326)

    at schemacrawler.crawl.DatabaseSchemaCrawler.crawlTables(DatabaseSchemaCrawler
    .java:352)

    This happens both in my code and using the command line tool. Any sugestions?

     
  • Alex
    Alex
    2010-01-23

    Also, I've been trying to do some logging with the command line utility.
    Anything up to INFO doesn't give me anything useful and anything past that
    seems to hang. I've had a run with ALL going for the past half hour with
    nothing output so far whereas normal runs complete in under a second. I'll try
    putting togethter a one table database and see if that helps.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2010-01-26

    Alex,

    Please use the -schemas command-line switch to provide a regular expression
    for the schemas that you want to use. In code, you will need to use
    SchemaCrawlerOptions.setSchemaInclusionRule().

    Sualeh.

     
  • Alex
    Alex
    2010-02-06

    The options call did the trick. I was going to complain that you're not
    honoring the rule if it's set in the properties file then I noticed that I had
    the property name spelled wrong the whole dang time. Anyway thank you again
    for all your help, Sualeh.

     
  • Sualeh Fatehi
    Sualeh Fatehi
    2010-02-06

    Good to hear, Alex. Are you back to using the Microsoft JDBC driver?

     
  • Alex
    Alex
    2010-02-06

    No, since it's not giving us any problems (and the consensus of opinion is
    that it's a better driver) we're sticking with jTDS.