"With Encryption"
-
Does anyone have a stored procedure will cycle through other stored procedures, views, and functions and apply "With Encryption" to them? Thanks.
--Lenard
-
This can be done, by reading the syscomments table, however,
I had to do something like this before. I found it easier to script out all the SP each into it's own file and use "Search&Replace" from Funduc software. Good luck...
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
tricky but maybe ...
-- ALZDBA dd 18/03/2004
-- convert sp to with encryption
set nocount on
DECLARE @SearchString varchar(255)
Declare @WhichProcs varchar(255)
Declare @sp_owner varchar(255)
select @WhichProcs = '%spc%', @sp_owner = '%'
DECLARE @spcount int,
@spLoopCount int,
@proc_name varchar(200),
@spTextTotal int,
@spTextCount int
CREATE TABLE #tStoredProcs
(
num_ID INTEGER IDENTITY(1,1),
PROCEDURE_QUALIFIER varchar(200),
PROCEDURE_OWNER varchar(200),
PROCEDURE_NAME varchar(200),
NUM_INPUT_PARAMS int,
NUM_OUTPUT_PARAMS int,
NUM_RESULT_SETS int,
REMARKS varchar(255),
PROCEDURE_TYPE varchar(10)
)
CREATE TABLE #tStoredProcText
(
num_ID INTEGER IDENTITY(1,1)
,spText varchar(4000)
)
CREATE TABLE #tStoredProcTextWORK
(
num_ID INTEGER not null
,num_ID_seq INTEGER IDENTITY(1,1)
,spText varchar(4000)
)
INSERT INTO #tStoredProcs
EXECUTE sp_stored_procedures @sp_name = @WhichProcs, @sp_owner = @sp_owner
SET @spcount = @@ROWCOUNT
SET @spLoopCount = 0
WHILE @spLoopCount <= @spcount
BEGIN
SET @spLoopCount = @spLoopCount + 1
SET @proc_name = (SELECT PROCEDURE_OWNER + '.' + PROCEDURE_NAME FROM #tStoredProcs WHERE num_ID = @spLoopCount)
SET @proc_name = SUBSTRING(@proc_name,1,PATINDEX('%;%',@proc_name)-1)
-- print @proc_name
if @proc_name is not NULL
BEGIN
INSERT INTO #tStoredProcText
EXEC sp_helptext @proc_name
INSERT INTO #tStoredProcText values ('-- MyEndProc-string')
END
END
Update #tStoredProcText
set spText = replace(spText,'create proc ','ALTER procedure ')
where spText like 'create proc %'
Update #tStoredProcText
set spText = replace(spText,'create procedure ','ALTER procedure ')
where spText like 'create procedure %'
-- search and transform " as "- part to " with encryption as "
declare @Num_Id_ptr as integer
declare @spText varchar(7000)
declare @blnSpOK smallint
declare @pos integer
-- initialize
Select @blnSpOK = 0
declare csrSpTxt cursor for
Select *
FROM #tStoredProcText
where spText like '%as%'
or spText = '-- MyEndProc-string'
for read only
Open csrSpTxt
FETCH NEXT FROM csrSpTxt
INTO @Num_Id_ptr, @spText
WHILE @@FETCH_STATUS = 0
BEGIN
if @spText = '-- MyEndProc-string'
begin
Select @blnSpOK = 0, @pos = 0
end
else
begin
set @pos = 0
if @blnSpOK = 0
begin
while @blnSpOK = 0 and @pos >= 0 and @pos < datalength(@spText)
begin
Select @pos = CHARINDEX ( 'as',@spText, @pos + 1)
if @pos = 0
begin
set @pos = -1
end
else
begin
if @pos = 1
begin
if datalength(@spText) < 3 or substring(@spText,3,1) in ( ' ', char(13), char(9) )
begin
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, ' WITH ENCRYPTION ')
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, @spText )
Set @blnSpOK = 1
end
end
else
begin
if substring(@spText,@pos - 1 ,1) in( ' ', char(9) )
begin
if datalength(@spText) < @pos + 2
begin
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, substring(@spText,1,@pos - 1) )
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, ' WITH ENCRYPTION ' )
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, substring(@spText ,@pos , datalength(@spText) - @pos ))
Set @blnSpOK = 1
end
else
begin
--if substring(@spText,@pos + 2 ,1) = ' ' or substring(@spText,@pos + 2 ,1) = char(13) or substring(@spText,@pos + 2 ,1) = char(9)
if substring(@spText,@pos + 2 ,1) in( ' ', char(13), char(9) )
begin
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, substring(@spText,1,@pos - 1) )
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, ' WITH ENCRYPTION ' )
insert into #tStoredProcTextWORK (num_ID ,spText)
values (@Num_Id_ptr, substring(@spText ,@pos , datalength(@spText) - @pos ))
Set @blnSpOK = 1
end
end
end
end
end
end
end
end
-- read next
FETCH NEXT FROM csrSpTxt
INTO @Num_Id_ptr, @spText
END
CLOSE csrSpTxt
DEALLOCATE csrSpTxt
select T1.num_ID, T2.num_ID_seq, isnull(T2.spText, T1.spText) as spText
from #tStoredProcText T1
left join #tStoredProcTextWORK T2
on T1.num_ID = T2.num_ID
order by T1.num_ID, isnull(T2.num_ID_seq,0)
DROP TABLE #tStoredProcs
DROP TABLE #tStoredProcText
DROP TABLE #tStoredProcTextWORK
TEST IT TEST IT TEST IT
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the code ALZDBA. I will give it a whirl shortly.
--Lenard
-
I've updated the T-sql text in my previous reply ! (also included tabulation-char) .
Watch out for UDF's
. sp_stored_procedures does not filter them.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply