asp.net and membership SQL in aspnetdb database: question

  • Ok if you have never heard of aspnetdb, then you cant help me.

    BUT this is my question...

    Ok assume I have these roles form authentication roles

    AA, BB, CC, DD

    And I realise that a user/member can have mutlple roles.

    HOW Can I do a select SQL query that returns the result set like this (SQL 2005)

    SELECT Roles FROM <tbl>

    WHERE UserID = <GUID>

    AND Roles IN ('AA','CC')

    Result set..vertical format

    AA

    CC

    Q: What would the TSQL be to do this in aspnet_ tables ?

    Thanks

  • -- Retrieve by user name.

    Select RoleName

    From dbo.aspnet_Users As U

    Inner Join dbo.aspnet_UsersInRoles As Ur On Ur.UserId = U.UserId

    Inner Join dbo.aspnet_Roles As R On R.RoleId = Ur.RoleId

    Where (RoleName In ('AA', 'CC')) And (U.UserName = 'username');

    -- Retrieve by user id.

    Select RoleName

    From dbo.aspnet_UsersInRoles As Ur

    Inner Join dbo.aspnet_Roles As R On R.RoleId = Ur.RoleId

    Where (RoleName In ('AA', 'CC')) And (Ur.UserId = 'user guid');

    Why do you want to limit the roles that you get back?

    You might want to consider using the built in stored procedures.

    To find all roles for a user:

    aspnet_UsersInRoles_GetRolesForUser (ApplicationName, UserName)

    To find all users in a role:

    aspnet_UsersInRoles_GetUsersInRoles (ApplicationName, RoleName)

    To test if a user is in a specific role:

    aspnet_UsersInRoles_IsUserInRole (ApplicationName, UserName, RoleName)

    If you are working through the application look at the RoleProvider class and the FindUsersInRole, GetAllRoles, GetRolesForUsers, and GetUsersInRole methods.

    Here's a bunch more stuff on working with Membership services.

  • Thanks..just what I need.

    There is a method to my madness...:-)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply