{"id":3374,"date":"2020-08-11T21:37:53","date_gmt":"2020-08-11T19:37:53","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3374"},"modified":"2020-08-11T21:37:53","modified_gmt":"2020-08-11T19:37:53","slug":"new-script-to-set-up-user-on-sql-server","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3374","title":{"rendered":"New script to set up user on SQL Server"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Herewith, I provide another script to set up a user in the SQL Server environment. The idea is that a role is created on the database. The user that is subsequently created is then added as a member to that role.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First, the login is created. It is created on the master database.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE LOGIN kijker_Stagingzone WITH PASSWORD='****'<br> GO<br> CREATE USER kijker_Stagingzone FOR LOGIN kijker_Stagingzone  WITH DEFAULT_SCHEMA=[dbo]<br> GO<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Then the role is created on the database.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE ROLE db_Kijken_Stagingzone AUTHORIZATION [dbo]\nGO\nGRANT \n    SELECT\nON SCHEMA::STAGINGZONE\n    TO db_Kijken_Stagingzone\nGO<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Subsequently, a user is created and the user is added as a member to that role.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE USER kijker_Stagingzone\n    FOR LOGIN kijker_Stagingzone\n    WITH DEFAULT_SCHEMA = STAGINGZONE\nGO\nEXEC sp_addrolemember N'db_Kijken_Stagingzone', N'kijker_Stagingzone'\nGO<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">see for the latest addition:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>use master<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>drop user edgar<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>CREATE LOGIN edgar WITH PASSWORD=&#8221;<br>GO<br>CREATE USER edgar FOR LOGIN edgar WITH DEFAULT_SCHEMA=[dbo]<br>GO<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>use sqlservertomvanmaanen<br>drop user edgar<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>CREATE USER edgar<br>FOR LOGIN edgar<br>WITH DEFAULT_SCHEMA = STAGINGZONE<br>GO<br>EXEC sp_addrolemember N&#8217;db_Lezer&#8217;, N&#8217;edgar&#8217;<br>GO<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Herewith, I provide another script to set up a user in the SQL Server environment. The idea is that a role is created on the database. The user that is subsequently created is then added as a member to that role. First, the login is created. It is created on the master database. CREATE LOGIN [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3374","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3374"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3374\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3374"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}