Hide Field contents based on an AD group of user

[UHNSTrust]

Hi,

Is there any way of hiding a field based on an AD group of the user running the t-SQL/SP. I was think of a function that you could call that checked the AD group and then returned TRUE if they belonged in the required AD group. Then you could substitute a fields contents with something else (i.e. 'ANON').

We currently have hospital data that we need to anonymise/pseudonymise at the query level if a person is not in an AD group (because they have not received training on a hospital system).

Any help appreciated.

Jonathan

 

[philhege]

PII/HIPAA best practices suggest that such data should be encrypted at rest.

You could create a CLR stored procedure based on something like this script, and evaluate those results.

-----------
With business clients like mine, you'd be better off herding cats.

 

[Olaf Doschke]

That would typically be done on the UI level of an application, not in SQL Server. In most cases you'd rather do another query for less privileged users.

HTH

Bye, Olaf.

 

[LarrySteele]

My initial thought would be to have the AD role passed as an input parameter to the proc and use it to mask the data (return blanks, encrypted values, etc.) for the applicable fields. I haven't worked with SQL Server for a long time, but if memory serves, you could even use it as a conditional statement to run a specific query like Olaf suggested.

HTH,
Larry