{"id":910,"date":"2005-09-10T23:09:00","date_gmt":"2005-09-10T23:09:00","guid":{"rendered":"\/blogs\/bobb\/post\/Security-in-SQL-Server-2005-execute-aswith-cookie.aspx"},"modified":"2013-07-05T10:37:32","modified_gmt":"2013-07-05T17:37:32","slug":"security-in-sql-server-2005-execute-as-with-cookie","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/","title":{"rendered":"Security in SQL Server 2005 – execute as…with cookie"},"content":{"rendered":"

You’ve probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in “CREATE PROCEDURE foo WITH EXECUTE AS OWNER”. But EXECUTE AS can also be used at a session level,like this:<\/p>\n

EXECUTE AS USER=’fred’
\n— some T-SQL here
\nREVERT<\/p>\n

This is meant to replace the SETUSER verb because you might want things executed as a specfic login as well as user. So there’s also EXECUTE AS LOGIN=’freds_login’. You can aribtrarily nest EXECUTE AS blocks and there’s subject to the nice, granular, IMPERSONATE privilege. Originally, I’d heard there was going to be a “REVERT ALL”, but there’s security mischief that might happen with this, so it never happened.<\/p>\n

BUT.
\nThere are two variations of EXECUTE AS that are interesting, based on security considerations. First one is “EXECUTE AS USER=’fred’ WITH NO REVERT”. Exactly what it sounds like.<\/p>\n

Next one is EXECUTE AS WITH COOKIE. The idea is similar to what I described in sp_unsetapprole<\/a>. You don’t want just any ol’ level of impersonation to do REVERT. So EXECUTE..WITH COOKIE gives you a cookie to use with REVERT.<\/p>\n","protected":false},"excerpt":{"rendered":"

You’ve probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in “CREATE PROCEDURE foo WITH EXECUTE AS OWNER”. But EXECUTE AS can also be used at a session level,like this: EXECUTE AS USER=’fred’ — some T-SQL here REVERT This is meant to replace the SETUSER verb because you […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,28],"tags":[],"class_list":["post-910","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2005"],"yoast_head":"\nSecurity in SQL Server 2005 - execute as...with cookie - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Security in SQL Server 2005 - execute as...with cookie - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"You’ve probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in “CREATE PROCEDURE foo WITH EXECUTE AS OWNER”. But EXECUTE AS can also be used at a session level,like this: EXECUTE AS USER=’fred’ — some T-SQL here REVERT This is meant to replace the SETUSER verb because you […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2005-09-10T23:09:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-07-05T17:37:32+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/\",\"name\":\"Security in SQL Server 2005 - execute as...with cookie - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2005-09-10T23:09:00+00:00\",\"dateModified\":\"2013-07-05T17:37:32+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Security\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/security\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Security in SQL Server 2005 – execute as…with cookie\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Security in SQL Server 2005 - execute as...with cookie - Bob Beauchemin","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:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/","og_locale":"en_US","og_type":"article","og_title":"Security in SQL Server 2005 - execute as...with cookie - Bob Beauchemin","og_description":"You’ve probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in “CREATE PROCEDURE foo WITH EXECUTE AS OWNER”. But EXECUTE AS can also be used at a session level,like this: EXECUTE AS USER=’fred’ — some T-SQL here REVERT This is meant to replace the SETUSER verb because you […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/","og_site_name":"Bob Beauchemin","article_published_time":"2005-09-10T23:09:00+00:00","article_modified_time":"2013-07-05T17:37:32+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/","name":"Security in SQL Server 2005 - execute as...with cookie - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2005-09-10T23:09:00+00:00","dateModified":"2013-07-05T17:37:32+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/security-in-sql-server-2005-execute-as-with-cookie\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Security","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/security\/"},{"@type":"ListItem","position":3,"name":"Security in SQL Server 2005 – execute as…with cookie"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/910","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=910"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/910\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=910"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=910"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=910"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}