Decrypt sproc returning NULL to non DBO.

I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.

TIA, ChrisR

--If there is no master key, create one now

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
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.

alter 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*/I have made some changes to the scripts, but the outcome is the same. Everything needed is below, so all ideas are welcomed. Also, please make sure to use these scripts, not the last ones.

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*/

debugging stored procedure

Hi everyone,
For some reason our stored procedure is returning an error when
executed. As part of resolving where the point of failure occurs, I
have defined an OUT variable to return what stage within the stored
procedure execution has reached before failure:
CREATE PROCEDURE sp_generate_invoices @.id char(14), @.retval int = 0 OUT
AS
BEGIN
<code>
SET @.retvalue = 1
<more code>
SET @.retvalue = 2
...
END
RETURN 1
GO
--
My question is, if the stored procedure fails half way through (for
whatever reason for example an overflow error), is the @.retval value
prior to the failure actually returned when the procedure fails? Or is
the default value returned whenever failure occurs?
If not, I am guessing the best method to track where the point of
failure occurs is to include statements like:
insert flowcontrol (datestamp, position)
value (getdate(), '1')
... throughout the code.
Any insight into this most appreciated!
thanks,
peterSimplest way would be to use @.@.ERROR to trap for error conditions and using
PRINT statements to debug your flow.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<mag1kus@.yahoo.com> wrote in message
news:1141009017.876445.302420@.p10g2000cwp.googlegroups.com...
> Hi everyone,
> For some reason our stored procedure is returning an error when
> executed. As part of resolving where the point of failure occurs, I
> have defined an OUT variable to return what stage within the stored
> procedure execution has reached before failure:
> --
> CREATE PROCEDURE sp_generate_invoices @.id char(14), @.retval int = 0 OUT
> AS
> BEGIN
> <code>
> SET @.retvalue = 1
> <more code>
> SET @.retvalue = 2
> ...
> END
> RETURN 1
> GO
> --
> My question is, if the stored procedure fails half way through (for
> whatever reason for example an overflow error), is the @.retval value
> prior to the failure actually returned when the procedure fails? Or is
> the default value returned whenever failure occurs?
> If not, I am guessing the best method to track where the point of
> failure occurs is to include statements like:
> insert flowcontrol (datestamp, position)
> value (getdate(), '1')
> ... throughout the code.
> Any insight into this most appreciated!
> thanks,
> peter
>