Is it possible to enforce Read Only behaviour with SqlCommand?

Question

Is it possible to enforce read only permissions using the System.Data.SqlClient code accessing a Sql Server database?

I want to allow trusted users to write their own SELECT statements, in a web site.

NO Im not trolling here! Obvious solutions are to create a readonly user in the database, and use those credentials in the connection string, and surely only an idiot accepts a SQL statement in a webpage. This is a user deployment issue, I don't trust someone else to set that up correctly and don't want to write code to check that the readonly connection string is readonly.

One solution would be to parse the SQL and verify that it is a readonly command, or to do something similar. What I want to do is to do something like;

SqlConnection conn = new SqlConnection(myConnectionString, Flags.Readonly)

update Given a connection string with SA priviledges, "create user blah with password=xxx" "use my-db" "create login blah" "grant select on mytable to blah". Then make a new connection string.

Answer 1

Create a new login in SQL Server and only give that login the permissions you want on the tables. Then in the connection string have the application use that login. You mention this as an obvious solution in your post but I don't see why you wouldn't want to do it this way.

Answer 2

You could use a transaction and always rollback? (but make sure the executed sql doesn't commit)