{"id":1937,"date":"2014-01-31T11:00:56","date_gmt":"2014-01-31T16:00:56","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1937"},"modified":"2014-11-13T13:19:42","modified_gmt":"2014-11-13T18:19:42","slug":"schema-permissions-simplify-permission-management","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/","title":{"rendered":"Using SQL Server Schema Permissions to Simplify Permission Management"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nSchema comes from the Greek word \u03c3\u03c7\u03ae\u03bc\u03b1<\/a> which means as much as \"shape\" or \"configuration\". The plural form is schemata (\u03c3\u03c7\u03ae\u03bc\u03b1\u03c4\u03b1).\n<\/p>\n

\nThe schema concept in SQL Server is something unique. While all RDBMSs are using the term \"schema\", only in SQL Server a schema is not the same as a database.\n<\/p>\n

\nIn SQL Server schemata can be used to group related objects together. That not only helps with organizing objects for easier administration and maintenance. More importantly it simplifies permission management.\n<\/p>\n

\nInstead of granting a given privilege on every object in a schema, you can just grant that same privilege on the schema itself. This automatically extends that permission to all objects that are created in that same schema, current objects and future ones.\n<\/p>\n

Schema Permission Example<\/h3>\n

\nLet's look at an example. First we need to create a schema and a table inside of it:\n<\/p>\n

\n[sql]\nCREATE SCHEMA TestSchema1;
\nGO
\nCREATE TABLE TestSchema1.tst1(id INT, col1 INT);
\nINSERT INTO TestSchema1.tst1 VALUES(42, 1);
\n[\/sql]\n<\/div>\n

\nWith schema and table in place, let's grant SELECT<\/span> on TestSchema1<\/span> to the user TestUser1<\/span>:\n<\/p>\n

\n[sql]\nGRANT SELECT ON SCHEMA::TestSchema1 TO TestUser1;
\n[\/sql]\n<\/div>\n

\nThis is the only privilege granted. In particular, we did not grant anything on the table itself. If the permission on the schema extends to the table, TestUser1<\/span> should now be able to select from that table. Let's see:\n<\/p>\n

\n\"Schema<\/a>\n<\/p>\n

\nAs promised, the permission was transferred automatically and TestUser1<\/span> was able to select from our table. Now let's see what happens if we create a new object in that schema:\n<\/p>\n

\n[sql]\nCREATE VIEW TestSchema1.tst2 AS SELECT NEWID() a_new_id;
\n[\/sql]\n<\/div>\n

\nEven though the new object is a view and not a table, the existing SELECT<\/span> permission automatically applies and TestUser1<\/span> is able to select from it:\n<\/p>\n

\n\"Schema<\/a>\n<\/p>\n

\nAs you can see, schema permissions apply to all objects to which the given permission is grantable. (It does for example not really make sense to grant SELECT<\/span> on a procedure.)\n<\/p>\n

\nNow, just to show that this is indeed due to the privilege granted on the schema, the next example creates a table in the dbo<\/span> schema:\n<\/p>\n

\n[sql]\nCREATE TABLE dbo.tst3(id INT, col1 INT);
\nINSERT INTO dbo.tst3 VALUES(42, 3);
\n[\/sql]\n<\/div>\n

\nHowever, not really to our surprise, TestUser1<\/span> can't access that table:\n<\/p\n\n\n

\n\"Schema<\/a>\n<\/p>\n<\/p>\n

Summary<\/h3>\n

\nTo simplify permission management, SQL Server allows us to group database objects together into schemata. Instead of granting privileges on each object, we can then just grant the privilege on the schema itself. Such a permission automatically extends to all objects (that that permission can be applied to) in that schema. This includes objects created at a later time.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

In SQL Server Schemata can significantly simplify permission management. Read on to find out how you can use schema permissions that extend automatically to all objects in that schema.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,34],"tags":[88,50,97,38,58,15],"class_list":["post-1937","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-grant","tag-permission","tag-schema","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nUsing SQL Server Schema Permissions to Simplify Permission Management - sqlity.net<\/title>\n<meta name=\"description\" content=\"In SQL Server Schemata can significantly simplify permission management, because schema permissions can extend automatically to objects in that schema.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using SQL Server Schema Permissions to Simplify Permission Management - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"In SQL Server Schemata can significantly simplify permission management, because schema permissions can extend automatically to objects in that schema.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-31T16:00:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:19:42+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Schema_permissions_do_extend_to_the_table.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Using SQL Server Schema Permissions to Simplify Permission Management\",\"datePublished\":\"2014-01-31T16:00:56+00:00\",\"dateModified\":\"2014-11-13T18:19:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/\"},\"wordCount\":406,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Schema_permissions_do_extend_to_the_table.jpg\",\"keywords\":[\"GRANT\",\"Permission\",\"schema\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/\",\"name\":\"Using SQL Server Schema Permissions to Simplify Permission Management - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Schema_permissions_do_extend_to_the_table.jpg\",\"datePublished\":\"2014-01-31T16:00:56+00:00\",\"dateModified\":\"2014-11-13T18:19:42+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"In SQL Server Schemata can significantly simplify permission management, because schema permissions can extend automatically to objects in that schema.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Schema_permissions_do_extend_to_the_table.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/Schema_permissions_do_extend_to_the_table.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1937\\\/schema-permissions-simplify-permission-management\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using SQL Server Schema Permissions to Simplify Permission Management\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using SQL Server Schema Permissions to Simplify Permission Management - sqlity.net","description":"In SQL Server Schemata can significantly simplify permission management, because schema permissions can extend automatically to objects in that schema.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/","og_locale":"en_US","og_type":"article","og_title":"Using SQL Server Schema Permissions to Simplify Permission Management - sqlity.net","og_description":"In SQL Server Schemata can significantly simplify permission management, because schema permissions can extend automatically to objects in that schema.","og_url":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-31T16:00:56+00:00","article_modified_time":"2014-11-13T18:19:42+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Schema_permissions_do_extend_to_the_table.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Using SQL Server Schema Permissions to Simplify Permission Management","datePublished":"2014-01-31T16:00:56+00:00","dateModified":"2014-11-13T18:19:42+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/"},"wordCount":406,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Schema_permissions_do_extend_to_the_table.jpg","keywords":["GRANT","Permission","schema","security","security management","SQL Server"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/","url":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/","name":"Using SQL Server Schema Permissions to Simplify Permission Management - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Schema_permissions_do_extend_to_the_table.jpg","datePublished":"2014-01-31T16:00:56+00:00","dateModified":"2014-11-13T18:19:42+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"In SQL Server Schemata can significantly simplify permission management, because schema permissions can extend automatically to objects in that schema.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Schema_permissions_do_extend_to_the_table.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/Schema_permissions_do_extend_to_the_table.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1937\/schema-permissions-simplify-permission-management\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Using SQL Server Schema Permissions to Simplify Permission Management"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-vf","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1937","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1937"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1937\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1937"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1937"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1937"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}