CODEWITHSUNDEEP
c#sql-server
gav
gav

Reputation: 29122

How do I limit the permissions of a C# program to read only on an SQLServer Database?

I have written some C# which connects to a live production database. I want to give my application read only access to the DB but am unsure how to achieve this.

Is there any trivial way to get this done by amending the connection string?

My understanding is that the application will logon with the credentials of the person running the application and hence may or may not have write access to the db based on that fact. Can I statically limit the permissions of the application so that if someone changes the program to do something devastating at a later date any manipulation will fail?

Apologies for how trivial the question may be but it's my first venture into the world of MS programming.

Thanks,

Gav

Upvotes: 0

Views: 480

Answers (5)

marc_s
marc_s

Reputation: 755421

You can always grant the user connection to your database just the "db_datareader" role on that database.

db_datareader gives your user SELECT permission on all tables - but nothing more.

alt text

Upvotes: 1

Kamran Khan
Kamran Khan

Reputation: 9986

Just a thought; usually not every logged in user gets to access the database, let alone a read-right. Therefore you in your case you can create a database user for your application with read rights; and use that in your connection string.

Upvotes: 0

Guffa
Guffa

Reputation: 700840

You can't change the permissions for the account by changing the connection string. There is a user account in the database that determines the permissions.

Unless you want different people to have different permissions, you should set up an account for the application and use in the connection string.

For any account in the database you can specify which databases it can access, and what it can do with the databases. The permissions can be specified down to the level of objects and operations, so you can for example specify that it can only do selects on certain tables.

Upvotes: 1

Dan Diplo
Dan Diplo

Reputation: 25359

There are basically two main types of SQL Server authentication:

  1. Integrated Windows authentication and
  2. Mixed authentication mode (SQL Server authentication)

It sounds like you are using the former. If you use the latter you can create a specific user account within SQL Server, give it a username and password, and then grant access to the tables you wish it to be able to read. You can control at the account level what access it has to any object in the database.

See http://msdn.microsoft.com/en-us/library/ms144284.aspx for more details.

Upvotes: 0

thecoshman
thecoshman

Reputation: 8660

Have it login to the database server as a user that only has read-only permissions. That and just don't try to write anything to the database.

Upvotes: 1

Related Questions