When deploying our product to different customers, the question often comes up with what are the SQL User Minimum Permissions needed to run the software. We aren’t adding anything that would require more than db_datareader and db_datawriter for the minimum permissions. I’ve discovered that the numberIdService may create additional schema if needed which would obviously exceed these permissions. If we have the schema already created then this doesn’t seem to be an issue.
What does Cuba recommend for the minimum user permissions for the SQL Connection for production? Studio will obviously require more permissions as we are manipulating schema. Are there other areas of concern like numberIdService that we should be aware of?
I’m not good at SQL Server permissions, so I’ll explain in generic terms.
Normally, only permissions to read and write data to the database are required. If you use BaseLongIdEntity/BaseIntIdEntity subclasses, or UniqueNumbers service, the framework will create sequences or tables (depending on your SQL Server version) on the fly, so you may need appropriate permissions. However, you can watch what objects are created in dev environment, and prepopulate your production database, so the framework will find the needed objects and won’t create anything.
Also, if you run database migration automatically (cuba.automaticDatabaseUpdate = true), it goes through the same connection and obviously requires rights to modify the schema.