{"id":1875,"date":"2014-01-24T11:00:26","date_gmt":"2014-01-24T16:00:26","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1875"},"modified":"2014-11-13T13:20:20","modified_gmt":"2014-11-13T18:20:20","slug":"grant-permissions-t-sql","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/","title":{"rendered":"How to GRANT Permissions in T-SQL"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nTo follow the Principle of Least Privilege<\/a> you need to at some point grant specific permissions to a login or user. While it is often a lot simpler to just add a user to the db_owner<\/span> fixed database role or a login to the sysadmin<\/span> fixed server role, being more granular with the permissions will lead to a better protected and more secure system.\n<\/p>\n

GRANT Example<\/h3>\n

\nTo grant a permission to a database principal<\/a> we can use the GRANT<\/span> statement. The GRANT<\/span> statement, while it looks fairly innocent, is actually quite complex. This shows in the number of pages dedicated to it in Books Online.<\/a>\n<\/p>\n

\nToday I am going to look at just the basic use case. The GRANT<\/span> statement basically has three sections:<\/p>\n

    \n
  1. What can be done?<\/li>\n
  2. Where (or to what) can it be done?<\/li>\n
  3. Who can do it?<\/li>\n<\/ol>\n

    The syntax looks like this:\n<\/p>\n

    \n[sql]\nGRANT SELECT ON OBJECT::dbo.tst TO TestUser1;
    \n[\/sql]\n<\/div>\n

    \nThe question \"What can be done?\" is answered by the word SELECT<\/span>. It describes the permission to be granted. In this case we are allowing for data to be selected.\n<\/p>\n

    \nThe answer to the \"Where or to what can it be done?\" question is given by OBJECT::dbo.tst<\/span>. It describes the securable; in this case the table or view that data can be selected from. The prefix OBJECT::<\/span> describes the type of the securable. If, as it is in this example, the securable is a database object like a table, the prefix is optional. In other cases like an entire schema the prefix must be specified.\n<\/p>\n

    \nFinally, TestUser1<\/span> is the grantee and describes the principal that will be able to execute the action after the GRANT<\/span> statement was executed.\n<\/p>\n

    \nSo, the above statement allows the database principal TestUser1<\/span> to execute a SELECT<\/span> against the table or view dbo.tst<\/span>.\n<\/p>\n

    GRANT in Action<\/h3>\n

    \nLet's see this in action. As described above, we have a database principal TestUser1<\/span> and a table or view dbo.tst<\/span>. Before the grant has been executed, an attempt to select from that object fails:\n<\/p>\n

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

    \nHowever, after the GRANT<\/span> statement was executed, the SELECT<\/span> statement works as expected:\n<\/p>\n

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

    Server Level Securables<\/h3>\n

    \nThe syntax described above works for almost all securables. There are however a few securables that are not live in a user database, like a login. When executing a GRANT<\/span> statement to grant a permission on such a server level securable, the current database must be master<\/span>. Otherwise an error is returned:\n<\/p>\n

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

    \nAlso, if the securable is at the server level, the grantee (the principal that the permission is granted to) must be a server principal<\/a>. If on the other hand the securable is a database object or a database itself, the grantee must be a database principal in that same database.\n<\/p>\n

    \nFinally be aware that if the securable is the instance itself, the securable does not need to be mentioned in the GRANT<\/span> statement. It is implied by the permission type, as for example in the case of the CONTROLL SERVER<\/span> permission:\n<\/p>\n

    \n[sql]\nGRANT CONTROL SERVER TO TestLogin1;
    \n[\/sql]\n<\/div>\n<\/p>\n

    Summary<\/h3>\n

    \nThe GRANT<\/span> statement can be used to give fine grained permissions to database and server principals. It is a surprisingly complex statement. In the basic form I has three sections that can be described by the three questions \"What?\", \"Where?\" and \"Who?\" They describe the permission, the securable and the grantee in that order.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

    The T-SQL GRANT statement is a surprisingly complex statement that can be used to control fine grained permissions. In its basic form is has three sections that answer the questions What?, Where? and Who?. Read on to get all the details.<\/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":[59,88,50,38,58,56],"class_list":["post-1875","post","type-post","status-publish","format-standard","hentry","category-general","category-security","tag-database-principals","tag-grant","tag-permission","tag-security-2","tag-security-management","tag-server-principals"],"acf":[],"yoast_head":"\nHow to GRANT Permissions in T-SQL - sqlity.net<\/title>\n<meta name=\"description\" content=\"The T-SQL GRANT statement is a surprisingly complex statement that can be used to control fine grained permissions. In its basic form is has three sections that answer the questions What?, Where? and Who?. Read on to get all the details.\" \/>\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\/1875\/grant-permissions-t-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to GRANT Permissions in T-SQL - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"The T-SQL GRANT statement is a surprisingly complex statement that can be used to control fine grained permissions. In its basic form is has three sections that answer the questions What?, Where? and Who?. Read on to get all the details.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/\" \/>\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-24T16:00:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:20:20+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/permission_denied_before_GRANT.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\\\/1875\\\/grant-permissions-t-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to GRANT Permissions in T-SQL\",\"datePublished\":\"2014-01-24T16:00:26+00:00\",\"dateModified\":\"2014-11-13T18:20:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/\"},\"wordCount\":616,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/permission_denied_before_GRANT.jpg\",\"keywords\":[\"database principals\",\"GRANT\",\"Permission\",\"security\",\"security management\",\"server principals\"],\"articleSection\":[\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/\",\"name\":\"How to GRANT Permissions in T-SQL - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/permission_denied_before_GRANT.jpg\",\"datePublished\":\"2014-01-24T16:00:26+00:00\",\"dateModified\":\"2014-11-13T18:20:20+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"The T-SQL GRANT statement is a surprisingly complex statement that can be used to control fine grained permissions. In its basic form is has three sections that answer the questions What?, Where? and Who?. Read on to get all the details.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/permission_denied_before_GRANT.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/01\\\/permission_denied_before_GRANT.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1875\\\/grant-permissions-t-sql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to GRANT Permissions in T-SQL\"}]},{\"@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":"How to GRANT Permissions in T-SQL - sqlity.net","description":"The T-SQL GRANT statement is a surprisingly complex statement that can be used to control fine grained permissions. In its basic form is has three sections that answer the questions What?, Where? and Who?. Read on to get all the details.","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\/1875\/grant-permissions-t-sql\/","og_locale":"en_US","og_type":"article","og_title":"How to GRANT Permissions in T-SQL - sqlity.net","og_description":"The T-SQL GRANT statement is a surprisingly complex statement that can be used to control fine grained permissions. In its basic form is has three sections that answer the questions What?, Where? and Who?. Read on to get all the details.","og_url":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-24T16:00:26+00:00","article_modified_time":"2014-11-13T18:20:20+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/permission_denied_before_GRANT.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\/1875\/grant-permissions-t-sql\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to GRANT Permissions in T-SQL","datePublished":"2014-01-24T16:00:26+00:00","dateModified":"2014-11-13T18:20:20+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/"},"wordCount":616,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/permission_denied_before_GRANT.jpg","keywords":["database principals","GRANT","Permission","security","security management","server principals"],"articleSection":["General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/","url":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/","name":"How to GRANT Permissions in T-SQL - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/permission_denied_before_GRANT.jpg","datePublished":"2014-01-24T16:00:26+00:00","dateModified":"2014-11-13T18:20:20+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"The T-SQL GRANT statement is a surprisingly complex statement that can be used to control fine grained permissions. In its basic form is has three sections that answer the questions What?, Where? and Who?. Read on to get all the details.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/permission_denied_before_GRANT.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/01\/permission_denied_before_GRANT.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1875\/grant-permissions-t-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to GRANT Permissions in T-SQL"}]},{"@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-uf","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1875","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=1875"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1875\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}