{"id":2129,"date":"2014-02-25T11:00:42","date_gmt":"2014-02-25T16:00:42","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2129"},"modified":"2014-11-13T12:00:11","modified_gmt":"2014-11-13T17:00:11","slug":"effective-permissions","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/","title":{"rendered":"What are my own effective permissions? Get Answers with sys.fn_my_permissions."},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nsys.database_permissions<\/span><\/a> and sys.server_permissions<\/span><\/a> can be used to determine if a user has a given permission on a specific object. Hover, this is not enough to actually see if that user or login has access to the object in question. The principal could be a member of a role or even a Windows group and could get additional grants or denies through that role or group membership<\/a>. You also need to take the hierarchy of securables<\/a> and the hierarchy of permissions<\/a> into account to see if a specific permission is in effect. In short, it is a lot of work to get from one of the permission catalog views mentioned above to the list of actually effective permissions for a security principal.\n<\/p>\n

\nIf you are just interested in the permissions of the current connection, you can greatly reduce that work by using the built-in sys.fn_my_permissions<\/span> function.\n<\/p>\n

Seeing all effective permissions<\/h3>\n

\nTo see sys.fm_my_permissions<\/span> in action, we need to first create a security principal and a securable:\n<\/p>\n

\n[sql]\nCREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
\nCREATE USER TestUser1 FOR LOGIN TestLogin1;
\nCREATE ROLE TestRole1;
\nALTER ROLE TestRole1 ADD MEMBER TestUser1;
\nGO
\nCREATE SCHEMA TestSchema1;
\nGO
\nCREATE TABLE TestSchema1.tst(id INT,col1 INT);
\nINSERT INTO TestSchema1.tst VALUES(42,17);
\n[\/sql]\n<\/div>\n

\nThis creates a login and an associated user that is also member in a new database role. The script also creates a schema and a table within that schema. Now we can grant a few permissions:\n<\/p>\n

\n[sql]\nGRANT SELECT ON SCHEMA::[TestSchema1] TO [TestRole1];
\nGRANT INSERT ON SCHEMA::[TestSchema1] TO [TestUser1];
\nGRANT UPDATE ON OBJECT::[TestSchema1].[tst] TO [TestRole1] WITH GRANT OPTION;
\nDENY SELECT ON OBJECT::[TestSchema1].[tst]([col1]) TO [TestUser1];
\n[\/sql]\n<\/div>\n

\nNote, that not everything is granting to the user. Two of the permissions go to the role and one is even a DENY<\/span>. Now let us see what sys.fn_my_permissions<\/span> returns, when executed by TestLogin1<\/span>. For that, we are going to use this query:\n<\/p>\n

\n[sql]\nEXECUTE AS USER='TestUser1';
\nGO
\nSELECT * FROM sys.fn_my_permissions('TestSchema1.tst','OBJECT') AS FMP;
\nGO
\nREVERT;
\n[\/sql]\n<\/div>\n

\nThe function takes two parameters that are not optional. The first takes the name of the securable you are interested in and the second represents the type of that securable. If you pass in NULL<\/span> or DEFAULT<\/span> for the two parameters, only server level permissions will be returned. The above query returns this result:\n<\/p>\n

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

\nAs you can see, if the securable is a table (or view), the result includes column level permission as well. Let us quickly walk through the result. We had granted UPDATE<\/span> on the table. Therefore, we expect to see three rows returned, one telling us we can update the table and one each for the two columns. We also granted INSERT<\/span> on the schema and as such should see the same three rows. However, there is only one INSERT<\/span> row. The reason is, that INSERT<\/span> is not a privilege that can be granted on a column. An insert is always an action that creates an entire row and therefore affects all columns.\n<\/p>\n

\nFinally, there is a GRANT SELECT<\/span> on the schema and a DENY SELECT<\/span> on one of the columns. That means that effectively only the id<\/span> column can be selected from. Therefore above result contains only one row pertaining to the SELECT<\/span> privilege.\n<\/p>\n

\nThis result shows that sys.fn_my_permissions<\/span> takes the hierarchy of securables and the hierarchy of security principals into account when determining the effective permissions for the current connection. The following image shows the result of running the same query, after CONTROLL<\/span> on the schema TestSchema1<\/span> has been granted to the role TestRole1<\/span>:\n<\/p>\n

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

\nThis clearly demonstrates that the hierarchy of permissions is also taken into account by sys.fn_my_permissions<\/span> when working out the list of effective permissions.\n<\/p>\n

Summary<\/h3>\n

\nThe sys.fn_my_permissions<\/span> function can be a helpful tool in your permission management tool box. However, it returns results only for a single securable and only for the current connection. To see the effective permissions of another security principal EXECUTE AS<\/span><\/a> can be used as shown in the above examples. However, this makes this function at best a cumbersome tool to get an overview over the effective permissions on an entire database or even SQL Server instance.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Are you looking to identify your effective permissions in SQL Server, the permissions actually in effect after taking things like denies or role membership into account? Read on to see how sys.fn_my_permissions can help.<\/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,103,88,50,98,38,58,15],"class_list":["post-2129","post","type-post","status-publish","format-standard","hentry","category-dmvs-cvs","category-general","category-security","category-security-dmvs-cvs","tag-cv","tag-deny","tag-grant","tag-permission","tag-revoke","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nWhat are my own effective permissions? Get Answers with sys.fn_my_permissions. - sqlity.net<\/title>\n<meta name=\"description\" content=\"Are you looking to identify your effective permissions in SQL Server taking denies and role membership into account? See how sys.fn_my_permissions can help.\" \/>\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\/2129\/effective-permissions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What are my own effective permissions? Get Answers with sys.fn_my_permissions. - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Are you looking to identify your effective permissions in SQL Server taking denies and role membership into account? See how sys.fn_my_permissions can help.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2129\/effective-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-25T16:00:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:00:11+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_my_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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"What are my own effective permissions? Get Answers with sys.fn_my_permissions.\",\"datePublished\":\"2014-02-25T16:00:42+00:00\",\"dateModified\":\"2014-11-13T17:00:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/\"},\"wordCount\":789,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_my_permissions.jpg\",\"keywords\":[\"CV\",\"DENY\",\"GRANT\",\"Permission\",\"REVOKE\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"DMVs & CVs\",\"General\",\"Security\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/\",\"name\":\"What are my own effective permissions? Get Answers with sys.fn_my_permissions. - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_my_permissions.jpg\",\"datePublished\":\"2014-02-25T16:00:42+00:00\",\"dateModified\":\"2014-11-13T17:00:11+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Are you looking to identify your effective permissions in SQL Server taking denies and role membership into account? See how sys.fn_my_permissions can help.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_my_permissions.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/sys.fn_my_permissions.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2129\\\/effective-permissions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What are my own effective permissions? Get Answers with sys.fn_my_permissions.\"}]},{\"@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":"What are my own effective permissions? Get Answers with sys.fn_my_permissions. - sqlity.net","description":"Are you looking to identify your effective permissions in SQL Server taking denies and role membership into account? See how sys.fn_my_permissions can help.","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\/2129\/effective-permissions\/","og_locale":"en_US","og_type":"article","og_title":"What are my own effective permissions? Get Answers with sys.fn_my_permissions. - sqlity.net","og_description":"Are you looking to identify your effective permissions in SQL Server taking denies and role membership into account? See how sys.fn_my_permissions can help.","og_url":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-25T16:00:42+00:00","article_modified_time":"2014-11-13T17:00:11+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_my_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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"What are my own effective permissions? Get Answers with sys.fn_my_permissions.","datePublished":"2014-02-25T16:00:42+00:00","dateModified":"2014-11-13T17:00:11+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/"},"wordCount":789,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_my_permissions.jpg","keywords":["CV","DENY","GRANT","Permission","REVOKE","security","security management","SQL Server"],"articleSection":["DMVs & CVs","General","Security","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/","url":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/","name":"What are my own effective permissions? Get Answers with sys.fn_my_permissions. - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_my_permissions.jpg","datePublished":"2014-02-25T16:00:42+00:00","dateModified":"2014-11-13T17:00:11+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Are you looking to identify your effective permissions in SQL Server taking denies and role membership into account? See how sys.fn_my_permissions can help.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2129\/effective-permissions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_my_permissions.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/sys.fn_my_permissions.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2129\/effective-permissions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"What are my own effective permissions? Get Answers with sys.fn_my_permissions."}]},{"@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-yl","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2129","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=2129"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2129\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}