set sql server connection to readonly?

Question

How do I set a SQL server connection to readonly? I tried Googling and all I found was File Mode=Read Only, but it didn't work (File Mode keyword not supported). The reference looked SQL CE specific.

No luck with SQLite Read Only=True either.

-edit-

My connection string is below. I have no clue when it comes to configuring the tables. I don't know how to make users/permissions.

rdconn = new SqlConnection(@"(wrong)Read Only=True;Server=.\SQLExpress;AttachDbFilename=test2.mdf;Database=dbo;Integrated Security=SSPI;User Instance=True;");

Tass

What type of authentication do you use? – Solution

You could begin a transaction, do work, then rollback transaction. Not quite what you're wanting, but this would ensure no changes were made to the database. – Reprovable

Answer 1

Just set on current user's permissions to SELECT only.

Is that what you want?

Click on the current db in SQL Server Management Studio, after click on Security->Users. Find your user, right click on him -> properties->Securable. Just mark SELECT, unmark all others.

Here're links on managing permissions

http://www.databasejournal.com/features/mssql/article.php/2246271/Managing-Users-Permissions-on-SQL-Server.htm

http://www.mssqlcity.com/Articles/Adm/manage_users_permissions.htm

Just found a a free tool for managing permissions. It can be useful too. Check the link

http://www.idera.com/Products/Free-Tools/SQL-permissions/

UPDATE:

If you want the DB to be read-only to any user:

ALTER DATABASE database-name SET READ_ONLY

or read here for more information

http://www.sqlservercurry.com/2009/03/set-database-to-read-only-mode-using.html

http://www.blackwasp.co.uk/SQLReadOnly.aspx

Solution

i was hoping that there was, like the original poster was hoping, a way to set my connection to be read only, using the same SQL Server authentication credentials that others are currently using. Similar to i am an administrator, and UAC strips those rights away. And how i'm now standard user, and protected mode strips those rights away. – Eremite

@IanBoyd , Unfortunately, there's no way to achieve read-only connection string in ms sql. Here's list of connection string parameters connectionstrings.com/all-sql-server-connection-string-keywords . – Solution

Answer 2

Change the security settings for the used login in SQL server.
For instance only db_datareader.

Neldanelia

Answer 3

It's really simple.

If your db-user is called MyApplicationWebServices_EN

Create a new login called MyApplicationWebServicesReadOnly_EN, with default language English (or whatever you need), who is member of the server-role "public" only (default).

In the DB you want the user to have access to, create a new user called MyApplicationWebServicesReadOnly_EN, and map it to login MyApplicationWebServicesReadOnly_EN.

Right-click the user in the database, and select properties -> general In the Select-box list for "Membership in database roles", select db_datareader only (make sure all others options are unchecked).

Now use MyApplicationWebServicesReadOnly_EN in your connection string as "user id", and you have your read-only mode..

Of course, if your entire db should be read-only, then

ALTER DATABASE your_database_name SET READ_ONLY

will do just fine, as hgulyan said.

Robinia

While your answer seems the closest to a real solution to the original question, I think 'really simple' must be 'really relative' here ;p – Amanita

Answer 4

In some cases, for instance in a High Availability instance in Azure, you may have a read-only replicated duplicate of your database. In that case, you add

;applicationintent=readonly

to the connection string to have that user reach the Read-Only replica.

More at Microsoft...

Andra

Answer 5

If you want to access the database in readonly mode, you can create a user for database and can enable 'READ' rights and disable 'WRITE' rights

you can see the users under database in sqlserver select that and if you want you can create new user else you can set the rights in properties

Also refer

http://www.zimbio.com/SQL/articles/-jf4iDK7qWQ/Set+database+read+only+mode+using+SQL+Server

Aldric