SQL Server creating a readonly user

I created a database in SQL Server (previously created in the post on Entity Framework – Code First). I decided I wanted to limit the access to the db to a readonly user.

So in SQL Server Management Studio I carried out the following

  • In the Server/Security/Logins right mouse click and create a new login. Login name “readonly” and set as SQL Server authentication, assign the password “readonly” for this example. Press OK.
  • Under the Databases section select the database we want to apply this user to, so in this case StorePlanDatabase
  • In the Security/Users section, right mouse click and select New User…
  • Click the ellipse (…) button next tot he Login name textbox
  • Check the [readonly] user
  • Next select the Securables item on the left of the dialog and press the Search button
  • Select specific objects…, press OK
  • Select Object Types…
  • Check the Tables option
  • Select Browse.. and check the tables you want to alter permissions for (in my case the three tables I added)
  • Now Grant Select permission to each table and press OK

Now to test this, I’m going to change the connection string in the client application from the Code First post to something this

Server=MyServer;Database=StorePlanDatabase;User Id=readonly;Password=readonly

and with the following simply code to test this

using(StorePlanDatabase context = new StorePlanDatabase())
{
   foreach(var p in context.Products)
   {
      Console.WriteLine(p.Name);
   }
}

This should now list the names of all our products stored in the database, but it doesn’t prove the permissioning worked, so if we replace the foreach loop above with

Product peas = new Product
{
   Name = "Peas",
   UPC = "9999"
};

context.Products.Add(peas);
context.SaveChanges();

We’ll get an exception due to not having valid INSERT permissions.