Prev Next

SQL Server Security Permissions

The security model implemented by Microsoft's SQL Server is quite powerful 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.

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 the database roles of 'db_datareader' and 'db_datawriter' to each user.

Additional Permissions for Diagram Images

When an Enterprise Architect repository has not been defined with the optional schema update  (EASchema_1220_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.  ie. the role of 'db_ddladmin'.

To avoid the need of each user to have an elevated level of permissions, an optional schema update (EASchema_1220_SQLServer_Update1.sql) was developed which unfortunately is not backward compatible with earlier versions of Enterprise Architect.  Therefore the optional schema update (EASchema_1220_SQLServer_Update1.sql) should only be applied if all users are using Enterprise Architect 14 or later.  see https://www.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.

Learn more