{"id":2122,"date":"2014-02-24T11:00:46","date_gmt":"2014-02-24T16:00:46","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2122"},"modified":"2014-11-13T12:00:32","modified_gmt":"2014-11-13T17:00:32","slug":"possible-permissions","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/","title":{"rendered":"Identify All Possible Permissions using sys.fn_builtin_permission"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nSQL Server 2012 defines 214 different permissions that you can grant or deny. How do I know that? Well, there is an App<\/del> catalog function for that: sys.fn_builtin_permissions<\/span>\n<\/p>\n

Listing Possible Permissions<\/h3>\n

\nIf you are looking to find out what permission you can grant on a given securable, you could look in Books Online under the GRANT<\/span><\/a> statement. That topic is actually quite substantial and you should take a look at it. However, sometimes BOL is not accessible or you just want a quick look at the possible values. In that case you can use the sys.fn_builtin_permissions<\/span> catalog function. It returns one row for each possible permission, containing the grantable (or deniable) privilege and the securable class that the privilege applies to. To try the function out, let us run this query:\n<\/p>\n

\n[sql]\nSELECT * FROM sys.fn_builtin_permissions(DEFAULT) AS FBP;
\n[\/sql]\n<\/div>\n

\nThe parameter of the function can be used to restrict the output to one class of securables. By passing in for example 'database'<\/span>, only permissions that can be granted on a database will be returned. The output of above query looks like this:\n<\/p>\n

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

\nThe first two columns, permission_name<\/span> and class_desc<\/span> identify the privilege as well as the type of securable that this privilege can be granted on. The values in those columns match the values you would find in the sys.database_permissions<\/span><\/a> and sys.server_permissions<\/span><\/a> catalog views.\n<\/p>\n

\nThe covering_permission_name<\/span> column identifies the next highest privilege on the same securable that implies the given privilege. If you look at the image above, you can see that for example the ALTER<\/span> privilege on a database is implied by the CONTROL<\/span> privilege on the same database. However, the CONTROL<\/span> privilege itself is not implied by any other database level permission.\n<\/p>\n

\nThe parent_covering_permission_name<\/span> and parent_class_desc<\/span> columns identify a permission on the next level of the hierarchy of securables that implies the given permission. In above image you can see for example that the UPDATE<\/span> privilege on an object is implied by the UPDATE<\/span> privilege on the schema that the object belongs to. Also, while the CONTROL<\/span> privilege, granted on a database, does not have an implying permission on the database level, it is implied by the server level CONTROL SERVER<\/span> privilege.\n<\/p>\n

\nThe permission hierarchy is fully transitive. That means for example that the ALTER<\/span> privilege for a database is also implied by the server level CONTROL SERVER<\/span> privilege, because the database level ALTER<\/span> privilege is implied by the database level CONTROL<\/span> privilege which in turn is implied by CONROL SERVER<\/span> as we had seen before.\n<\/p>\n

Summary<\/h3>\n

\nThe catalog function sys.fn_builtin_permissions<\/span> can be used to list all possible permissions together with the securable classes they can be granted or denied on. The function can also be used to traverse the permission hierarchy to identify which permission is implied by which other permissions.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

The sys.fn_builtin_permissions catalog function returns a result set that contains one row for each possible permission that you can grant (or deny) in SQL Server. Read on to find out how to use it and how to interpret the results.<\/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_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[118,5,34,119],"tags":[122,123,103,88,50,98,38,58,121,15],"class_list":["post-2122","post","type-post","status-publish","format-standard","hentry","category-dmvs-cvs","category-general","category-security","category-security-dmvs-cvs","tag-cv","tag-database-permissions","tag-deny","tag-grant","tag-permission","tag-revoke","tag-security-2","tag-security-management","tag-server-permissions","tag-sql-server"],"acf":[],"yoast_head":"\nIdentify All Possible Permissions using sys.fn_builtin_permission - sqlity.net<\/title>\n<meta name=\"description\" content=\"Find out how you can identify all possible permissions you can grant or deny in SQL Server using the sys.fn_builtin_permissions catalog function.\" \/>\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\/2122\/possible-permissions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Identify All Possible Permissions using sys.fn_builtin_permission - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Find out how you can identify all possible permissions you can grant or deny in SQL Server using the sys.fn_builtin_permissions catalog function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/\" \/>\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-02-24T16:00:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:00:32+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_builtin_permissions.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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Identify All Possible Permissions using sys.fn_builtin_permission\",\"datePublished\":\"2014-02-24T16:00:46+00:00\",\"dateModified\":\"2014-11-13T17:00:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/\"},\"wordCount\":545,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_builtin_permissions.jpg\",\"keywords\":[\"CV\",\"database permissions\",\"DENY\",\"GRANT\",\"Permission\",\"REVOKE\",\"security\",\"security management\",\"server permissions\",\"SQL Server\"],\"articleSection\":[\"DMVs & CVs\",\"General\",\"Security\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/\",\"name\":\"Identify All Possible Permissions using sys.fn_builtin_permission - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_builtin_permissions.jpg\",\"datePublished\":\"2014-02-24T16:00:46+00:00\",\"dateModified\":\"2014-11-13T17:00:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Find out how you can identify all possible permissions you can grant or deny in SQL Server using the sys.fn_builtin_permissions catalog function.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_builtin_permissions.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_builtin_permissions.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2122\\\/possible-permissions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Identify All Possible Permissions using sys.fn_builtin_permission\"}]},{\"@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":"Identify All Possible Permissions using sys.fn_builtin_permission - sqlity.net","description":"Find out how you can identify all possible permissions you can grant or deny in SQL Server using the sys.fn_builtin_permissions catalog function.","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\/2122\/possible-permissions\/","og_locale":"en_US","og_type":"article","og_title":"Identify All Possible Permissions using sys.fn_builtin_permission - sqlity.net","og_description":"Find out how you can identify all possible permissions you can grant or deny in SQL Server using the sys.fn_builtin_permissions catalog function.","og_url":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-24T16:00:46+00:00","article_modified_time":"2014-11-13T17:00:32+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_builtin_permissions.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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Identify All Possible Permissions using sys.fn_builtin_permission","datePublished":"2014-02-24T16:00:46+00:00","dateModified":"2014-11-13T17:00:32+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/"},"wordCount":545,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_builtin_permissions.jpg","keywords":["CV","database permissions","DENY","GRANT","Permission","REVOKE","security","security management","server permissions","SQL Server"],"articleSection":["DMVs & CVs","General","Security","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/","url":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/","name":"Identify All Possible Permissions using sys.fn_builtin_permission - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_builtin_permissions.jpg","datePublished":"2014-02-24T16:00:46+00:00","dateModified":"2014-11-13T17:00:32+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Find out how you can identify all possible permissions you can grant or deny in SQL Server using the sys.fn_builtin_permissions catalog function.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2122\/possible-permissions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_builtin_permissions.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_builtin_permissions.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2122\/possible-permissions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Identify All Possible Permissions using sys.fn_builtin_permission"}]},{"@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_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-ye","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2122","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=2122"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2122\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2122"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}