I'm trying to use SQL Server's role provider, but I'm not sure if I'm using it in the most efficient way. Instead of haivng just some roles for the site, and assigning the users to the roles, I am trying to write a program for several organizations to assign their member's to roles.

A little background: My site has individuals and companies creating accounts. An individual account would be for a standard employee, and a company account would be for a manager, like in HR or Financing. An individual can be employed by multiple companies, and obviously a company can have multiple employees, as well as multiple company accounts for that company.

So...

Since I'm trying to just use what SQL Server provides out of the box, I made it so when a role is created for an company it is created with a RoleName that's something like "123456|Administrator" where "123456" is the company's account ID. There's only about 8 different roles, but the way I have it, each company will have 8, and the rules for each role will be the same for each company.

It seems kind of redundant, but I couldn't think of any other way to be able to differentiate between the roles that an individual would have with one company and the ones he or she would have with another company. I tried adding a column for the company ID to the aspnet_UsersInRoles table, but I don't know an easy way to be able to access that ID.

Also, after I get the roles working correctly, I am going to start working on allowing the companies to create work groups for their employees to be assigned to. I thought a quick way to do that would be to just expand on what I was doing with the roles, and have actual roles named "123456|role|Administrator" and work groups named "123456|workgroup|Team A" in the aspnet_Roles table as well. Is that bad?

Thanks.