This is not something common, I guess. Imagine with me, will you? You have an app dedicated for reporting. The sole reason you built this app is to allow non-developers, but data savvy people, to create operational reports for LOB applications. How do you ensure that your users will not use this solution to modify data on the fly? Imagine how fun would it be to explain why your reporting solution allow them to modify data to your boss. With me so far?
First thing to do is to ensure that the SQL user have read-only access in the desired DB.
This should prevent all writes and for most scenarios it will be enough. However, if the report user needs to execute stored procedures, we need to do more. When a user is granted execute on a stored procedure they implicitly get access to the objects within it, including write permissions. One way to prevent writes in this scenario is to use the EXECUTE AS clause in the stored procedure to limit the access granted.
One other possible solution, albeit not as elegant as the option above, is to simply start a transaction and roll it back. Any changes will not be applied because of the transaction so it should be a catch all solution.
Very edgy but fun. Cheers!