Read Only DB Connection Strings
This seems like a really silly question, but I've had a search around and I can't find anything about this.
I've got a DB connection string that I'm creating in my web.config:-
<connectionStrings> <add name="DBConn" connectionString="Data Source=<db svr>;Initial Catalog=<dbname>;Integrated Security=True" providerName="System.Data.SqlClient /> </connectionStrings>
Data Source=<db svr>;Database=<db name>;User ID=<uname>;Password=<pword>;
but I need this connection to be read only. I've defined all my linq objects with only gets on their properties, and none of my (MVC) repository classes have .SubmitChanges() methods in them so I'm 99% sure the system can't update this DB, but I would also like to set my DB connection to be RO if at all possible. I realise that ideally this should be done at the SQL server end and the user should be made RO, but that (for various reasons, out of my control) can't be done, so I wanted to lock down my connection as the app mustn't write to the DB.
Is there a "readonly" parameter I can apply to the connection string so that it would throw an error or discard the data if any updates were attempted?
Just to reiterate (the 1st answer I had, when asking this, on another forum was "change your DB credentials") I cannot, in any way, change the DB access credentials, these are RW and any attempt to change them (currently) crashes the SQL server DB. This is not my problem, and I can't look at resolving that issue, so that's why I want to look at making the DB connection RO as it absolutely, positively has to kill every.... errr, I mean absolutely, positively can't change the DB data.
What you have under your control is classes to acces code (L2S). I suggest to override in a partial class the SubmitChanges for your datacontext in order to do nothing ( or even throw an error!) (or implementing all extensibility methods InsertObject, UpdateObject or DeleteObject that belong to your datacontext)
No, there is no way (that I know of). Unfortunately for you, the right way to do it would be to change the grants of the current user, or create a new user with only select privileges. I realize this is not the answer you are looking for but having a Sql Server that crashes when you try to change things in it seems to be a problem that is really worth looking into. Is it because you are using the "sa" account to connect? If so you should create another user and grant the appropriate permissions to the new user.