How to adapt WPF application according to user role in SQL Server?

Question

I am building a WPF application with C# and EDMX file as my data access layer.

I have two security roles in SQL server 2008, AppUsers and AppAdmins.

Yes, AppUsers members do not have permission to edit one table lets call it T, only can select data from it. However AppAdmins members can insert and update data in to this table T as well.

I am using try/catch block in C# to report errors in the status bar, but I want to turn off complete modules from my application according to the user role, instead of waiting the user to use my app and report error which is not technically an error and is SQL permission obstacle.

How can I get current user role?

Is there some API inside the EDMX file or Entity Framework files which I can use to get user's role?

Can I get it with LINQ command?

Or should I develop SQL string query and run it directly on to the database server?

Answer 1

Yes you should write a SQL Query to retrieve the role memberships. EF won't do this for you.

Something like:

select name RoleName, is_member(name) IsUserInRole
from sys.database_principals
where type = 'R'

or

select is_member('AppUsers') IsAppUser, is_member('AppAdmins') IsAppAdmin