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
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 ca see there as both giving same output while users run them.
EXEC GetUserDataWithoutEncrypt
EXEC GetUserDataWithEncrypt
GO
Finally we will check the text of these both stored procedures objects.
-- Viewing text of Stored Procedure without Encrypted
EXEC SP_HELPTEXT GetUserDataWithoutEncrypt
-- Viewing text of Stored Procedure with Encrypted
EXEC SP_HELPTEXT GetUserDataWithEncrypt
Doing same thing for Encrypted Stored Procedure from UI and see what happen,
It is not showing text in sys.syscomments as well.
SELECT
*
FROM sys.syscomments
WHERE id = object_id('GetUserDataWithEncrypt')
GO
Let me what you think for the stored procedure encryption and share your comment if you have used it.