{"id":3528,"date":"2013-08-01T18:25:06","date_gmt":"2013-08-01T17:25:06","guid":{"rendered":"http:\/\/andreas-wolter.com\/control-server-gegen-sysadmin-sa\/"},"modified":"2019-04-07T14:13:42","modified_gmt":"2019-04-07T13:13:42","slug":"control-server-vs-sysadmin-sa","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/control-server-vs-sysadmin-sa\/","title":{"rendered":"CONTROL SERVER vs. sysadmin\/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation caveats"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-45f2a8032c6975d1e2870a088aa85b0c\">\n#top .av-special-heading.av-av_heading-45f2a8032c6975d1e2870a088aa85b0c{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-45f2a8032c6975d1e2870a088aa85b0c .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-45f2a8032c6975d1e2870a088aa85b0c .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-45f2a8032c6975d1e2870a088aa85b0c av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >CONTROL SERVER vs. sysadmin\/sa:<\/h3><div class='av-subheading av-subheading_below'><p>permissions, system procedures, DBCC, automatic schema creation and privilege escalation caveats<\/p>\n<\/div><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Since SQL Server 2005, the server wide permission <em>CONTROL SERVER<\/em> has been existing. In principle being an alternative to <em>sysadmin<\/em>-membership, it did not turn out to be much more than a shelf warmer. &#8211; Little known and even less used.<\/p>\n<p>One of the main reasons for this was the absence of an option to grant this permission to a group of principals\/Logins on server-level.<!--more--><\/p>\n<p>Since SQL Server 2012, it has been possible to define custom server-roles, so now this permission is used more and more.<\/p>\n<p>However, it is no complete replacement for <em>sysadmin<\/em>. In the following, I want to demonstrate to my readers exactly why this is, where the differences are and where it can even be a risk.<\/p>\n<p>I will take as a given basic knowledge about the behavior of <em>sysadmin<\/em> and <em>dbo<\/em> and therefore focus on the things that are different when using <em>CONTROL SERVER<\/em>.<\/p>\n<p>First of all, a new Login, \u201eDBA_TheDude\u201c, is created \u2013 the password of course adhering strongly to my rules (for blogs). \ud83d\ude09<\/p>\n<p>This is followed by a new server role \u201cRole_DBA\u201d to which the Login will be added as a member.<\/p>\n<p>And since this role is meant for administrators, it receives the most extensive permission: CONTROL SERVER.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-46f7a41dd27b3325fff83833a12bd142\">\n.avia-image-container.av-av_image-46f7a41dd27b3325fff83833a12bd142 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-46f7a41dd27b3325fff83833a12bd142 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-46f7a41dd27b3325fff83833a12bd142 av-styling- avia-align-center  avia-builder-el-2  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3477 avia-img-lazy-loading-not-3477 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1_controlserver_screenshot.png\" alt='' title='1_controlserver_screenshot'  height=\"349\" width=\"507\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1_controlserver_screenshot.png 507w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1_controlserver_screenshot-300x207.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1_controlserver_screenshot-450x310.png 450w\" sizes=\"(max-width: 507px) 100vw, 507px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Next we log onto the system as DBA and have &#8220;our (effective) rights&#8221; displayed. \ud83d\ude42 <a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/2_execute_login_screenshot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3480\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/2_execute_login_screenshot.png\" alt=\"\" width=\"418\" height=\"129\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/2_execute_login_screenshot.png 418w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/2_execute_login_screenshot-300x93.png 300w\" sizes=\"auto, (max-width: 418px) 100vw, 418px\" \/><\/a><\/p>\n<table width=\"368\">\n<tbody>\n<tr>\n<td width=\"101\"><strong>\u00a0<\/strong><strong>entity_name<\/strong><\/td>\n<td width=\"232\"><strong>permission_name<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CONNECT SQL<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">SHUTDOWN<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CREATE ENDPOINT<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CREATE ANY DATABASE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CREATE AVAILABILITY GROUP<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY LOGIN<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY CREDENTIAL<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY ENDPOINT<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY LINKED SERVER<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY CONNECTION<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY DATABASE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER RESOURCES<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER SETTINGS<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER TRACE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY AVAILABILITY GROUP<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ADMINISTER BULK OPERATIONS<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">AUTHENTICATE SERVER<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">EXTERNAL ACCESS ASSEMBLY<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">VIEW ANY DATABASE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">VIEW ANY DEFINITION<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">VIEW SERVER STATE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CREATE DDL EVENT NOTIFICATION<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CREATE TRACE EVENT NOTIFICATION<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY EVENT NOTIFICATION<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER SERVER STATE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">UNSAFE ASSEMBLY<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY SERVER AUDIT<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CREATE SERVER ROLE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY SERVER ROLE<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">ALTER ANY EVENT SESSION<\/td>\n<\/tr>\n<tr>\n<td width=\"101\">server<\/td>\n<td width=\"232\">CONTROL SERVER<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Sounds good. Nothing seems to be missing at first glance. Or does it?<\/p>\n<p>Let\u2019s compare it to the list of all permissions which exist on server-scope:<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/3_select_class.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3483\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/3_select_class.png\" alt=\"\" width=\"494\" height=\"228\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/3_select_class.png 494w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/3_select_class-300x138.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/3_select_class-450x208.png 450w\" sizes=\"auto, (max-width: 494px) 100vw, 494px\" \/><\/a><\/p>\n<p>Ok, we really do have all permissions which can be granted (at server scope). This is also documented: <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms191291.aspx\" target=\"_blank\" rel=\"noopener\">Permissions (Database Engine)<\/a> and here: <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms186717.aspx\" target=\"_blank\" rel=\"noopener\">GRANT Server Permissions (Transact-SQL)<\/a><\/p>\n<p>Those permissions and commands really do work as documented.<\/p>\n<p>But how about <strong>system stored procedures<\/strong>?<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/4_exec_error_screenshot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3485\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/4_exec_error_screenshot.png\" alt=\"\" width=\"640\" height=\"197\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/4_exec_error_screenshot.png 640w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/4_exec_error_screenshot-600x185.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/4_exec_error_screenshot-300x92.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/4_exec_error_screenshot-450x139.png 450w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Why is that now?<\/p>\n<p>Quite simple. Most do probably remember from SQL 2000 times, that security wise critical system procedures include a check for membership in certain roles. And most of them still do the same today. One can call it an artefact of the then inflexible permission-management.<\/p>\n<p>When we look into the code of <em>sp_readerrorlog<\/em>, this is what is being done there as well:<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/5_code_sp_screenshot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3488\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/5_code_sp_screenshot.png\" alt=\"\" width=\"531\" height=\"193\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/5_code_sp_screenshot.png 531w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/5_code_sp_screenshot-300x109.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/5_code_sp_screenshot-450x164.png 450w\" sizes=\"auto, (max-width: 531px) 100vw, 531px\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Okay. Of course we can work around this code by calling <em>xp_readerrorlog<\/em> directly.<\/p>\n<p>This does not help in the GUI-presentation in Management Studio but we can use it as a manual workaround.<\/p>\n<p>Among other system procedures that are called via SSMS and refuse execution are: <em>sp_addumpdevice<\/em> &#8211; for backup-devices <em>sp_enum_oledb_providers<\/em> \u2013 is used for viewing and configuring Linked Servers.<\/p>\n<p><em>sp_addlinkedserver<\/em> on the other hand can be executed without problems \u2013 just not via the GUI because of the just mentioned procedure.<\/p>\n<p>Setting up <strong>Database-Mail<\/strong> via the GUI is also reserved for sysadmins. It should work via script though (untested).<\/p>\n<p>Also the configuration of the distributor for <strong>replication<\/strong> by means of <em>sp_adddistributor<\/em> is not possible without the <em>sysadmin<\/em> bit. Other replication tasks are partially possible even via the GUI.<\/p>\n<p>Under SQL Server 2005 \u2013 2008R2, <strong>to add\/remove (server) role members<\/strong>, solely the system procedure <em>sp_addsrvrolemember \/ sp_dropsrvrolemember<\/em> exists. This one also conducts a server-role-membership-check. &#8211; Under SQL Server 2012 this procedure still exists for backwards-compatibility reasons, but this specific check has been removed from the code. Thereby it now behaves like the direct call of <em>ALTER SERVER ROLE {RoleName} ADD MEMBER {Loginname}<\/em>.<\/p>\n<p>Same applies to <em>sp_addrolemember \/ sp_droprolemember<\/em> for database roles.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>So much for system stored procedures. Quite simple after all, if you know the background.<\/p>\n<p>Therefore, wherever possible: use DDL-commands, as it is advised since SQL Server 2005.<\/p>\n<p>Side-note: there are at least 7 variations for the check of sysadmin-membership. \ud83d\ude42 \ud83d\ude41<\/p>\n<p>A teaser:<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/6_sample_screenshot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3491\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/6_sample_screenshot.png\" alt=\"\" width=\"512\" height=\"157\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/6_sample_screenshot.png 512w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/6_sample_screenshot-300x92.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/6_sample_screenshot-450x138.png 450w\" sizes=\"auto, (max-width: 512px) 100vw, 512px\" \/><\/a><\/p>\n<p>The most frequent error messages which are triggered if applicable are:<\/p>\n<table width=\"620\">\n<tbody>\n<tr>\n<td width=\"91\"><strong>message_id<\/strong><\/td>\n<td width=\"580\"><strong>text<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"91\"><strong>14126<\/strong><\/td>\n<td width=\"580\">You do not have the required permissions to complete the operation.<\/td>\n<\/tr>\n<tr>\n<td width=\"91\"><strong>14260<\/strong><\/td>\n<td width=\"580\">You do not have sufficient permission to run this command. Contact your system administrator.<\/td>\n<\/tr>\n<tr>\n<td width=\"91\"><strong>15003<\/strong><\/td>\n<td width=\"580\">Only members of the %s role can execute this stored procedure.<\/td>\n<\/tr>\n<tr>\n<td width=\"91\"><strong>15247<\/strong><\/td>\n<td width=\"580\">User does not have permission to perform this action.<\/td>\n<\/tr>\n<tr>\n<td width=\"91\"><strong>21089<\/strong><\/td>\n<td width=\"580\">Only members of the sysadmin fixed server role can perform this operation.<\/td>\n<\/tr>\n<tr>\n<td width=\"91\"><strong>22904<\/strong><\/td>\n<td width=\"580\">Caller is not authorized to initiate the requested action. DBO privileges are required.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>A complete list of procedures that check for <em>sysadmin<\/em>-membership is included at the end of this post.<\/p>\n<p>Let\u2019s come to another important area:<\/p>\n<p><strong>DBCC commands<\/strong><\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/7_commando_screenshot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3492\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/7_commando_screenshot.png\" alt=\"\" width=\"633\" height=\"186\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/7_commando_screenshot.png 633w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/7_commando_screenshot-600x176.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/7_commando_screenshot-300x88.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/7_commando_screenshot-450x132.png 450w\" sizes=\"auto, (max-width: 633px) 100vw, 633px\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>That\u2019s a good start, isn\u2019t it..<\/p>\n<p>The \u201erule\u201c for DBCC is even simpler: Almost every DBCC-command checks for sysadmin.<\/p>\n<p>From <em>DBCC CHECKDB <\/em>via <em>DBCC LOGINFO<\/em> to <em>DBCC TRACEON<\/em>. Thus some quite valuable commands also for external support. &#8211; In fact even <em>DBCC HELP<\/em> is only allowed for <em>sysadmins<\/em>&#8230;<\/p>\n<p>The only exceptions known to me are: DBCC SHOW_STATISTICS<\/p>\n<ul>\n<li>one needs to be at least <em>ddl_admin<\/em> or owner of the table<\/li>\n<\/ul>\n<p>DBCC DETACHDB<\/p>\n<ul>\n<li>this is a special case, since it&#8217;s executed via <em>sp_detach_db<\/em><\/li>\n<li>db_owner or dbo of the database is sufficient<\/li>\n<\/ul>\n<p>DBCC FREEPROCCACHE and DBCC SQLPERF<\/p>\n<ul>\n<li>Those require only the <em>ALTER SERVER STATE<\/em> permission.<\/li>\n<\/ul>\n<p>DBCC DROPCLEANBUFFERS on the other hand requires sysadmin-role membership.<\/p>\n<p>Those permissions are documented quite well, by the way.<\/p>\n<p><strong>Database permissions<\/strong><\/p>\n<p>With <em>CONTROL SERVER<\/em>, a principal has full access to all databases. Almost, but only almost like sysadmins, which are, as is well-known, mapped to \u201c<em>dbo<\/em>\u201d.<\/p>\n<p>Logins with merely CONTROL SERVER permission are not(!) mapped to dbo.<\/p>\n<p>The consequences of this can be seen in the following:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/8_control_screenshot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3495\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/8_control_screenshot.png\" alt=\"\" width=\"541\" height=\"387\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/8_control_screenshot.png 541w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/8_control_screenshot-300x215.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/8_control_screenshot-260x185.png 260w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/8_control_screenshot-450x322.png 450w\" sizes=\"auto, (max-width: 541px) 100vw, 541px\" \/><\/a><\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3501\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema.png\" alt=\"\" width=\"378\" height=\"70\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema.png 378w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema-300x56.png 300w\" sizes=\"auto, (max-width: 378px) 100vw, 378px\" \/><\/a><\/p>\n<p style=\"padding-left: 30px;\">&#8211;><\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Schema.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3497\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Schema.png\" alt=\"\" width=\"374\" height=\"145\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Schema.png 374w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Schema-300x116.png 300w\" sizes=\"auto, (max-width: 374px) 100vw, 374px\" \/><\/a><\/p>\n<p>SSMS:<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema_SSMS.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3503\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema_SSMS.png\" alt=\"\" width=\"346\" height=\"103\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema_SSMS.png 346w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Table_Schema_SSMS-300x89.png 300w\" sizes=\"auto, (max-width: 346px) 100vw, 346px\" \/><\/a><\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Schema_SSMS.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3499\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_Schema_SSMS.png\" alt=\"\" width=\"212\" height=\"121\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>What happened?<\/p>\n<p>Do you remember the pre-SQL 2012 problem in which windows groups could not be assigned a default schema? And what happened if a developer forgot to specify a schema when creating an object?<\/p>\n<p>Right. This is exactly the same issue: a schema with the Login-Name of the developer has been created. A true joy for later clean-up, to identify and drop the \u201eright wrong\u201d schemas. This Issue (one of the most voted <a href=\"http:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/328585\/default-schema-for-windows-group\" target=\"_blank\" rel=\"noopener\">Connect-Items<\/a>) has been resolved with SQL Server 2012.<\/p>\n<p>Unfortunately, the handling of the CONTROL SERVER permission has been overlooked to be fixed. I did create a connect item: \u201e<a href=\"http:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/768009\/login-with-control-server-permission-creating-an-object-without-specifying-schema-leads-to-creation-of-new-schema-with-login-name\" target=\"_blank\" rel=\"noopener\">Login with CONTROL SERVER Permission Creating an Object without specifying Schema leads to creation of new Schema with Login-Name<\/a>\u201d. \u2013 Unfortunately too late. :-\/<\/p>\n<p>Probably an identical code-block is being used for that, since just like in the Windows-Group scenario, this implicit creation of the schema is uncatchable.<\/p>\n<p>Conclusion: Always specify a schema when creating an object as a CONTROL SERVER granted principal \u2013 best: <u>always<\/u> specify a schema. (there are other reasons for that, too)<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Privilege-Escalation<\/strong>-risk:<\/p>\n<p>I want to finish off with a security-wise extremely important hint:<\/p>\n<p><strong>Logins with CONTROL SERVER permission can impersonate EVERY Login.<\/strong> ALL OF THEM. Thus also all sysadmins and sa itself! \u2013 And even if SQL authentication is not active!<\/p>\n<p>And this is how this can be marvellously exploited:<\/p>\n<p><a href=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/9_alter_role_screenshot.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3506\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/9_alter_role_screenshot.png\" alt=\"\" width=\"548\" height=\"375\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/9_alter_role_screenshot.png 548w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/9_alter_role_screenshot-300x205.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/9_alter_role_screenshot-450x308.png 450w\" sizes=\"auto, (max-width: 548px) 100vw, 548px\" \/><\/a><\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-dcdc3e699a488055d2269f00a164b191\">\n.avia-image-container.av-av_image-dcdc3e699a488055d2269f00a164b191 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-dcdc3e699a488055d2269f00a164b191 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-dcdc3e699a488055d2269f00a164b191 av-styling- avia-align-center  avia-builder-el-13  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3509 avia-img-lazy-loading-not-3509 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_sysadmin_escalation.png\" alt='' title='1307_Control_Server_sysadmin_escalation'  height=\"297\" width=\"305\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_sysadmin_escalation.png 305w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_sysadmin_escalation-300x292.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1307_Control_Server_sysadmin_escalation-36x36.png 36w\" sizes=\"(max-width: 305px) 100vw, 305px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Those who wants to protect themselves from this form of <em>privilege escalation<\/em> must DENY impersonating. &#8211; And why would you not want to do that since<strong> this eventually would put the whole concept of CONTROL SERVER in question <\/strong>(?!) &#8211; Because this is the final and crucial difference:<\/p>\n<p><strong>Sysadmin-members cannot be restricted anything via <em>DENY<\/em>. Yet principals with mere CONTROL SERVER permissions can.<\/strong><\/p>\n<p>This is how all plain DDL\/DML commands work.<\/p>\n<p>So, to say it clearly again, because this is vital:<\/p>\n<p><span style=\"color: #ff0000;\">A Login with CONTROL SERVER Permission only makes sense when at the same time he is explicitly denied impersonation of any sysadmin (or better: any Logins). Otherwise you may as well just make him sysadmin right away!<\/span><\/p>\n<p>Unfortunately this only works via &#8220;DENY IMPERSONATE ON LOGIN::[Loginname]&#8221;. This is because <em>IMPERSONATE<\/em> is hierarchically a direct subordinate of CONTROL SERVER!<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-19229cb05b74bccb6cd5ee17ff7e1388\">\n.avia-image-container.av-av_image-19229cb05b74bccb6cd5ee17ff7e1388 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-19229cb05b74bccb6cd5ee17ff7e1388 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-19229cb05b74bccb6cd5ee17ff7e1388 av-styling- avia-align-center  avia-builder-el-16  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-3511 avia-img-lazy-loading-not-3511 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/10_use_master_screenshot.png\" alt='' title='10_use_master_screenshot'  height=\"100\" width=\"482\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/10_use_master_screenshot.png 482w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/10_use_master_screenshot-300x62.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/10_use_master_screenshot-450x93.png 450w\" sizes=\"(max-width: 482px) 100vw, 482px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>For a changing list of system administrators, one has a real problem, in case of not remembering this crucial Deny! (<strong>Next risk!<\/strong>)<\/p>\n<p>&#8211; At the latest at this point one should address the <em>Auditing<\/em> feature of SQL Server.<\/p>\n<p>My <strong>conclusion and lessons learned<\/strong> therefore are:<\/p>\n<p>CONTROL SERVER itself is a consistent continuation of the efforts of the security team to make SQL Server even simpler and thereby more robust.<\/p>\n<p>Because of the impersonate-permissions, a privilege escalation is really simple. In order to prevent this, one should be familiar with the permission hierarchy of SQL Server. \u2013 As always: \u201cKnowledge leads to security.\u201d<\/p>\n<p><strong>What really does hurt<\/strong>, is one of the best application cases: <strong>Permissions for support-personnel<\/strong>, internal or external. The missing possibility to grant permissions to DBCC directly throws a monkey wrench in our plans. If one does not want to create a procedure as wrapper for every important DBCC command, <strong>there is no way around sysadmin in those scenarios. (!)<\/strong><\/p>\n<p>In the end, a strict \u201cseparation of duties\u201d is the iron goal.<\/p>\n<p>Here you can read more about it \u2013 you should also check out the \u201cSQL Server Separation of Duties Framework\u201d on codeplex with many good ideas:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><a href=\"https:\/\/techcommunity.microsoft.com\/t5\/SQL-Server\/Separation-of-Duties-for-DBA-s\/ba-p\/383915\" target=\"_blank\" rel=\"noopener\">https:\/\/techcommunity.microsoft.com\/t5\/SQL-Server\/Separation-of-Duties-for-DBA-s\/ba-p\/383915<\/a><\/p>\n<p>Update 04-2014:<\/p>\n<p>With SQL Server 2014 CONTROL Server becomes a bit safer. You can read more on the new possibilities here:<\/p>\n<p><a href=\"https:\/\/andreas-wolter.com\/en\/new-permissions-in-sql-server-2014-and-the-old-control-server\/\">New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER<\/a><\/p>\n<p>A displacement of \u201esa\u201c is not planned by the way, but with every release the scenarios where sa\/sysadmin is imperatively needed become less.<\/p>\n<p>Happy securing,<\/p>\n<p><br class=\"\u201cclear\u201c\" \/>Andreas<br \/>\n<br class=\"\u201cclear\u201c\" \/><\/p>\n<p>Enclosed the list of all system procedures under SQL Server 2012 that check for sysadmin-role membership (171 \u2013 vs. 171 under SQL server 2008. SQL Server 2014 CTP 1 still the same):<\/p>\n<p>(Hint: Altogether 197 system procedures check for server role membership like <em>diskadmin<\/em> or <em>serveradmin<\/em> and so on)<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><table width=\"316\">\n<tbody>\n<tr>\n<td width=\"286\"><strong>Module_Name<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"286\">fn_yukonsecuritymodelrequired<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_add_agent_parameter<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_add_agent_profile<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_adddatatype<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_adddistributiondb<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_adddistributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_addqreader_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_addsubscriber<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_addsubscriber_schedule<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_addtabletocontents<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_attachsubscription<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_cleanup_change_table<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_disable_db<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_disable_table<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_drop_job<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_enable_db<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_enable_table<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_restoredb<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cdc_vupgrade<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_certify_removable<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_change_agent_parameter<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_change_agent_profile<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_change_subscription_properties<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_change_users_login<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changedistpublisher<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changedistributiondb<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changedistributor_password<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changedistributor_property<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changemergesubscription<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changeqreader_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changereplicationserverpasswords<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_changesubscriptiondtsinfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_checkinvalidivarticle<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_copysubscription<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_create_removable<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_cycle_errorlog<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbcmptlevel<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitoraddmonitoring<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitorchangealert<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitordropalert<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitordropmonitoring<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitorhelpalert<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitorhelpmonitoring<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitorresults<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbmmonitorupdate<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dbremove<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_drop_agent_parameter<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_drop_agent_profile<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dropdatatypemapping<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dropdistpublisher<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dropdistributiondb<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dropdistributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dropmergepullsubscription<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_droppullsubscription<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dropsubscriber<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_dsninfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_enumdsn<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_flush_commit_table_on_demand<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_generate_agent_parameter<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_get_distributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_get_Oracle_publisher_metadata<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_getagentparameterlist<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_getdefaultdatatypemapping<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_grant_publication_access<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_help_agent_default<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_help_agent_parameter<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_help_agent_profile<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_helpdistpublisher<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_helpdistributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_helpmergesubscription<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_helpqreader_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_helpreplicationdboption<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_identitycolumnforreplication<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_IHValidateRowFilter<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_IHXactSetJob<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_link_publication<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_monitor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSadd_distribution_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSadd_logreader_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSadd_merge_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSadd_snapshot_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSadd_subscriber_schedule<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSadd_tracer_history<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSadd_tracer_token<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MScdc_cleanup_job<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MScdc_db_ddl_event<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MScdc_ddl_event<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSchange_distribution_agent_properties<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSchange_logreader_agent_properties<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSchange_merge_agent_properties<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSchange_snapshot_agent_properties<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSchangedynamicsnapshotjobatdistributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSchangedynsnaplocationatdistributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MScheck_pull_access<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MScleanupmergepublisher_internal<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSclear_dynamic_snapshot_location<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MScreate_dist_tables<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSdbuserpriv<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSdeletefoldercontents<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSdrop_6x_replication_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSdrop_merge_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSdrop_snapshot_dirs<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSdropmergedynamicsnapshotjob<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSdynamicsnapshotjobexistsatdistributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSenumallpublications<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSfetchAdjustidentityrange<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSfix_6x_tasks<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSforce_drop_distribution_jobs<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSget_agent_names<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSget_jobstate<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSget_oledbinfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSget_publication_from_taskname<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSgetdbversion<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSgetmaxsnapshottimestamp<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelp_repl_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelp_replication_status<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelp_snapshot_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelpconflictpublications<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelpdynamicsnapshotjobatdistributor<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelplogreader_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelpsnapshot_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MShelptranconflictcounts<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSinit_publication_access<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreinit_failed_subscriptions<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSremoveoffloadparameter<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_backup_complete<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_backup_start<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_createdatatypemappings<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_dropdatatypemappings<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_enumarticlecolumninfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_enumpublications<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_enumpublishertables<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_enumsubscriptions<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_enumtablecolumninfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_getdistributorinfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSrepl_startup_internal<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreplagentjobexists<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreplcheck_permission<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreplcheck_pull<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreplcheck_subscribe<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreplcheck_subscribe_withddladmin<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreplcopyscriptfile<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSreplremoveuncdir<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSsetalertinfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSSetServerProperties<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSsetupnosyncsubwithlsnatdist<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSsetupnosyncsubwithlsnatdist_cleanup<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSsetupnosyncsubwithlsnatdist_helper<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSstartdistribution_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSstartmerge_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSstartsnapshot_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSstopdistribution_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSstopmerge_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSstopsnapshot_agent<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_MSupdate_agenttype_default<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_oledbinfo<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_procoption<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_removedbreplication<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_removesrvreplication<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_replication_agent_checkup<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_replicationdboption<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_resetstatus<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_restoredbreplication<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_SetAutoSAPasswordAndDisable<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_setdefaultdatatypemapping<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_updatestats<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_validatelogins<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_vupgrade_mergeobjects<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_vupgrade_replication<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">sp_vupgrade_replsecurity_metadata<\/td>\n<\/tr>\n<tr>\n<td width=\"286\">xp_repl_convert_encrypt_sysadmin_wrapper<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-20  el_after_av_textblock  el_before_av_hr  first flex_column_div av-zero-column-padding  column-top-margin'     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-22  el_after_av_one_full  el_before_av_social_share '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-00566587b7355f5f3aec989679437938 av-social-sharing-box-default  avia-builder-el-23  el_after_av_hr  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Eintrag teilen<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/control-server-vs-sysadmin-sa\/&#038;t=CONTROL%20SERVER%20vs.%20sysadmin%2Fsa%3A%20permissions%2C%20system%20procedures%2C%20DBCC%2C%20automatic%20schema%20creation%20and%20privilege%20escalation%20caveats\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=CONTROL%20SERVER%20vs.%20sysadmin%2Fsa%3A%20permissions%2C%20system%20procedures%2C%20DBCC%2C%20automatic%20schema%20creation%20and%20privilege%20escalation%20caveats&#038;url=https:\/\/andreas-wolter.com\/en\/?p=3528\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=CONTROL%20SERVER%20vs.%20sysadmin%2Fsa%3A%20permissions%2C%20system%20procedures%2C%20DBCC%2C%20automatic%20schema%20creation%20and%20privilege%20escalation%20caveats&#038;url=https:\/\/andreas-wolter.com\/en\/control-server-vs-sysadmin-sa\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-24  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"Since SQL Server 2005, the server wide permission CONTROL SERVER has been existing. In principle being an alternative to sysadmin-membership, it did not turn out to be much more than a shelf warmer. &#8211; Little known and even less used. One of the main reasons for this was the absence of an option to grant [&hellip;]","protected":false},"author":4,"featured_media":3691,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[327,57],"tags":[253,257,258],"class_list":["post-3528","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-schemas-en","category-security-en","tag-control-server-en","tag-schema-en","tag-sysadmin-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3528","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=3528"}],"version-history":[{"count":6,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3528\/revisions"}],"predecessor-version":[{"id":6053,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/3528\/revisions\/6053"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/3691"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=3528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=3528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=3528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}