{"id":190,"date":"2013-06-14T20:44:51","date_gmt":"2013-06-14T20:44:51","guid":{"rendered":"http:\/\/putridparrot.com\/blog\/?p=190"},"modified":"2013-06-14T20:44:51","modified_gmt":"2013-06-14T20:44:51","slug":"sql-server-creating-a-readonly-user","status":"publish","type":"post","link":"https:\/\/putridparrot.com\/blog\/sql-server-creating-a-readonly-user\/","title":{"rendered":"SQL Server creating a readonly user"},"content":{"rendered":"<p>I created a database in SQL Server (previously created in the post on Entity Framework &#8211; Code First). I decided I wanted to limit the access to the db to a readonly user.<\/p>\n<p>So in SQL Server Management Studio I carried out the following<\/p>\n<ul>\n<li>In the Server\/Security\/Logins\u00a0right mouse click and create a new login. Login name &#8220;readonly&#8221; and set as SQL Server authentication, assign the password &#8220;readonly&#8221; for this example. Press OK.<\/li>\n<li>Under the Databases section select the database we want to apply this user to, so in this case StorePlanDatabase<\/li>\n<li>In the Security\/Users section, right mouse click and select New User&#8230;<\/li>\n<li>Click the ellipse (&#8230;) button next tot he Login name textbox<\/li>\n<li>Check the [readonly] user<\/li>\n<li>Next select the Securables item on the left of the dialog and press the Search button<\/li>\n<li>Select specific objects&#8230;, press OK<\/li>\n<li>Select Object Types&#8230;<\/li>\n<li>Check the Tables option<\/li>\n<li>Select Browse.. and check the tables you want to alter permissions for (in my case the three tables I added)<\/li>\n<li>Now Grant Select permission to each table and press OK<\/li>\n<\/ul>\n<p>Now to test this, I&#8217;m going to change the connection string in the client application from the Code First post to something this<\/p>\n<pre class=\"brush: xml; title: ; notranslate\" title=\"\">\r\nServer=MyServer;Database=StorePlanDatabase;User Id=readonly;Password=readonly\r\n<\/pre>\n<p>and with the following simply code to test this<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nusing(StorePlanDatabase context = new StorePlanDatabase())\r\n{\r\n   foreach(var p in context.Products)\r\n   {\r\n      Console.WriteLine(p.Name);\r\n   }\r\n}\r\n<\/pre>\n<p>This should now list the names of all our products stored in the database, but it doesn&#8217;t prove the permissioning worked, so if we replace the foreach loop above with<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nProduct peas = new Product\r\n{\r\n   Name = &quot;Peas&quot;,\r\n   UPC = &quot;9999&quot;\r\n};\r\n\r\ncontext.Products.Add(peas);\r\ncontext.SaveChanges();\r\n<\/pre>\n<p>We&#8217;ll get an exception due to not having valid INSERT permissions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I created a database in SQL Server (previously created in the post on Entity Framework &#8211; 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\u00a0right mouse click and create a new login. Login [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[22,23],"tags":[],"class_list":["post-190","post","type-post","status-publish","format-standard","hentry","category-database","category-sql-server"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/190","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=190"}],"version-history":[{"count":4,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/190\/revisions"}],"predecessor-version":[{"id":494,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/190\/revisions\/494"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=190"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}