Andy Brownsword brings out the hangman:
We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures.
Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy.
So let’s fix that.
The nice thing about this role is that you can then introduce module signing and allow the stored procedures to do things that you might not want regular users to do, such as truncate tables, start and stop SQL Agent jobs, etc. That way, users don’t have these advanced permissions, but the application (whose account has the stored procedure execution role) can do what it needs to do. You can, of course, also have other roles, like one for the kinds of things I mentioned and another to execute most stored procedures. Maintaining that gets a little trickier, but is doable.