We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE.
So I planned to create a stored procedure as shown below
CREATE function [dbo].[SQLEncrypt](@str varchar(max)) returns varchar(max) as begin declare @test nvarchar(max) set @test='' set @test = convert(varchar(MAX),DECRYPTBYPASSPHRASE ('password',@str)) return @test END GO
I can use this stored procedure as shown below to decrypt a column
----usgae--- select master.dbo.SQLEncrypt(columnname) from tablename-- -----------------
Now I am planning to store this password somewhere globally in SQL server(Don't know if it is possible or not) and then use it in stored procedure. IS it possible to do by anyway? Also can I hide the password from users?