You are here:Home » tsql » Stored Procedure Encryption - SQL Server

Stored Procedure Encryption - SQL Server

Because of some security policies we need to require our code to be safe from Users who are going to use SQL Server database and objects of them and some outside threat. We have different different users to access the database objects are used for application.

We have so many ways to encrypt data, but here I am talking about the code encryption. With this encryption security, Users can execute the stored procedures but cannot view the code. Let us check the how the Stored Procedures can be encrypted. 
-- Creating demo objects

USE demo
GO

-- Creating table
IF (OBJECT_ID('UserMaster','U')> 0)
DROP TABLE UserMaster
GO

CREATE TABLE UserMaster
(
UserId INT,
UserName VARCHAR(100),
UserPwd NVARCHAR(100)
)

GO

-- Inserting demo records
INSERT INTO UserMaster
SELECT '1','User1','pwd1'
UNION ALL
SELECT '2','User2','pwd2'
UNION ALL
SELECT '3','User3','pwd3'

GO
Now we will create two Stored Procedures with and without encryption.
-- Creating Stored Procedure without encryption
CREATE PROCEDURE GetUserDataWithoutEncrypt
AS
BEGIN
SET NOCOUNT ON 

SELECT 
UserId,
UserName,
UserPwd
FROM UserMaster

END
GO

-- Creating Stored Procedure with encryption
CREATE PROCEDURE GetUserDataWithEncrypt
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON 

SELECT 
UserId,
UserName,
UserPwd
FROM UserMaster

END
GO
Executing both above created stored procedures, and you can see there as both giving the same output while Users run them,

EXEC GetUserDataWithoutEncrypt
EXEC GetUserDataWithEncrypt
GO



Finally, we will check the text of these both stored procedures.

-- Viewing text of Stored Procedure without Encrypted
EXEC SP_HELPTEXT GetUserDataWithoutEncrypt


-- Viewing text of Stored Procedure with Encrypted
EXEC SP_HELPTEXT GetUserDataWithEncrypt


Doing the same thing for Encrypted Stored Procedure from UI and see what happens.



Let me what you think for the stored procedure encryption and share your comment the if you have used it.