I'm trying to create a symmetric key in SQL Server 2005 and restrict it to just the sa and application user accounts. All other accounts should get an error when trying to open it.
Here's how I'm creating it. I changed the names for the purposes of this question.
CREATE CERTIFICATE My_Certificate
WITH SUBJECT = 'My Cert Subject';
GO
CREATE SYMMETRIC KEY My_Key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE My_Certificate;
GO
At this point, the key can be opened by my app user even though I haven't given it permission. If I DENY the [public] group, I can't open the key even after I later GRANT it directly to the app user. I don't want to DENY each user individually because I don't know what users exist in Production. I also want any new users that get created to not have access to this key, either.
The DENY and GRANT lines I'm using are:
DENY VIEW DEFINITION ON SYMMETRIC KEY :: My_Key TO [public]
GRANT VIEW DEFINITION ON SYMMETRIC KEY :: My_Key TO [AppUser]