locked
Can EF 6 read roles in a SQL Server database and if so, how is it done? RRS feed

  • Question

  • We're working on a new desktop app. There are certain areas of the app which are supposed to be enabled or disabled based upon the permissions a user has. The permissions are associated with database roles specified in the SQL Server database, whether the user has membership in the role, etc. I've got years of experience working with SQL Server, Entity Framework and LINQ, but I've never used EF to hit against the roles, either database roles or application roles, in a SQL Server database. I'm wondering if this is even possible with EF? I've searched and found articles like this one Entity Framework and SQL Server user roles: how to enable EF to generate role-aware queries? But that poster was asking about queries, whereas I'm just interested in seeing if the logged in user is in a certain role or not. Or get all the roles the user is a member of. And that poster was using an older version of EF. So let me ask, can EF 6.1.3 now access the database roles for a database? If it can, how is that done?

    And if it can't, then is there some other way of doing that? Or should be use some other method to document what role(s) a user is in?

     

    Rod

    Tuesday, March 21, 2017 7:58 PM

Answers

  • Hi Rod at Work,

    It seems that there are not raw sql statement to get the roles for the database. so that ef could not retrieve related roles for the database. you could use SMO to achieve it.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Rod at Work Thursday, March 23, 2017 2:19 PM
    Thursday, March 23, 2017 3:14 AM

All replies

  • Hi Rod at Work,

    It seems that we could use Raw SQL Queries to achieve it. like this:

    #Roels

     public class Roles
        {
            public string Name { get; set; }
            public string type_desc { get; set; }
        }

    #Usage:

    string sql = @"SELECT
                                    p.name AS [Name] ,r.type_desc
                                    FROM
                                    sys.server_principals r
                                    INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
                                    INNER JOIN sys.server_principals p ON
                                    p.principal_id = m.member_principal_id
                                    WHERE r.type = 'R' and r.name = N'sysadmin'";
                    var result = db.Database.SqlQuery<Roles>(sql);

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 22, 2017 5:15 AM
  • I've been reading up on SMO might be better? I don't know because I've never used SMO before. I was thinking I could write a library assembly that reads through the SMO objects, getting the roles that the user is in. Of course it might also have to parse through AD as well, since the user may be in a AD Group, that's in the role. Putting people into a AD group is more likely what we'd do here, rather than put add them to the SQL Server role individually.

    Rod

    Wednesday, March 22, 2017 5:52 PM
  • Cole, I've been experimenting with the SQL SELECT that you listed. I'm not familiar with sys.server_principals nor sys.server_role_members. Looks to me as though they are system views. I tried running the code exactly as you gave it and all it listed was the SQL Server sa account. So next I ran SELECT * against sys.server_principals and sys.server_role_members individually, but that didn't show the roles that are defined for the database. In poking around a little more I came to the conclusion that they're listing roles for the server, not the database. Am I correct about that?

    Rod

    Wednesday, March 22, 2017 8:24 PM
  • Hi Rod at Work,

    It seems that there are not raw sql statement to get the roles for the database. so that ef could not retrieve related roles for the database. you could use SMO to achieve it.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Rod at Work Thursday, March 23, 2017 2:19 PM
    Thursday, March 23, 2017 3:14 AM