set sql server connection to readonly?

How do I set a SQL server connection to readon开发者_开发问答ly? 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;");

---

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

---

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

---

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.

---

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

---

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...