{"id":8329,"date":"2019-01-21T06:44:33","date_gmt":"2019-01-21T06:44:33","guid":{"rendered":"https:\/\/dbtut.com\/?p=8329"},"modified":"2019-01-21T06:44:33","modified_gmt":"2019-01-21T06:44:33","slug":"how-to-encrypt-sql-server-stored-procedures-views-and-functions","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/","title":{"rendered":"How To Encrypt SQL Server Stored Procedures, Views and Functions"},"content":{"rendered":"

 <\/p>\n

Stored procedures, functions, and views are objects that each application uses at the database tier. In some cases, it may be necessary to encrypt the text parts of these objects for various reasons. Let’s look at how to encrypt these three types of objects through an example.<\/p>\n

In the AdventureWorks2014 database, we get the create script of the HumanResources.vEmployee view as follows.<\/p>\n

\"\"<\/p>\n

After the CREATE VIEW section, we add WITH ENCRYPTION as follows. I added Encrypted to the end of the view name so that the view’s name is different from the original.<\/p>\n

\"\"<\/p>\n

You may receive an error as follows when you run the script.<\/p>\n

Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 84<\/em><\/span><\/p>\n

Property cannot be added. Property ‘MS_Description’ already exists for ‘HumanResources.vEmployee’.<\/em><\/span><\/p>\n

I will not mention it because it is irrelevant to our subject. Even though it receives an error, you will see that the encrypted version of the view is created as follows.<\/p>\n

\"\"<\/p>\n

As I explained at the beginning of the article, when you try to get the script of this view, it will give you an error as follows because the content of the view is encrypted.<\/p>\n

Property TextHeader is not available for View ”. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)<\/em><\/span><\/p>\n

\"\"<\/p>\n

You can also perform this procedure for Stored Procedures and Functions. You must put the WITH ENCRYPTION statement between the CREATE and AS statements.<\/p>\n

Important Note:<\/strong> Before encrypting objects, I recommend you to make a copy, as you may need to update them later.<\/p>\n\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

  Stored procedures, functions, and views are objects that each application uses at the database tier. In some cases, it may be necessary to encrypt the text parts of these objects for various reasons. Let’s look at how to encrypt these three types of objects through an example. In the AdventureWorks2014 database, we get the …<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[1724,1723,1722],"class_list":["post-8329","post","type-post","status-publish","format-standard","","category-mssql","tag-encrypting-sql-server-objects","tag-encrypting-sql-server-stored-produceres","tag-encrypting-stored-procedures"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":328,"today_views":1},"yoast_head":"\nHow To Encrypt SQL Server Stored Procedures, Views and Functions - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Encrypt SQL Server Stored Procedures, Views and Functions\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Encrypt SQL Server Stored Procedures, Views and Functions - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Encrypt SQL Server Stored Procedures, Views and Functions\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-21T06:44:33+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\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\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Encrypt SQL Server Stored Procedures, Views and Functions\",\"datePublished\":\"2019-01-21T06:44:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/\"},\"wordCount\":283,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png\",\"keywords\":[\"Encrypting SQL Server Objects\",\"Encrypting SQL Server Stored Produceres\",\"Encrypting Stored Procedures\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/\",\"name\":\"How To Encrypt SQL Server Stored Procedures, Views and Functions - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png\",\"datePublished\":\"2019-01-21T06:44:33+00:00\",\"description\":\"How To Encrypt SQL Server Stored Procedures, Views and Functions\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png\",\"width\":1271,\"height\":459},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Encrypt SQL Server Stored Procedures, Views and Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Encrypt SQL Server Stored Procedures, Views and Functions - Database Tutorials","description":"How To Encrypt SQL Server Stored Procedures, Views and Functions","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:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/","og_locale":"en_US","og_type":"article","og_title":"How To Encrypt SQL Server Stored Procedures, Views and Functions - Database Tutorials","og_description":"How To Encrypt SQL Server Stored Procedures, Views and Functions","og_url":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/","og_site_name":"Database Tutorials","article_published_time":"2019-01-21T06:44:33+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Encrypt SQL Server Stored Procedures, Views and Functions","datePublished":"2019-01-21T06:44:33+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/"},"wordCount":283,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png","keywords":["Encrypting SQL Server Objects","Encrypting SQL Server Stored Produceres","Encrypting Stored Procedures"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/","url":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/","name":"How To Encrypt SQL Server Stored Procedures, Views and Functions - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png","datePublished":"2019-01-21T06:44:33+00:00","description":"How To Encrypt SQL Server Stored Procedures, Views and Functions","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/img_5c4568e1a0a73.png","width":1271,"height":459},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/21\/how-to-encrypt-sql-server-stored-procedures-views-and-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Encrypt SQL Server Stored Procedures, Views and Functions"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/8329","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=8329"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/8329\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=8329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=8329"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=8329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}