Menu
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>
or
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 read-write, and any attempt to change them (currently) crashes the SQL Server database.
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 read-only as it absolutely, positively can't change the DB data.
476
Right-click on your connection and select "Properties". You will get the Properties window for your connection. Find the "Connection String" property and select the "connection string". So now your connection string is in your hands; you can use it anywhere you want.
The connection string is an expression that contains the parameters required for the applications to connect a database server. In terms of SQL Server, connection strings include the server instance, database name, authentication details, and some other settings to communicate with the database server.
A read-only database allows users to read but not modify data. You can set the database to READ_ONLY in T-SQL using ALTER DATABASE: ALTER DATABASE MyDatabase SET READ_ONLY; You can also set the database to read-only from the Object Explorer in SQL Server Management Studio.
Databases can be set to READ ONLY mode and back using T-SQL and SSMS. For our testing we will use the sample database AdventureWorks to carry out all operations. Once a database is changed to READ ONLY nothing will change in your database.
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.
153
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
