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 or used for application.
We have so many ways to encrypt data, but here i am talkig about the code encryption. With this encryption security, Users can execute the stored procedures but can not view the code.
Let us check the how the Stored Procedures can be encrypted.
-- Creating demo objects
-- Creating table
IF (OBJECT_ID('UserMaster','U')> 0)
DROP TABLE UserMaster
CREATE TABLE UserMaster
-- Inserting demo records
INSERT INTO UserMaster
Now we will create two Stored Procedures with and without encryption.
-- Creating Stored Procedure without encryption
CREATE PROCEDURE GetUserDataWithoutEncrypt
SET NOCOUNT ON
-- Creating Stored Procedure with encryption
CREATE PROCEDURE GetUserDataWithEncrypt
SET NOCOUNT ON
Executing both above created stored procedures , and you ca see there as both giving same output while users run them.
Finally we will check the text of these both stored procedures objects.
-- Viewing text of Stored Procedure without Encrypted EXEC SP_HELPTEXT
-- Viewing text of Stored Procedure with EncryptedEXEC SP_HELPTEXT
Doing same thing for Encrypted Stored Procedure from UI and see what happen,
It is not showing text in sys.syscomments as well.
WHERE id = object_id('GetUserDataWithEncrypt')
Let me what you think for the stored procedure encryption and share your comment if you have used it.