Home » none » Unique SQL Login w/ SqlMembership Provider

Unique SQL Login w/ SqlMembership Provider

Yesterday, we looked at how to setup the web.config file and modify the business logic layer to make use of Profile properties to give us unique SQL logins when we use the SqlMembership provider for forms authentication.  Today, we are going to show you the code you will need to associate the profile properties with the user.

Most people think of profile properties as something the user associates with his own login.  What we need to do in this case is have the admin associate the properties with the login.  Fortunately, there are APIs in ASP.NET that allow us to do just that.  So, to start, I’m going to show you the update code:

Update the user information

public void UpdateLogin(string LoginName, 
    string SQLUserName,string SQLPassword) 
{ 
    ProfileCommon Profile = 
      (ProfileCommon)ProfileCommon.Create(LoginName,true); 
    Profile.SQLUserName = SQLUserName; 
    Profile.SQLPassword = SQLPassword; 
    Profile.Save(); 
}

Note: the Profile classes are part of the System.Web.Profile namespace.  You’ll want to include that at the top of any source file that is using it.

The first line of this code creates a profile object that is associated with the login name.  The second and third line assign the username and password.  The forth line saves that all back into the Membership database.

You may wonder where the SQLUserName and SQLPassword properties came from.  The ones that are hanging off the Profile object.  They are not part of the ProfileCommon class.  They automatically get created simply by declaring them in the web.config file.

Displaying the list of users

As I mentioned yesterday, not every user needs to have this information associated with them.  So, we need to be able to filter out the users.  In my implementation, I decided to make the SQL login association role based.  So, my code that files my DataTable that I can bind to a grid (or whatever) retrieves all of the users in the system and then adds rows to the DataTable if the user is part of that row:

public DataSetContentEditors.LoginsDataTable GetLogins() 
{ 
    ProfileCommon Profile = new ProfileCommon(); 
    DataSetContentEditors.LoginsDataTable logins = 
         new DataSetContentEditors.LoginsDataTable(); 
    MembershipUserCollection muc = Membership.GetAllUsers(); 
    foreach (MembershipUser mu in muc) 
    { 
        if (Roles.IsUserInRole(mu.UserName, "Editor")) 
        { 
            logins.AddLoginsRow(mu.UserName, 
               Profile.GetProfile(mu.UserName).SQLUserName, 
               Profile.GetProfile(mu.UserName).SQLPassword); 
        } 
    } 
    return logins; 
}

Again, I’m using the ProfileCommon class.  This time to retrieve the username and password values from the profile database.  Just like we did yesterday when we got the connection string.

The Membership.GetAllUsers() call is retrieving all of the users in the system.  My foreach statement iterates through each of those users, checks to see if they are part of the “Editor” role and if they are, add the login name, the sql username and the sql password to the LogingDataTable.

Is it Secure?

No, not out of the box.  You can visit this article at Microsoft to get more information on how to implement profile properties at various security levels:  Security Profile Properties

More Information

For links to more information see yesterday’s article.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • Computed Columns Using LINQ to SQL ClassesComputed Columns Using LINQ to SQL Classes Last week we looked at the extension points Microsoft has wired into the LINQ to SQL classes and how they can be used to achieve some of the capabilities of the Business Logic Layer (BLL) […]
  • SQL Express Mixed Mode AuthenticationSQL Express Mixed Mode Authentication So, I'm getting ready to do some data binding demos and I realized that when I installed Visual Studio, I forgot to install SQL Server Express first so that I could control the […]
  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to […]
  • SQL For Developers – 9 Reasons to botherSQL For Developers – 9 Reasons to bother If you are a developer like I am, you've probably gotten by with pretty simple SQL for quite a while. In fact, my experience has been that developers don't get much past SELECT, INSERT, […]
  • Is LINQ Multi Layered?Is LINQ Multi Layered? Several weeks ago, we discussed the point of Multi-Layered Architectures.  We discussed what a multi-layered architecture looks like, and the problems it solves. Today, what I want to […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.