I am writing an web employee portal where the employee is supposed to see (and edit) his personal data that is stored on SQL Server. Me as the administrator needs to have access to that tables without seeing the content of those specific columns. The only person to see and upload the content is the director of human resources beside the logged in user of the portal that should see exclusivly his own data
I was looking at Encryption with a masterkey password and a certificate, tied to a symmetric Encryption Key. This seems to be a good starting point, but I still don't see how to grant the user (active directory user with Windows Authentication) access to his data.