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;");

Answer 1

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

Answer 2

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

Answer 3

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