This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Today, I worked on a service request that our customer needs to avoid that some users could retrieve some rows but depending on a specific value of customer application. In this situation, I suggested to use sp_set_session_context that allows to save some details in the session context and use it during the duration of the session.
The first thing that I need to do, is, for example, run the following TSQL command every time that I stablished a connection to the SQL Database - EXEC sp_set_session_context 'Allow', 1
The value 1 means that I will be able to see the data and 0 not.
Right now, I need to create the function using this code:
And I'm going to create a dummy table:
Finally, I'm going to apply this function to the security context:
For example, if I change the value to 0 using the following TSQL: EXEC sp_set_session_context 'Allow', 0 and I run the command select * from Protegido1 I don't have any results but if I changed to 1, for example, EXEC sp_set_session_context 'Allow', 1 I'm going to obtain all the values.
Enjoy!