April 12, 2010 at 12:07 am
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
April 12, 2010 at 1:01 am
-- 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.
April 12, 2010 at 1:07 pm
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