{"id":83,"date":"2014-01-02T23:05:17","date_gmt":"2014-01-02T15:05:17","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=83"},"modified":"2014-01-02T23:20:56","modified_gmt":"2014-01-02T15:20:56","slug":"how-to-create-user-defined-server-roles-in-sql-server-2012","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html","title":{"rendered":"How to : Create User Defined Server Roles in SQL Server 2012"},"content":{"rendered":"

Salah satu fitur baru di SQL Server 2012 adalah adanya User Defined Server Roles. dengan adanya fitur ini kita bisa menambahkan Server Roles baru selain yang ada secara default di SQL Server seperti gambar yang ada dibawah ini<\/p>\n

\"\"<\/a><\/p>\n

Dengan adanya fitur ini seorang\u00a0 super admin dapat mendelegasikan tugasnya ke admin yang lain dengan menerapkan jenis Server Roles yang sudah dimodifikasi. Sehingga dengan adanya fitur ini akan menjadi lebih fleksibel dalam pengaturan otoritas tiap-tiap DBA.<\/p>\n

Untuk lebih jelasnya, kita coba praktekkan bagaimana membuat User defined Server Roles di SQL Server 2012.<\/p>\n

Kita akan menyiapkan 2 buah user yang nantinya salahsatu dari user tersebut sebagai Admin Server dengan kemampuan mengontrol server..<\/p>\n

\n
USE<\/span> master\r\nGO<\/span>\r\nCREATE<\/span> LOGIN [TANTI] WITH<\/span> PASSWORD ='MugiB4njarmasin'<\/span>\r\nCREATE<\/span> LOGIN [FIRA] WITH<\/span> PASSWORD ='MugiB4njarmasin'<\/span><\/pre>\n
Setelah 2 user telah tersedia, kemudian kita membuat sebuah Server Role baru dengan Nama DBABjm<\/strong><\/p>\n<\/div>\n
\n
CREATE<\/span> SERVER ROLE<\/span> DBABjm<\/pre>\n
Nah, pada server role yang baru ini, DBABjm, kita akan menjadikannya setara dengan Admin Server yang mampu mengontrol server namun dengan keterbatasan tidak dapat membuat database dan tidak dapat melakukan perubahan terhadap login yang ada. T-SQLnya ada sebagai berikut :<\/p>\n<\/div>\n
\n
GRANT<\/span> CONTROL SERVER TO<\/span> DBABjm -- kemampuan mengontrol server<\/span>\r\nDENY<\/span> CREATE<\/span> ANY<\/span> DATABASE<\/span> TO<\/span> DBABjm -- tdk bisa membuat database<\/span>\r\nDENY<\/span> ALTER<\/span> ANY<\/span> LOGIN TO<\/span> DBABjm -- tdk bisa merubah properti login<\/span><\/pre>\n<\/div>\n
<\/div>\n
Saat ini Login [TANTI] dan [FIRA] hanya memiliki otoritas Server Role Public. Mari kita buat Login [TANTI] memiliki otoritas Server Role DBABjm<\/div>\n
<\/div>\n
\n
ALTER<\/span> SERVER ROLE<\/span> DBABjm ADD<\/span> MEMBER [TANTI]<\/pre>\n
 <\/p>\n<\/div>\n
Nah Saat ini login [TANTI] memiliki kemampuan mengontrol server, namun tidak memiliki kemampuan Membuat Database dan merubah properti sebuah Login. Mari kita buktikan.<\/div>\n
<\/div>\n
Bukti bahwa Login [TANTI] dapat mengontrol Server :<\/div>\n
\"\"<\/a><\/div>\n
<\/div>\n
<\/div>\n
Bukti bahwa Login [TANTI] tidak dapat membuat sebuah database :<\/div>\n
\"\"<\/a><\/div>\n
<\/div>\n
<\/div>\n
Bukti bahwa Login [TANTI] tidak dapat merubah properti sebuah login :<\/div>\n
\"\"<\/a><\/div>\n
Demikian, semoga bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"
Salah satu fitur baru di SQL Server 2012 adalah adanya User Defined Server Roles. dengan adanya fitur ini kita bisa menambahkan Server Roles baru selain yang ada secara default di SQL Server seperti gambar yang ada dibawah ini Dengan adanya fitur ini seorang\u00a0 super admin dapat mendelegasikan tugasnya ke admin \u2026<\/p>\n
 Continue reading<\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":102,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[29],"class_list":["post-83","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-roles"],"yoast_head":"\nHow to : Create User Defined Server Roles in SQL Server 2012 - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to : Create User Defined Server Roles in SQL Server 2012 - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Salah satu fitur baru di SQL Server 2012 adalah adanya User Defined Server Roles. dengan adanya fitur ini kita bisa menambahkan Server Roles baru selain yang ada secara default di SQL Server seperti gambar yang ada dibawah ini Dengan adanya fitur ini seorang\u00a0 super admin dapat mendelegasikan tugasnya ke admin \u2026 Continue reading\" \/>\n<meta property=\"og:url\" content=\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-02T15:05:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-02T15:20:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png\" \/>\n\t<meta property=\"og:image:width\" content=\"526\" \/>\n\t<meta property=\"og:image:height\" content=\"157\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#article\",\"isPartOf\":{\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"How to : Create User Defined Server Roles in SQL Server 2012\",\"datePublished\":\"2014-01-02T15:05:17+00:00\",\"dateModified\":\"2014-01-02T15:20:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html\"},\"wordCount\":244,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png\",\"keywords\":[\"Roles\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html\",\"url\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html\",\"name\":\"How to : Create User Defined Server Roles in SQL Server 2012 - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage\"},\"image\":{\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png\",\"datePublished\":\"2014-01-02T15:05:17+00:00\",\"dateModified\":\"2014-01-02T15:20:56+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png\",\"width\":526,\"height\":157},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to : Create User Defined Server Roles in SQL Server 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to : Create User Defined Server Roles in SQL Server 2012 - Suherman Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html","og_locale":"en_US","og_type":"article","og_title":"How to : Create User Defined Server Roles in SQL Server 2012 - Suherman Blog","og_description":"Salah satu fitur baru di SQL Server 2012 adalah adanya User Defined Server Roles. dengan adanya fitur ini kita bisa menambahkan Server Roles baru selain yang ada secara default di SQL Server seperti gambar yang ada dibawah ini Dengan adanya fitur ini seorang\u00a0 super admin dapat mendelegasikan tugasnya ke admin \u2026 Continue reading","og_url":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-01-02T15:05:17+00:00","article_modified_time":"2014-01-02T15:20:56+00:00","og_image":[{"width":526,"height":157,"url":"https:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png","type":"image\/png"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#article","isPartOf":{"@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"How to : Create User Defined Server Roles in SQL Server 2012","datePublished":"2014-01-02T15:05:17+00:00","dateModified":"2014-01-02T15:20:56+00:00","mainEntityOfPage":{"@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html"},"wordCount":244,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png","keywords":["Roles"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#respond"]}]},{"@type":"WebPage","@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html","url":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html","name":"How to : Create User Defined Server Roles in SQL Server 2012 - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage"},"image":{"@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png","datePublished":"2014-01-02T15:05:17+00:00","dateModified":"2014-01-02T15:20:56+00:00","breadcrumb":{"@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/win.png","width":526,"height":157},{"@type":"BreadcrumbList","@id":"https:\/\/suherman.asia\/w2\/how-to-create-user-defined-server-roles-in-sql-server-2012.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"How to : Create User Defined Server Roles in SQL Server 2012"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/83","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=83"}],"version-history":[{"count":2,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/83\/revisions"}],"predecessor-version":[{"id":103,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/83\/revisions\/103"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/102"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=83"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=83"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}