What are the minimum permissions required to Reverse Engineer from a SQL Server database?
The following are the minimum permissions which a user needs to be able to Reverse Engineer against a Database.
1. Public and db_datareader role in database Roles:
Reverse Engineering brings most of the items like tables, columns, roles, logins etc. but while it brings back the names of the Views but it does not bring View columns.
2. Public and db_owner role in database Roles: Reverse Engineering brings all the information from the database to the model including the columns of t he Views.
In general, to see what privileges are required, you may want to capture the SQL Trace file. This can be activated by going to Tools | Options | Diagnostics. Check the box for SQL Trace and also assign the location where you would want this file to be saved. If this option is activated, the file is created during a Reverse Engineering session and contains all the queries that ERwin sends to a database to retrieve the information. A good way will be to create a new database with nothing in it and select every thing in Reverse Engineering options. This will send all possible queries that ERwin sends to retrieve the information. In this file, look in the FROM clause to see which system catalog table ERwin is accessing this information from. It is true for almost all major databases, e.g. Oracle, Teradata, DB2 etc. Please note if the box for SQL Trace is selected, it will create a new file each time Reverse Engineering is initiated so if you don't want the file to be created, simply deselect the check box.