Which server role in sqlserver should I obtain to use schemacrawler?

[pastelto]

Hi, I have two questions regarding the server role of SQL Server and the getTables method in SchemaCrawler. I'm currently using SchemaCrawler with Java and SQL Server.

When I test SchemaCrawler using the 'sa' account, which has the sysadmin server role, it works. However, it retrieves all the schema data, including 'system databases' and 'database snapshots,' which I don't need. However, I am unable to retrieve the schema data with another account that has the 'public' server role. I receive the same error message as #1109: 'The server principal 'user' is not able to access the database 'database' under the current security context.'

So, my first question is: Is there a specific server role that I need to use with SchemaCrawler? I would like to use SchemaCrawler with a regular SQL Server account that has the 'public' server role.

Regarding the getTables method, when I retrieve table schemas using the 'sa' account, it fetches all the tables in my SQL Server, including 'system databases' and 'database snapshots.' However, I only need the schema for tables and columns that I have created. I looked up the Javadocs and found a few filtering options, but they didn't work for me.

Could you provide some tips on how to retrieve the schema excluding 'system databases' and 'database snapshots'? Which method should I use?

Thank you!

[sualeh]

@pastelto,

1. Since SchemaCrawler is a general purpose tool for any relational database, I am not able to advise on any particular one. I am not an expert on SQL Server permissions. If you would like me to look into it further, you can provide some test scripts to set up the two databases, and user permissions. I can help you under a consulting agreement.
2. Please take a look at the SchemaCrawler tests for SQL Server to see if there is anything you can leverage.

Sualeh.

[pastelto]

Hi, I found the reason for my problem.

When I don't set LimitOptionsBuilder, SchemaCrawler tries to get all the schema includes System Database (which some schema is not allow to look up with public account.)
If I limit to specific database with LimitOptionsBuilder, then it works.

I will close this question.
Thanks, and have a wonderful day.