How to enforce a readonly LocalDB connection

Question

Most people seem to have the opposite problem, but the solution is "grant access on the DB." I want to restrict access for one of two connections to the database (LocalDB). I'm afraid the answer is "create a database user and use that to connect." This is for testing only and I don't want to provision a user. Is there any way to enforce a readonly connection with LocalDB, say, through a connection string? I doubt it, but I thought I'd ask. Here's my current connection string:

<add name="readonlyConnection" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;Database=test;Trusted_Connection=True; MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

The implementation is much more complicated but basically uses the System.Data.Entity.DbContext class, if that helps.

What is the issue on creating a new user in the DB? Od course you need a user to access a db, no matter if you´re attempting to read, write or whatever. — MakePeaceGreatAgain, Jan 21 '18 at 17:53
I'm lazy and don't want to write and test more SQL just to write my tests. — Chaim Eliyah, Jan 21 '18 at 17:53
Well, not specifically, no, you don't -- `Trusted_Connection=true` uses Windows credentials. So there's a "user" on the database, but it's just the Windows account of the developer. There's no user provisioning currently. — Chaim Eliyah, Jan 21 '18 at 18:01
The default Windows credentials have `sa` permissions on the database. `DROP DATABASE`, for example, no problems. I am testing a connection with more limited permissions (in a live environment). One of the tests is to ensure that the user can't do things like that. I understand how to use the SQL permissions. If you read the question again, you'll see I am asking if it can be done another way; for example, with EF, or with options available in the connection string itself. — Chaim Eliyah, Jan 21 '18 at 18:13
My base line recommendation is to put your database users (not logins) in source control along with their permissions. That way you can use them to test with exactly the same permissions as they'll have in production. — Jonathan Allen, Jan 21 '18 at 19:14
Thanks Jonathan, unfortunately in this case we are targeting multiple databases and the readonly users have all wound up with different names. But yes, in theory, I think that's the best practice, and I'll be working to implement that. — Chaim Eliyah, Jan 21 '18 at 19:25

Chaim Eliyah · Answer 1 · 2018-01-21T19:24:13.943

Short answer: No.

Longer answer:

You can find all of the options available for connection strings here; in short, the permissions assigned to a connection are not a concern of the ConnectionString class; they're a concern of the database.

People have tried to do this with Entity Framework and it has only resulted in a series of hacks that are more work than just creating the darn user in the first place.

Here's what you do (in a context that's not readonly):

        using (var connection = (db as DbContext).Database.Connection)
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "sp_executesql";
                command.CommandType = CommandType.StoredProcedure;
                var param = command.CreateParameter();
                param.ParameterName = "@statement";
                param.Value = @"
CREATE LOGIN readonlyLogin WITH PASSWORD='testpassword'
CREATE USER readonlyUser FROM LOGIN readonlyLogin
EXEC sp_addrolemember 'db_datareader', 'readonlyUser';
                ";
                command.Parameters.Add(param);
                connection.Open();
                try { 
                    command.ExecuteNonQuery();
                } catch (SqlException ex) {     // user already exists
                    if (ex.Errors[0].Number.Equals(15025)) { } else throw ex;
                }
            }
        }

And then, in your connection strings for your test project:

<add name="readonlyConnection" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;Database=test;Trusted_Connection=False;User Id=readonlyLogin;Password=testpassword;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

How to enforce a readonly LocalDB connection

1 Answers1