{"id":7462,"date":"2026-04-30T13:28:59","date_gmt":"2026-04-30T18:28:59","guid":{"rendered":"https:\/\/andreas-wolter.com\/?p=7462"},"modified":"2026-04-30T13:28:59","modified_gmt":"2026-04-30T18:28:59","slug":"2604_sqlserver_external_model_stale_permissions","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/2604_sqlserver_external_model_stale_permissions\/","title":{"rendered":"When DROP USER Doesn\u2019t Fully Drop Access: Stale EXTERNAL MODEL Permissions in SQL Server\u2019s AI Integrations"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-m0cxh8ps-14f8b266073a9babed94d91b53a51ff0\">\n#top .av-special-heading.av-m0cxh8ps-14f8b266073a9babed94d91b53a51ff0{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-m0cxh8ps-14f8b266073a9babed94d91b53a51ff0 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-m0cxh8ps-14f8b266073a9babed94d91b53a51ff0 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-m0cxh8ps-14f8b266073a9babed94d91b53a51ff0 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\"  >When DROP USER Doesn\u2019t Fully Drop Access: Stale EXTERNAL MODEL Permissions in SQL Server\u2019s AI Integrations<\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-m0cxgkjy-c935304b4106b45214698f40e83a9894 '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><h2>Why deterministic security matters<\/h2>\n<p>Unlike areas such as performance tuning, where partial improvements or probabilistic outcomes may still provide value, security controls must behave predictably and consistently. The same configuration, permission, or revocation should always produce the same security outcome.<\/p>\n<p>When identity or permission boundaries behave inconsistently &#8211; even under specific lifecycle conditions &#8211; that is more than a product bug. It becomes a security-relevant design issue, because security depends not just on how access is granted, but also on how reliably it is revoked.<\/p>\n<p>While investigating the new permission model introduced alongside SQL Server 2025\u2019s AI integration and vector search capabilities (Article: <a href=\"https:\/\/andreas-wolter.com\/en\/2603_sqlserver2025_newpermissions\/\">New Permissions in SQL Server 2025<\/a>), I encountered a case where EXTERNAL MODEL permissions can persist after a user is dropped, creating stale authorization state.<\/p>\n<p>Expected deprovisioning behavior<\/p>\n<p>One of the fundamental expectations that DBAs have is that when accounts are dropped, associated permission-assignments are removed as well.<\/p>\n<ol>\n<li>Drop principal<\/li>\n<li>Associated permissions removed from metadata<\/li>\n<\/ol>\n<p>That assumption is foundational to deprovisioning, least privilege, and access governance.<\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<h2><a name=\"_Toc228261899\"><\/a>Observed behavior<\/h2>\n<p>In testing the new EXTERNAL MODEL object to register external AI models such as OpenAI within SQL Server, I observed a permission lifecycle bug:<\/p>\n<p>When a user granted EXECUTE on an external model is dropped, the permission entry remains in sys.database_permissions \u2013 together with the original principal_id.<\/p>\n<blockquote><p><strong><em>Important background information<\/em><\/strong><br \/>\nIn SQL Server, newly created principals are assigned the lowest available principal_id.<\/p><\/blockquote>\n<p>And because newly created principals by design receive the lowest available principal_id, <strong>this stale permission can effectively become associated with a newly created principal that inherits the reused principal_id<\/strong>.<\/p>\n<h3>What this does &#8211; and does not &#8211; mean<\/h3>\n<p>This does <strong>not<\/strong> automatically mean the new principal can execute the model immediately.<\/p>\n<p>To successfully use the external model, the new principal must also have the REFERENCES permission on the database scoped credential used by that model.<\/p>\n<p><strong>No automatic privilege escalation: credential permission remains a prerequisite.<\/strong><\/p>\n<p>However, if the new principal is later granted access to the <u>same credential<\/u> for another legitimate external model, the stale permission on the old model may become usable as well.<\/p>\n<p>How unintended access can emerge<\/p>\n<p>Let\u2019s say there are multiple external models in play, each for separate use cases with different confidentiality or governance requirements.<\/p>\n<p>A principal may gain access to an external model it was never explicitly granted, because:<\/p>\n<ul>\n<li>Old permission rows persist<\/li>\n<li>principal_id is reused<\/li>\n<li>Credential access overlaps<\/li>\n<\/ul>\n<p><strong>Stale-permission spread:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong><\/p>\n<p>If the newly created principal is a <strong>database role<\/strong> rather than an individual user, this stale permission will be passed on to potentially <strong>multiple users <\/strong>through role membership.<\/p>\n<h2>Practical example<\/h2>\n<p><strong>Create User1:<\/strong><\/p>\n<ul>\n<li>Granted EXECUTE on MyOpenAIModel1<\/li>\n<li>Granted REFERENCES to Credential A<\/li>\n<\/ul>\n<p><strong>Drop User1:<\/strong><\/p>\n<ul>\n<li>Principal removed<\/li>\n<li>External model permission persists<\/li>\n<\/ul>\n<p>Here is a screenshot of the stale permission entry in the system object sys.database_permissions:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-7463 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLExternalModel_StalePermissionentry.jpg\" alt=\"\" width=\"628\" height=\"347\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLExternalModel_StalePermissionentry.jpg 628w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLExternalModel_StalePermissionentry-300x166.jpg 300w\" sizes=\"auto, (max-width: 628px) 100vw, 628px\" \/><\/p>\n<p><strong>Create User2 or RoleX:<\/strong><\/p>\n<ul>\n<li>Receives lowest available principal_id \u2013 in this case of former User1<\/li>\n<li>Granted Credential A for a different model<\/li>\n<\/ul>\n<p><strong>Result:<\/strong><\/p>\n<p>User2 \/ RoleX may now unexpectedly execute MyOpenAIModel1 as well, even though the stale permission row was never intentionally granted to User2 \/ RoleX.<\/p>\n<p>Here is a visual of what is happening:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-large wp-image-7465 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLServer_ExternalModel_stale_permission_reassignment-1030x687.png\" alt=\"\" width=\"1030\" height=\"687\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLServer_ExternalModel_stale_permission_reassignment-1030x687.png 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLServer_ExternalModel_stale_permission_reassignment-300x200.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLServer_ExternalModel_stale_permission_reassignment-768x512.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLServer_ExternalModel_stale_permission_reassignment-705x470.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2026\/04\/202604_SQLServer_ExternalModel_stale_permission_reassignment.png 1200w\" sizes=\"auto, (max-width: 1030px) 100vw, 1030px\" \/><\/p>\n<h2><a name=\"_Toc228261902\"><\/a>Security and governance implications<\/h2>\n<p><strong>Identity lifecycle and authorization hygiene<\/strong><\/p>\n<p>Identity lifecycle and authorization hygiene matter because real-world security controls depend on predictable deprovisioning.<\/p>\n<p><strong>Confidentiality<\/strong><\/p>\n<p>If the old model points to sensitive AI endpoints, proprietary workflows, or regulated processing paths, unintended reassignment could expose capabilities beyond intended authorization.<\/p>\n<p><strong>Governance<\/strong><\/p>\n<p>As newer securable classes are introduced, permission lifecycle consistency becomes increasingly important for governance:<\/p>\n<ul>\n<li>Joiner \/ mover \/ leaver controls (JML)<\/li>\n<li>Least privilege assumptions<\/li>\n<li>Permission reviews<\/li>\n<li>Audit expectations<\/li>\n<\/ul>\n<h2>Why AI integrations raise the stakes<\/h2>\n<p>External models are not ordinary static table objects.<\/p>\n<p>They may:<\/p>\n<ul>\n<li>Connect to OpenAI \/ Azure OpenAI endpoints<\/li>\n<li>Use a different AI model inference endpoint over time (the EXTERNAL MODEL object in SQL Server explicitly allows for that flexibility)<\/li>\n<li>Process sensitive enterprise text<\/li>\n<li>Support embeddings or downstream automation<\/li>\n<li>Interact with governed AI workflows<\/li>\n<\/ul>\n<p>That makes stale permission persistence more consequential than a simple metadata inconsistency.<\/p>\n<p>Practical risk reduction<\/p>\n<p>Microsoft MSRC acknowledged this behavior as a bug and assigned it <u>low severity<\/u>, which may affect remediation prioritization.<\/p>\n<p>Given that External Models are database-scoped objects, you cannot use schema-level permissions as a workaround.<\/p>\n<p><strong>One best practice may help reduce risk: assign permissions to roles rather than directly to users.<\/strong><\/p>\n<p>Grant permissions to database roles and drop users. The role will remain in the system, together with the permission which is expected behavior.<\/p>\n<p>However, this does not fully solve permission removal if the role itself should later be decommissioned. In that case, dropping the external model may still be required.<\/p>\n<h2>Final thought<\/h2>\n<p>Historically, SQL Server has maintained strong consistency around principal and permission cleanup, which makes this deviation on a newer securable type somewhat unusual.<\/p>\n<p>Security is defined not only by how effectively access is granted, but by how reliably it is revoked. As SQL Server expands into newer AI-driven securables, permission lifecycle consistency should remain just as foundational as the access controls themselves.<\/p>\n<p>Andreas<\/p>\n<\/div><\/section>\r\n\r\n<div  class='flex_column av-27ilfv-30c3733b2a94e65a34b4942e6b6a5f6f av_one_full  avia-builder-el-2  el_after_av_textblock  el_before_av_social_share  first flex_column_div  column-top-margin'     ><div  class='hr av-baku8u-c77559299fb7cb036a9bcb2d27e7c839 hr-default  avia-builder-el-3  el_before_av_textblock  avia-builder-el-first '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\n<section  class='av_textblock_section av-mo242alu-8257e57f95cd0d93808b09100c4a8236 '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Need to secure your own SQL Server environment against these vulnerabilities? Andreas Wolter and the team at Sarpedon Quality Lab offer proprietary, deep-tier security assessments<\/p>\n<\/div><\/section>\n<div  class='avia-button-wrap av-mo243ot6-164fa501a180d18458cc0048066b5ee8-wrap avia-button-center  avia-builder-el-5  el_after_av_textblock  el_before_av_hr '>\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-mo243ot6-164fa501a180d18458cc0048066b5ee8\">\n#top #wrap_all .avia-button.av-mo243ot6-164fa501a180d18458cc0048066b5ee8{\nfont-size:14px;\nbackground-color:#75a823;\nborder-color:#75a823;\ncolor:#ffffff;\nbox-shadow: 0 0 5px 5px ;\ntransition:all 0.4s ease-in-out;\n}\n<\/style>\n<a href=\"https:\/\/sarpedonqualitylab.us\/sql-server-security-assessment\/\" class=\"avia-button av-mo243ot6-164fa501a180d18458cc0048066b5ee8 avia-icon_select-yes-left-icon avia-size-medium avia-position-center\" target=\"_blank\" rel=\"noopener\"><span class='avia_button_icon avia_button_icon_left' aria-hidden='true' data-av_icon='\ue832' data-av_iconfont='entypo-fontello'><\/span><span class='avia_iconbox_title' >Explore Sarpedon Quality Lab Assessments<\/span><\/a><\/div>\n<div  class='hr av-9inuj-ef570c4ea0fba0353373c5000396a879 hr-default  avia-builder-el-6  el_after_av_button  avia-builder-el-last '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div><\/div>\r\n\r\n<div  class='av-social-sharing-box av-5n5vpa-78ffdd9d224b4a246af65bdc00dce900 av-social-sharing-box-default  avia-builder-el-7  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 article<\/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\/2604_sqlserver_external_model_stale_permissions\/&#038;t=When%20DROP%20USER%20Doesn%E2%80%99t%20Fully%20Drop%20Access%3A%20Stale%20EXTERNAL%20MODEL%20Permissions%20in%20SQL%20Server%E2%80%99s%20AI%20Integrations\" 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=When%20DROP%20USER%20Doesn%E2%80%99t%20Fully%20Drop%20Access%3A%20Stale%20EXTERNAL%20MODEL%20Permissions%20in%20SQL%20Server%E2%80%99s%20AI%20Integrations&#038;url=https:\/\/andreas-wolter.com\/en\/?p=7462\" 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=When%20DROP%20USER%20Doesn%E2%80%99t%20Fully%20Drop%20Access%3A%20Stale%20EXTERNAL%20MODEL%20Permissions%20in%20SQL%20Server%E2%80%99s%20AI%20Integrations&#038;url=https:\/\/andreas-wolter.com\/en\/2604_sqlserver_external_model_stale_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-4ofg9q-c2108540b480aba02923089240a3a176\">\n#top .hr.hr-invisible.av-4ofg9q-c2108540b480aba02923089240a3a176{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-4ofg9q-c2108540b480aba02923089240a3a176 hr-invisible  avia-builder-el-8  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-284ftq-f5a1564cd6b8ffad6ce835e2d40de4b7  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":4,"featured_media":7465,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57,385,383],"tags":[206],"class_list":["post-7462","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security-en","category-ai-integration","category-sql-server-2025","tag-sql-security"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/7462","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=7462"}],"version-history":[{"count":2,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/7462\/revisions"}],"predecessor-version":[{"id":7469,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/7462\/revisions\/7469"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/7465"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=7462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=7462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=7462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}