{"id":1759,"date":"2014-01-04T11:00:24","date_gmt":"2014-01-04T16:00:24","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1759"},"modified":"2014-11-13T13:22:18","modified_gmt":"2014-11-13T18:22:18","slug":"using-server-roles-server-level-security-management","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/","title":{"rendered":"Using Server Roles for Server Level Security Management"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nWhen managing permissions for multiple logins, server roles can simplify your life significantly. In most places where permissions need to be handled, some kind of grouping does exist, for example Windows Groups. Their purpose is to allow you to define a permission set for example based on a job requirement. After that set is defined once, you can grant that entire set to one person just by adding their login to that group. When that person does not have this specific job requirement anymore, you can just remove their login from the group.\n<\/p>\n

\nThis simplifies the administrators live significantly, particularly if a person is in multiple groups and is leaving only a few. If groups wouldn't exist, you would have to go through the list of all permissions and determine if each one is still necessary for any of that person's job requirements. With groups you just remove their login from the groups in question and the system handles the rest.\n<\/p>\n

The New and Flexible User-Defined Server Role<\/h3>\n

\nServer roles in SQL Server have the same purpose. However, up to SQL Server 2008R2 their use was somewhat limited. SQL Server came with nine different server roles out of the box, all of which were immutable. That meant, we could not remove or add specific permissions to one of those roles. Creating new roles was also not possible.\n<\/p>\n

\nSQL Server 2012 added fully functional user-defined server roles. You can create a new server role by simply executing this statement:\n<\/p>\n

\n[sql]\nCREATE SERVER ROLE role_name [ AUTHORIZATION server_principal ];
\n[\/sql]\n<\/div>\n

\nThe role_name must be unique within the server. The authorization clause is optional and allows you to specify the owner of the new role. Once you have the role created you can add members with the ALTER SERVER ROLE<\/span> Statement. This statement works the same for fixed and user-defined server roles. You can add any login (with the exception of 'SA') and any user-defined server role to any other server role.\n<\/p>\n

Permissions<\/h3>\n

\nThe permissions that can be granted to a server role are the same that can be granted to a login; they are all server level permissions. For permissions on objects that reside in a user database, a user or user role is required. SQL Server currently offers 30 different server level permissions. They include things like IMPERSONATE<\/span> a login, CONTROL<\/span> an endpoint or VIEW SERVER STATE<\/span>. The complete list can be found here<\/a>.\n<\/p>\n

Summary<\/h3>\n

\nServer roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles that could not be changed. Starting with SQL Server 2012 we are now able to create our own server roles and grant (or deny) any combination of the 30 server level permissions. Any login and any other user-defined server role can be a member of such a new server role.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles. However, now we are able to create user-defined server roles and grant any server level permission to them. Read on to find out more…<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,34],"tags":[18,50,38,15],"class_list":["post-1759","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-best-practice","tag-permission","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nUsing Server Roles for Server Level Security Management - sqlity.net<\/title>\n<meta name=\"description\" content=\"Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles. However, now we are able to create user-defined server roles and grant any server level permission to them.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using Server Roles for Server Level Security Management - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles. However, now we are able to create user-defined server roles and grant any server level permission to them.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-04T16:00:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:22:18+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Using Server Roles for Server Level Security Management\",\"datePublished\":\"2014-01-04T16:00:24+00:00\",\"dateModified\":\"2014-11-13T18:22:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/\"},\"wordCount\":486,\"commentCount\":0,\"keywords\":[\"Best Practice\",\"Permission\",\"security\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/\",\"name\":\"Using Server Roles for Server Level Security Management - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-04T16:00:24+00:00\",\"dateModified\":\"2014-11-13T18:22:18+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles. However, now we are able to create user-defined server roles and grant any server level permission to them.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1759\\\/using-server-roles-server-level-security-management\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using Server Roles for Server Level Security Management\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using Server Roles for Server Level Security Management - sqlity.net","description":"Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles. However, now we are able to create user-defined server roles and grant any server level permission to them.","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:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/","og_locale":"en_US","og_type":"article","og_title":"Using Server Roles for Server Level Security Management - sqlity.net","og_description":"Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles. However, now we are able to create user-defined server roles and grant any server level permission to them.","og_url":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-04T16:00:24+00:00","article_modified_time":"2014-11-13T18:22:18+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Using Server Roles for Server Level Security Management","datePublished":"2014-01-04T16:00:24+00:00","dateModified":"2014-11-13T18:22:18+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/"},"wordCount":486,"commentCount":0,"keywords":["Best Practice","Permission","security","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/","url":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/","name":"Using Server Roles for Server Level Security Management - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-04T16:00:24+00:00","dateModified":"2014-11-13T18:22:18+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Server roles simplify management of server level permissions. Before SQL Server 2012 we were confined to 9 fixed server roles. However, now we are able to create user-defined server roles and grant any server level permission to them.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1759\/using-server-roles-server-level-security-management\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Using Server Roles for Server Level Security Management"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-sn","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1759","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1759"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1759\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1759"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1759"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1759"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}