Wonder if someone can help. I've recently set up a SQL server that we'll be using purely for reporting purposes; its currently replicating data from our main production database which is all good. I've created a new login on the new server with read only access to ensure no-one manages to update/insert etc into the subscriber. Again, all good so far, the problem I'm having is when we get to stored procedures and functions - I'm having to grant execute permissions to the SPs and functions that different reports needs. This means I have to check through them to ensure there are no updates/inserts etc before granting the execute permissions. I was hoping someone might know a clever way to give the read only user permission to run all SPs/functions but for the user's permission to be taken into account rather than allowing the SP/function to do as is pleases....does that make sense? Essentially if the SP/function only performs reads then cool carry on but if the SP/function amends data then throw a fuss.
Any help is appreciated 🙂