Create a view in database to fetch username and password of all users having a particular roie (Forms Authentication)

Question

Hi Guys,

Background
==========

I am using ASP.NET Forms Authentication for my application.
I have made my Password Format "Clear", so no problems with the password encryption.

Requirement
===========

I need to create a view(in SQL server) to display all Administrators in my System.
ie aspnet_Roles.LoweredRoleName='administrator'
The fields needed are UserName and Password

Can you guys give point out the most optimal way to do so?

Regards,
Naveen Jose

Answer 1

Something like this :

SELECT     dbo.aspnet_Membership.Password, dbo.aspnet_Users.UserName
FROM         dbo.aspnet_Membership INNER JOIN
                  dbo.aspnet_Users ON dbo.aspnet_Membership.UserId =     dbo.aspnet_Users.UserId INNER JOIN
                  dbo.aspnet_UsersInRoles ON dbo.aspnet_Users.UserId = dbo.aspnet_UsersInRoles.UserId INNER JOIN
                  dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
WHERE     (dbo.aspnet_Roles.LoweredRoleName = 'administrator')