I need a quick "no" for DELETE/UPDATE/INSERT, since 3p reporting tool allows users to write their own SQL.
I know that I should probably add a new user and set permissions on tables/sp/views/etc..., and then create a new connection as restricted user.
Is there a quicker way to force a transaction or connection in SQL Server to read only mode?
Change the permissions for the user (the one used in the connection string) on the SQL Server.
I don't know. If the 3P tool is that crazy, I would be completely paranoid about what I exposed to it. I think that setting up a new user is the best thing. Maybe even just giving them certian views and/or stored procs and calling it a day.
Why are you worried about your users' ability to put arbitrary SQL in their reporting queries? If they have the rights to change data in your database, surely they can just connect to it with any ODBC client and execute the SQL directly.
I'm not sure it's 3P that's the issue here, it sounds more like you need to restrict your users but haven't.
If you have a class of users who shouldn't be allowed to change your data, then set their accounts up that way. Relying on the fact that they'll only use a reporting tool that doesn't let them change data is a security hole I could drive a truck through.
If they are allowed to change the data, restricting sessions from 3P won't help secure your system.
Unless I've misunderstood your set-up. I've been wrong before, just ask my wife. In which case, feel free to educate me.
If you have control when the connection is created and closed the you could perform a BEGIN TRAN and then do a ROLLBACK at the end. That way anything this reporting tool does will be rolled back at the end. However, if it has the ability to manage these transactions or new connections, or if the user base is unknown and potentially malicious then it is not foolproof. In addition, any large transaction may result in your database being locked by your users actions
I have to say though, the real answer is security is allocated to users. The "quicker" way you're after is a new user with just read only permissions.
Does it have to be with named users ? I have a "report" user and a "browser" user that just has select rights on most tables. Anyone that needs data uses those accounts and since they are select only I don't have to worry about them.
See Kern's link.