Is always hard to me to manage roles and permissions with users connecting to SQL server with Windows authentication.
There are some users (not actually SQL users on SQL, are coming from active directory) connecting to a server with SQL 2014, they are all using windows authentication, I want them to avoid doing INSERT/UPDATE/DELETE in all tables with a particular schema but they can run stored procedures that can affect the tables on that schema.
I was thinking to create a server role and then apply that server role to each database and also for new databases created.
My idea is to create a server role, assign those users to that role and then assign the role to the different databases on the server.
Is that the right way to do it?