There is a great article on SQLServerCentral on an Extended Stored Procedure http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp and this XP will undoubtedly perform better than my Function. However I had a need to encrypt a column in a shared hosted environment where I was not allowed to install XPs. Just copy script and paste into Query Analyzer
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_PwdCrypt]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_PwdCrypt]
GO
CREATE FUNCTION dbo.fn_PwdCrypt(@ptSource varchar(255), @ptPassword varchar(255))
RETURNS varbinary(64)
BEGIN
DECLARE @tdest varchar(255),
@lteller int,
@lPasswTeller int,
@cnt int,
@len int,
@tFinal varbinary(64)
SELECT @tFinal = Convert(varbinary(64),'')
SELECT @len = Len(@ptSource)
SELECT @lteller = 1
SELECT @tdest = @ptSource
SELECT @lPasswTeller = 0
WHILE @lteller <= @len
BEGIN
SELECT @lPasswTeller = @lPasswTeller - 1
If @lPasswTeller < 1
SELECT @lPasswTeller = Len(@ptPassword)
SELECT @tfinal = @tfinal + Convert(varbinary(64),Stuff(Substring(@tdest, @lteller, 1),1,Len( Char(Ascii(Substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ) )
SELECT @lteller = @lteller + 1
END
RETURN @tfinal
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_PwdDeCrypt]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_PwdDeCrypt]
GO
CREATE FUNCTION dbo.fn_PwdDeCrypt(@ptSource varchar(255), @ptPassword varchar(255))
RETURNS varchar(255)
BEGIN
DECLARE @tdest varchar(255),
@lteller int,
@lPasswTeller int,
@cnt int,
@len int,
@tFinal varchar(255)
SELECT @tFinal = ''
SELECT @len = Len(@ptSource)
SELECT @lteller = 1
SELECT @tdest = @ptSource
SELECT @lPasswTeller = 0
WHILE @lteller <= @len
BEGIN
SELECT @lPasswTeller = @lPasswTeller - 1
If @lPasswTeller < 1
SELECT @lPasswTeller = Len(@ptPassword)
SELECT @tfinal = @tfinal + Left(Stuff(substring(@tdest, @lteller, 1),1,Len( Char(Ascii(substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ), 1)
SELECT @lteller = @lteller+1
END
RETURN @tfinal
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Test it here
create table #temp
( pwd varbinary(64))
insert into #temp (pwd)
select dbo.fn_PwdCrypt ('MyPassword','secret')
select pwd from #temp
select dbo.fn_PwdDeCrypt (pwd,'secret') from #temp
drop table #temp 
2007-10-02 (first published: 2002-06-20)
15,451 reads
I have always read that 8060 bytes is the maximum size. This is stated over and over again in Books Online, the MS site and numerous other sites, including this one. However a post in our forum recently questioned this. I decided to verify the problem and do a little research.
2007-10-02 (first published: 2002-02-07)
37,402 reads
The User_Defined_Functions.exe file contains the User-Defined Functions white paper. The User-Defined functions white paper outlines the characteristics of the new user-defined function (UDF) feature that is introduced in Microsoft SQL Server 2000. The white paper also summarizes how you can create your own Transact-SQL functions to extend the programmability of Transact-SQL.
2001-08-31
2,233 reads
The following stored procedure will demonstrate the use of cursor metadata. Using cursor metadata we can get information about an SQL statement and use that information to dynamically generate other code such as HTML or other stored procedures.
2001-07-27
5,203 reads
ne of the issues you face when building Web applications is handling the errors you encounter when interacting with a back-end database. I was recently working with someone to create a new Web site with SQL Server™, ActiveX® Data Objects (ADO), and ASP. Lots of little things came up that I thought were worth sharing with MIND readers, so I'll focus this column on what I learned from this experience and the solutions to many of the problems I faced.
2001-07-13
1,579 reads