Create Encrypted View and Stored Procedure in SQL Server

This blog is to demonstrate .How to create Encrypted View and Stored Procedure in SQL to keep them safe(unchanged).

Create encrypted Stored Procedure and View

Some time we don’t want someone else to make changes (alter definition) in Views or Stored Procedure created by us in SQL Server. In this blog I am going to show you how to create encrypted View or Stored Procedure in SQL Server, so that no one can alter it.Not only this but also it can hide its definition.

Create Encrypted View (Encrypt a View)We can create an encrypted view using with keyword

E.g

CREATE VIEW MY_ENCRYPTED_VIEW WITH ENCRYPTION
AS
SELECT TOP(10) * FROM test1 – -this is your sql query.

Here in this example I have created MY_ENCRYPTED_VIEW with encryption.

Now if you will see it in Object Explorer window it will look like:

Now encrypted view has been created if you will try to alter this, it will show you below error message:

 

We can also alter existing view and make it encrypted using :

ALTER VIEW MY_ENCRYPTED_VIEW_2 WITH ENCRYPTION
AS
SELECT TOP(10) * FROM test1 –- this is your sql query.

Create Encrypted Stored Procedure (Encrypt a Stored Procedure)

It is same as creating an Encrypted view:

E.g

We can create encrypted stored Procedure using query:

CREATE PROC MY_ENCRYPTED_SP WITH ENCRYPTION
AS
SELECT TOP(10) * FROM test1

Encrypted stored procedure will displayed like :

 

It’s also possible to alter existing Stored Procedure for encryption.

You can achieve the same using:

ALTER PROC MY_ENCRYPTED_SP_1 WITH ENCRYPTION
AS
SELECT TOP(10) * FROM test1

If you will try to modify Encrypted SP , will show the same error as we saw in case of Encrypted View