Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Hi Chris
I could not get you sample to produce the effect you say, but then I changed
the procedure to open/close the keys. You should have the keys open for as
short a time as possible
CREATE PROCEDURE getDecryptedIDNumber
WITH EXEC AS OWBER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID Number],
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted ID
Number]
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
GO
If this does not work you may want to post in
microsoft.public.sqlserver.security
A good source for encryption information is
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what you
but signs the procedure instead.
John
"ChrisR" wrote:

> If someone would try out my script below I'd really appreciate it. Whenever
> I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
> fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
> in the execution. All ideas are welcomed.
> TIA, ChrisR
>
> USE [AdventureWorks];
> GO
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> 'vato'
> GO
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> with exec as owner
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
> /*works for me, shows the decrypted data*/
> exec getDecryptedIDNumber
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
> GRANT IMPERSONATE ON USER:: dbo TO test;
> GO
> /*Now, open up a "file/new/DB Engine Query" and login with the test login*/
> exec as user = 'dbo'
> exec getDecryptedIDNumber
> /*This returns NULL values where it should show the decrypted data*/
>
>
|||Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you[vbcol=seagreen]
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
Whenever[vbcol=seagreen]
the[vbcol=seagreen]
'dbo'"[vbcol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
login*/[vbcol=seagreen]