NOTICE: Our Community is moving. Get more information.
Is it possible to assign only the db_datareader privilege to a database user id that is going to be used with Enterprise Analyzer (EA)?
The db_datareader role grants a user select access rights to the user tables (ie the EA tables) within a database. The other roles and privileges that EA users require are:
Within EA there are two levels of user access the master user and the normal user. The tasks these users would perform include:
Because of the above list of tasks, the master user will need to create, drop and alter tables. The master user will also need insert / delete / update and select data from the tables as well. This means that the master user requires db_owner role for the database.
A normal user will do tasks like:
If EA is started so that it connects to the MS SQL database with a user id that has the following privileges assigned to them:
Then this will lead to the error shown below.
The reason for this error is that EA will create an activity log for every session that is attached to the workspace. To be able to create the activity log, the user must have insert privilege to the relevant table. The db_dataread only grants select privileges to the user tables.
Also, most operations that a normal user performs involves storing data into the database behind the workspace. All the lists that hold the results of the queries that users perform against the source modules in the workspace are held within the database.
Assigning tags to modules requires updates to the tables in the workspace. Changing any of the options within EA means that the database is updated to point to the correct options files. Changing the projects that modules are in requires updates to the database tables.
This means that even normal users require the db_datawriter role as this grants a user the privileges to insert, delete and update data within the user tables.
Below is an example of the roles that a normal user should be setup with to use an EA database.
The user must have read and write access to all of the workspace directories, especially if the workspace is accessed via a network share.
When granting access to the database for a workspace, the user needs to be assigned the default schema of dbo. When EA creates the tables in the database, it creates them under the dbo schema. By assigning the default schema of dbo, this means that EA will be able to access the tables.
For more information on the different roles that can be used within a MS SQL database see this link: http://msdn.microsoft.com/en-us/library/ms189121.aspx
Incident # 2788322