Sql Server Stored Procedure


SQL SERVER – Stored Procedure WITH ENCRYPTION


One of the ways to keep stored procedure, function or view schema hidden is using the WITH ENCRYPTION statements. In this way, access to objects' DDL script can be locked, so that unauthorized users cannot see them:


create database procedurepractical
use procedurepractical
create table employee
( id int primary key,
  Name nvarchar(20),
  Class nvarchar(20),
  Dept nvarchar(20)
)
insert into employee values(1,'Sam','B.tech','IT')
insert into employee values(2,'Rahul','B.tech','CS')
insert into employee values(3,'Mahesh','B.tech','Mechnical')
insert into employee values(4,'Ethen Hunt','B.tech','EC')
insert into employee values(5,'Ram','B.tech','ME')
insert into employee values(6,'Jhon','B.tech','IT')

select * from employee

Create proc employeeproc
As
 begin
    select Name,Class from employee
 end

--------------------------------------------------------------------------------       --------------------------------------------------------------------------------
TO create procedure we can type CREATE PROC or CREATE PROCEDURE it depends on you
To execute store procedure just type the name or Execute ProcuderName or EXEC procedure name



select * from employee
 
create proc getemployee_by_parameter
@Class nvarchar(20),
@Dept nvarchar(20)
as
  begin
       Select Name,Class,Dept from employee where Class= @Class and Dept=@Dept

  End


   select * from employee
   --To execute we two way
  getemployee_by_parameter 'B.tech','IT'
  getemployee_by_parameter  @Class='B.tech',@Dept='ME'

 IF i want to view the text or definition of the store procedure to use following queries
 Right clik on store procedure go for script store procedure as--->Create to-->New query Window
 Two above procedure are the UDP
 Another way to view the

 sp_helptext getemployee_by_parameter(store_procedure name)

 sp_helptext getemployee_by_parameter

To change store procedure implementation

  sp_helptext employeeproc

  Create proc employeeproc
As

 begin
    select Name,Class from employee order by id
 end


 After execute above procedure we got error -There is already an object named 'employeeproc' in the database.
 
Now we would not be change store_procedure name just a way to do that
  TO change the definition of the sp we use ALTER


  Alter proc employeeproc
As

 begin
    select Name,Class from employee order by id
 end

 --TO delete procedure (Drop procedure procedureName)

 Drop proc employeeproc

 To encrypt the store procedure---> we use WITH ENCRYPTION

 sp_helptext getemployee_by_parameter


 Alter proc getemployee_by_parameter

@Class nvarchar(20),
@Dept nvarchar(20)
With encryption
as
 begin
       Select Name,Class,Dept from employee where Class= @Class and Dept=@Dept
  End
 

  Once we perform Stored Procedure WITH ENCRYPTION we can't see            the SP text


Previous
Next Post »