SQL Server Database Roles through ASP?

[raindogs]

Hi all,

I'm a bit new to ASP so I have what may be a foolish question. The ASP role management options that come through "roleManager" are great but, as far as I can tell, they do everything through the "aspnet_*" tables in the DB. What I would really like to do is access the Database roles I've created directly in SQL Server 2005 via my ASP application. Does anyone know a way to do this? I've searched around a bit online and come up with nothing, but a link to a good tutorial would be much appreciated if such a thing exists.

Thanks,
Alex

 

[jbenson001]

Look into the system tables for the DB. There is probably a table or view that holds the role information.

 

[elhaix]

Take a look through the Roles/Membership API:

http://www.google.com/search?source...DGUS,DGUS:2006-12,DGUS:en&q=asp.net+roles+api

You can also specify your own datastore for that information as well:

=============================================================

By default, form-based authentication in an ASP.Net 2.0 application is done using an application-specific database (using the built-in SQL Server 2005 Express provider), located at:
• <website directory>\App_Data
o ASPNETDB.MDF
o aspnetdb_log.ldf

These files are directly mapped within the ASP.Net application, and do not show up in any SQL Server admin tool.

For the sake of centralizing our data, we may wish to contain all user-related information in the same database as our application data. We may also wish to use a SQL Server 2000 or 2005 database.

For some applications we would want to store all related data in the same database:




We could use two separate databases for the same application. The advantage to this is that we can use the same user profiles for multiple applications – creating a separate user’s database for use with multiple sites.



Setting It All Up

To setup a central user store for .Net 2.0, use the following tool to create profiles etc.
Launch C:\Windows\Microsoft.Net\Framework\<version>\aspnet_regsql.exe

To specify your own database for the user’s store, configure the Web.config as follows:

<configuration>
	<appSettings>
		<!-- Data store for TCCA -->
		<!-- add key="dbConnection" value="server=192.168.1.45;Database=ccdb0x1;User ID=WebAppUser; Password=webappdbuserpw1234;Trusted_Connection=False"/ -->
    <add key="dbConnection" value="server=66.240.xxx.xxx;Database=APPLICATIONDATABSE;User ID=WebDBID; Password=WebDBPW;Trusted_Connection=False"/>
  </appSettings>
      <connectionStrings>
		<!-- Global user store for all Websites -->
		<add name="UserMembershipSQLServer" connectionString="Server=TSA-LA-xxx-xx; Database=userstorexxx; User Id=WebxxxxxUser; password=webapppxxxxxx" providerName="System.Data.SqlClient"/>
		<!-- 
      <add name="MyLocalSQLServer"
           connectionString="Initial Catalog=aspnetdb;data source=localhost;Integrated Security=SSPI;" />
     -->
      </connectionStrings>

	<system.web>
		<!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.
        -->
		<roleManager enabled="true" defaultProvider="CustomizedRoleProvider">
			<providers>
				<add connectionStringName="UserMembershipSQLServer" name="CustomizedRoleProvider" type="System.Web.Security.SqlRoleProvider"/>
			</providers>
		</roleManager>


<appSettings />

For a one or two-tiered database approach, this contains the application database connection information. This data could be contained in the <connectionStrings /> block, however, for this application we are using a DAL framework that uses the appSettings variables.

<connectionStrings />
This contains the SQL Server 2000, 2005, or Express database where the user store is to be generated. Using the aspnet_regsql.exe tool will automatically create .Net’s required tables for the user membership functionalities.

<roleManager />
We want to enable roles, as to grant access to different user roles (these can be seen as groups)
For example:
• Administrator
• Manager
• UnvalidatedUser
• ValidatedUser

These are especially useful because we no longer need to check specific user information, only use the Membership API’s Page context to retrieve the current user’s role.

The <providers /> tag specifies which datastore to use.

=============================================================


Hope that helps!