{"id":5709,"date":"2018-10-24T21:57:25","date_gmt":"2018-10-24T20:57:25","guid":{"rendered":"http:\/\/andreas-wolter.com\/blocking-when-missing-permissions-the-strange-case-of-the-blocked-index-rebuild\/"},"modified":"2018-10-24T22:00:37","modified_gmt":"2018-10-24T21:00:37","slug":"1809_blocking_with_missing_permissions","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/1809_blocking_with_missing_permissions\/","title":{"rendered":"Blocking when missing Permissions &#8211; The strange case of the blocked index rebuild"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-eb89dc1ab32aaf420fe8df6c70ec037d\">\n#top .av-special-heading.av-av_heading-eb89dc1ab32aaf420fe8df6c70ec037d{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-eb89dc1ab32aaf420fe8df6c70ec037d .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-eb89dc1ab32aaf420fe8df6c70ec037d .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-eb89dc1ab32aaf420fe8df6c70ec037d 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\"  >Blocking when missing Permissions <\/h3><div class='av-subheading av-subheading_below'><p>The strange case of the blocked index rebuild<\/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>Recently, a client drew my attention to a blocking problem he could not make any sense of.<\/p>\n<p>After a brief analysis, the <strong>scenario<\/strong> can be described quite simply:<\/p>\n<p>Session 1) (SPID 69 in my example)<\/p>\n<p>A user carries out an <strong>update statement<\/strong> on a table within an explicit transaction, but <strong>which fails due to the lack of required permissions<\/strong>. The transaction, however, remains open. (The is of course the actual problem, as one will conclude at the end. A basic error processing is essential.)<\/p>\n<p>Session 2) (SPID 67)<\/p>\n<p>In the meantime, an index rebuild is running via a maintenance job. And this one <strong>is stuck due to a block<\/strong> which was caused by the previously described session.<\/p>\n<p><strong>Part 1: blocked index rebuild<\/strong><\/p>\n<p>What kind of a blocking can be maintained by a non-executable update statement? \u2013 That is a reasonable question.<\/p>\n<p>You can see the set locks through session 1, identified via the DMV <em><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-tran-locks-transact-sql?view=sql-server-2017\" target=\"_blank\" rel=\"noopener\">sys.dm_tran_locks<\/a><\/em><em>,<\/em> here:<\/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-4503d5f9917a1a23e56f8c300d44a751\">\n.avia-image-container.av-av_image-4503d5f9917a1a23e56f8c300d44a751 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-4503d5f9917a1a23e56f8c300d44a751 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-4503d5f9917a1a23e56f8c300d44a751 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-5619 avia-img-lazy-loading-not-5619 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions.jpg\" alt='' title='1809_Locks_Update_without_Permissions'  height=\"199\" width=\"2250\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions.jpg 2250w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions-600x53.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions-300x27.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions-768x68.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions-1030x91.jpg 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions-1500x133.jpg 1500w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions-705x62.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Update_without_Permissions-450x40.jpg 450w\" sizes=\"(max-width: 2250px) 100vw, 2250px\" \/><\/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>First, one will probably notice that the table has received an IX. \u2013 Even though further below in the hierarchy no further lock was set, nor could be set! There are no further locks at page or key level as would be the case otherwise in a successful update.<\/p>\n<p>Parallel to this, metadata locks stand out: these are at the schema of the table (5) and the database principal dbo. We will get to this in the <strong>2<sup>nd<\/sup> part<\/strong> further below.<\/p>\n<p><strong>Which of these is causing the blocking?<\/strong><\/p>\n<p>Those who have dealt with index rebuilds know that these set an Sch-M Lock (schema modification lock). And this particular lock type <strong>isn\u2019t compatible with any other lock mode! <\/strong>(<a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms172925(v=sql.110).aspx\" target=\"_blank\" rel=\"noopener\">https:\/\/technet.microsoft.com\/en-us\/library\/ms172925(v=sql.110).aspx<\/a> )<\/p>\n<p>Accordingly, we see the following locks and wait states:<\/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-8f25ea7ed8fc40c80ec2036156de606a\">\n.avia-image-container.av-av_image-8f25ea7ed8fc40c80ec2036156de606a img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-8f25ea7ed8fc40c80ec2036156de606a .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-8f25ea7ed8fc40c80ec2036156de606a av-styling- avia-align-center  avia-builder-el-4  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-5620 avia-img-lazy-loading-not-5620 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild.jpg\" alt='Blocked_Index_Rebuild' title='1809_Blocked_Index_Rebuild'  height=\"77\" width=\"800\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild.jpg 800w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild-600x58.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild-300x29.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild-768x74.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild-705x68.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild-450x43.jpg 450w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/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>(Session 69 was tried by the update, session 67 tries the index rebuild)<\/p>\n<p>For those indulging in a professional monitoring tool, it is a little bit easier to interpret via <em>SQLSentry <\/em>Blocking monitor:<\/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-d81da54fa431eba09be185bb50265f82\">\n.avia-image-container.av-av_image-d81da54fa431eba09be185bb50265f82 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-d81da54fa431eba09be185bb50265f82 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-d81da54fa431eba09be185bb50265f82 av-styling- avia-align-center  avia-builder-el-6  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-5718 avia-img-lazy-loading-not-5718 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild_SQLSentry.jpg\" alt='' title='1809_Blocked_Index_Rebuild_SQLSentry'  height=\"44\" width=\"1000\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild_SQLSentry.jpg 1000w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild_SQLSentry-600x26.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild_SQLSentry-300x13.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild_SQLSentry-768x34.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild_SQLSentry-705x31.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocked_Index_Rebuild_SQLSentry-450x20.jpg 450w\" sizes=\"(max-width: 1000px) 100vw, 1000px\" \/><\/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>As you will notice, the Sch-M Lock actually waits for the release and is blocked until the end of the lock of the transaction with the failed update keeping the IX-lock at table level.<\/p>\n<p>By the way, this is also the decisive difference to Index Reorganize. (hint)<\/p>\n<p><strong>Part 2: The key question: <\/strong>Could one simply grant permissions via a <strong>GRANT UPDATE<\/strong> directly for the table, then have the 1<sup>st<\/sup> transaction repeat the update command, and expect that in so doing their locks disappear and continue the index rebuild as a result?<\/p>\n<p>The command for this would be:<\/p>\n<p>GRANT UPDATE<\/p>\n<p>ON SQL_Demos.Transactions<\/p>\n<p>TO SQLUserX<\/p>\n<p>\u00a0<\/p>\n<p>According to the documentation, permissions will immediately become \u201cactive\u201d and do not require a login again.<\/p>\n<p><strong>The answer<\/strong> is nonetheless: <strong>No<\/strong>, in this particular case it does not work like this.<\/p>\n<p><strong>Why?<\/strong><\/p>\n<p>The problem is the Sch-M-Lock in the table (\u201eOBJECT\u201c).<\/p>\n<p><strong>A GRANT UPDATE at object level requires a Sch-S-Lock in the table.<\/strong><\/p>\n<p>The following blocking chain is created (the crucial lines are highlighted in yellow):<\/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-a8be4de365bbf5d6ddb83a712afebfcc\">\n.avia-image-container.av-av_image-a8be4de365bbf5d6ddb83a712afebfcc img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-a8be4de365bbf5d6ddb83a712afebfcc .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-a8be4de365bbf5d6ddb83a712afebfcc av-styling- avia-align-center  avia-builder-el-8  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-5716 avia-img-lazy-loading-not-5716 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission.jpg\" alt='' title='1809_Blocking_Index_Rebuild_Grant_Object_Permission'  height=\"143\" width=\"1000\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission.jpg 1000w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission-600x86.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission-300x43.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission-768x110.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission-705x101.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission-450x64.jpg 450w\" sizes=\"(max-width: 1000px) 100vw, 1000px\" \/><\/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>(#1 is created by the update attempt, #2 by the index rebuild that now has to wait, and #3 by the GRANT waiting for it)<\/p>\n<p>Here, it is worth relaunching the <em>SQLSentry<\/em> Blockade monitor again:<\/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-8e7ca38953ed2997691b21ebf2ab3c27\">\n.avia-image-container.av-av_image-8e7ca38953ed2997691b21ebf2ab3c27 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-8e7ca38953ed2997691b21ebf2ab3c27 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-8e7ca38953ed2997691b21ebf2ab3c27 av-styling- avia-align-center  avia-builder-el-10  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-5714 avia-img-lazy-loading-not-5714 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry.jpg\" alt='' title='1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry'  height=\"52\" width=\"1000\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry.jpg 1000w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry-600x31.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry-300x16.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry-768x40.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry-705x37.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Blocking_Index_Rebuild_Grant_Object_Permission_SQLSentry-450x23.jpg 450w\" sizes=\"(max-width: 1000px) 100vw, 1000px\" \/><\/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>But those who got used to the <strong>Best Practice of granting permissions only at scheme level<\/strong> (also see: <a href=\"https:\/\/andreas-wolter.com\/en\/schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind\/\">Schema-design for SQL Server: recommendations for Schema design with security in mind<\/a>) also avoid these locks at object level!:<\/p>\n<p>GRANT UPDATE<\/p>\n<p>ON SCHEMA::SQL_Demos<\/p>\n<p>TO SQLUserX<\/p>\n<p>The locks for this hence are at scheme and database-principal-level, and thus not in the way.<\/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-0f22cfc9d7eeddd5b619fdb6f4dc5d9c\">\n.avia-image-container.av-av_image-0f22cfc9d7eeddd5b619fdb6f4dc5d9c img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-0f22cfc9d7eeddd5b619fdb6f4dc5d9c .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-0f22cfc9d7eeddd5b619fdb6f4dc5d9c av-styling- avia-align-center  avia-builder-el-12  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-5712 avia-img-lazy-loading-not-5712 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Grant_User_on_Schema.jpg\" alt='' title='1809_Locks_Grant_User_on_Schema'  height=\"179\" width=\"1000\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Grant_User_on_Schema.jpg 1000w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Grant_User_on_Schema-600x107.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Grant_User_on_Schema-300x54.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Grant_User_on_Schema-768x137.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Grant_User_on_Schema-705x126.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Grant_User_on_Schema-450x81.jpg 450w\" sizes=\"(max-width: 1000px) 100vw, 1000px\" \/><\/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>The principal for testing has the principal-ID 5, and also, my test scheme happens to have the ID 5.<\/p>\n<p>For insiders: Object-ID 29 belongs to the internal system table <em>sysprivs<\/em> that contains the database permissions.<\/p>\n<p><strong>It would be even better to work exclusively with roles and schemas.<\/strong><\/p>\n<p>Adding the user to a role retroactively looks like this at lock-level:<\/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-68d0475904c8bb17252c649dcd7553c8\">\n.avia-image-container.av-av_image-68d0475904c8bb17252c649dcd7553c8 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-68d0475904c8bb17252c649dcd7553c8 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-68d0475904c8bb17252c649dcd7553c8 av-styling- avia-align-center  avia-builder-el-14  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-5710 avia-img-lazy-loading-not-5710 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Add_Role_Membership.jpg\" alt='' title='1809_Locks_Add_Role_Membership'  height=\"120\" width=\"1000\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Add_Role_Membership.jpg 1000w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Add_Role_Membership-600x72.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Add_Role_Membership-300x36.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Add_Role_Membership-768x92.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Add_Role_Membership-705x85.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2018\/09\/1809_Locks_Add_Role_Membership-450x54.jpg 450w\" sizes=\"(max-width: 1000px) 100vw, 1000px\" \/><\/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>In other words: Also no problem for continuing to work in the table.<\/p>\n<p>As one can recognize by the high number, the Principal-ID 16391 belongs to the built-in database roles, in this case the <em>db_datawriter<\/em> role.<\/p>\n<p>I hope this analysis of a rather rare scenario was interesting to some of you.<\/p>\n<p>Andreas<\/p>\n<p>(Still-owner of <strong><em>Sarpedon Quality Lab\u00ae<\/em><\/strong> (<a href=\"https:\/\/andreas-wolter.com\/en\/time-for-a-new-challenge-senior-program-manager-microsoft-sql-server-security-team\/\">http:\/\/andreas-wolter.com\/de-1809_news_senior_program_manager_microsoft_sql_server_security_team\/<\/a> ))<\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-16  el_after_av_textblock  el_before_av_one_full '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\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-17  el_after_av_hr  el_before_av_social_share  first flex_column_div av-zero-column-padding  '     ><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='av-social-sharing-box av-av_social_share-9212b0552d99e8199ae50242702f349f av-social-sharing-box-default  avia-builder-el-19  el_after_av_one_full  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/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\/1809_blocking_with_missing_permissions\/&#038;t=Blocking%20when%20missing%20Permissions%20%E2%80%93%20The%20strange%20case%20of%20the%20blocked%20index%20rebuild\" 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=Blocking%20when%20missing%20Permissions%20%E2%80%93%20The%20strange%20case%20of%20the%20blocked%20index%20rebuild&#038;url=https:\/\/andreas-wolter.com\/en\/?p=5709\" 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=Blocking%20when%20missing%20Permissions%20%E2%80%93%20The%20strange%20case%20of%20the%20blocked%20index%20rebuild&#038;url=https:\/\/andreas-wolter.com\/en\/1809_blocking_with_missing_permissions\/\" 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-20  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":"","protected":false},"author":4,"featured_media":5620,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,327,57],"tags":[],"class_list":["post-5709","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-locking-blocking","category-schemas-en","category-security-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5709","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=5709"}],"version-history":[{"count":5,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5709\/revisions"}],"predecessor-version":[{"id":5740,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/5709\/revisions\/5740"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/5620"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=5709"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=5709"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=5709"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}