| Prev | Next |
SQL Server Security Permissions
The security model implemented by Microsoft's SQL Server is tight and highly configurable, supporting many different possible solutions for securing the data contained in SQL Server databases and ensuring it is only accessible to users with the required permissions. For a more detailed description and explanation of SQL Server permissions, see the SQL Server documentation.
Minimum Permissions
Enterprise Architect users who plan to add, edit and delete contents in a SQL Server repository must have permissions to execute SELECT, UPDATE, INSERT and DELETE statements on all Tables in the Enterprise Architect database. The easiest way to achieve this is to grant these database roles to each user:
- db_datareader
- db_datawriter
Additional Permissions for Diagram Images
When an Enterprise Architect repository has not been defined with the optional schema update (EASchema_XXXX_SQLServer_Update1.sql) database users will require additional permissions in order to be able to perform Project Transfers and XML imports that include diagram images.
Why is this the case? In order for Enterprise Architect to be able to store unique images in each repository the value used as the key in T_IMAGE records needs to be set; however, the original schema definition of the T_IMAGE table did not allow this and as a result Enterprise Architect needed to execute SET IDENTITY_INSERT T_IMAGE {ON | OFF} commands, which requires higher permissions; that is, the role of 'db_ddladmin'.
To avoid the need for each user to have an elevated level of permissions, an optional schema update (EASchema_XXXX_SQLServer_Update1.sql) was developed that unfortunately is not backward compatible with earlier versions of Enterprise Architect. Therefore the optional schema update (EASchema_XXXX_SQLServer_Update1.sql) should only be applied if all users are using Enterprise Architect 14 or later. See https://sparxsystems.com/resources/repositories/index.html
Does Enterprise Architect support Windows Authentication?
Enterprise Architect does support Windows Authentication. However, the type of authentication is determined by the configuration of the connection used, and not by Enterprise Architect.
Windows Authentication to SQL Server is commonly used by Enterprise Architect users, but this requires that all Windows users in Enterprise Architect be defined on the SQL Server server and be granted the security roles 'db_datareader' and 'db_datawriter' for the repository.