{"id":1824,"date":"2019-07-05T15:41:54","date_gmt":"2019-07-05T15:41:54","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1824"},"modified":"2019-07-05T15:51:59","modified_gmt":"2019-07-05T15:51:59","slug":"cannot-alter-the-schema-msg-15151-level-16-state-1-line-1","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=1824","title":{"rendered":"Cannot Alter the Schema &#8211; Msg 15151, Level 16, State 1, Line 1"},"content":{"rendered":"<p>A rookie developer reached out to me requesting help to move a couple of tables to a new schema.\u00a0 Thinking the developer did not have permissions to the one of the schemas, I agreed to help.\u00a0 However, when I ran the &#8220;Alter Schema&#8221; statement, I received the following error:<\/p>\n<pre>Msg 15151, Level 16, State 1, Line 1\r\n\r\nCannot alter the schema 'HR', because it does not exist or you do not have permission.<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/MSG15151_CannotAlterSchema.jpg\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1826 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/MSG15151_CannotAlterSchema.jpg\" alt=\"ALTER SCHEMA ERROR\" width=\"800\" height=\"216\" \/><\/a><\/p>\n<p>After a little digging, I found that the reason the developer could not alter the schema for the tables, was because the schema did not exist in this database.\u00a0 The tables moved to the new schema easily after I ran the following SQL to create the schema:<\/p>\n<pre>IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = ' &lt;new schema name &gt; '))\r\n\r\nBEGIN\r\n\r\nEXEC ('CREATE SCHEMA [ &lt;new schema name &gt; ] AUTHORIZATION [dbo]')\r\n\r\nEND\r\n\r\nALTER SCHEMA [&lt;new schema name &gt;]\r\nTRANSFER [dbo].[ &lt;table&gt; ];\r\nGO<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/CreateSchema.jpg\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1825 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/CreateSchema.jpg\" alt=\"CREATE SCHEMA\" width=\"1209\" height=\"589\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/CreateSchema.jpg 1209w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/CreateSchema-300x146.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/CreateSchema-768x374.jpg 768w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/CreateSchema-1024x499.jpg 1024w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/07\/CreateSchema-210x102.jpg 210w\" sizes=\"(max-width: 1209px) 100vw, 1209px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A rookie developer reached out to me requesting help to move a couple of tables to a new schema.\u00a0 Thinking the developer did not have permissions to the one of &hellip; <a class=\"readmore\" href=\"https:\/\/www.theSQLReport.com\/?p=1824\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":1826,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[160,161,121],"class_list":["post-1824","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-alter-schema","tag-create-schema","tag-msg-15151"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1824"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1824"}],"version-history":[{"count":4,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1824\/revisions"}],"predecessor-version":[{"id":2115,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1824\/revisions\/2115"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1826"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1824"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1824"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1824"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}