We are currently moving to SQL Server and are setting up initial permissions for our environment. Does anyone have any best practices to set up:
We plan to give an application its own user for security purposes and then allow that user to have access to what is needed.
How do we set it up so that developers can develop their stored procedures and get permission to what they need without getting blocked by permissions issues.
Is there a way to allow any stored procedures to have execute permissions if it belongs to a specific schema?
Essentially we have 4 Databases with many tables with many applications that access the various tables. We want to make it easy to manage but also easy to know what each application has permissions to do. For example we only want ReadOnly access for those tables that the application only needs readOnly to and we we want update/delete/insert to those that require it..
Any documentation on best practices out there? We have a lot of stored procedures that access tables from all the different database as well.
What security should an application user have so that when a new stored procedure is added they don't need to be granted specific privileges for the new stored procedure ? is that even possible? What if I create a new schema and add all the stored procedures for this application inside of it. Can I grant execute to the schema level ? thoughts?
Thanks in advance.