Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

ASP.NET Security for SQL Server


By:   |   Last Updated: 2007-03-13   |   Comments (3)   |   Related Tips: More > Security

Problem
In all of my ASP applications I need to have at least basic security, how can I do so in simple manner?  The reality is that in the majority of my applications I need membership, profiles, role management and personalization, are any applications available directly from Microsoft to handle this need?  If so, where can I find them?  Do they integrate with the .NET 2.0 framework?  If so, how do you set this up?  Where can I find more information?

Solution
With the .NET 2.0 framework, it ships with the ASP.NET SQL Server Setup Wizard.  This wizard will go through the process of setting up tables, views, stored procedures, roles, etc. in order to manage membership, profiles, role management and personalization in a standardized manner.  These objects can be installed on a SQL Server instance and database you specify during the installation.  This wizard can be found at - C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe.

Installation Process

Below are the steps to install the ASP.NET SQL Server Setup Wizard.  In order to initiate this process, double click on C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe which will start the wizard.

ID Description Screen Shot
1 Intro Screen - Overview of the application and installation process.

Press the 'Next' button to continue the process.

 

2 Setup Option - Select the 'Configure SQL Server for application services' to install the product.

Press the 'Next' button to continue the process.

 

3 SQL Server Instance - Select the SQL Server instance, authentication means and database. 

Press the 'Next' button to continue the process.

 

4 Settings Summary - Review the installation summary before installing the database objects.

Press the 'Next' button to continue the process.

 

 

5 Close the wizard - Press the 'Finish' button to end the process.

 

 

Installed Objects

ID Description Objects
1 Tables
  • aspnet_Applications
  • aspnet_Membership
  • aspnet_Paths
  • aspnet_PersonalizationAllUsers
  • aspnet_PersonalizationPerUser
  • aspnet_Profile aspnet_Roles
  • aspnet_SchemaVersions
  • aspnet_Users
  • aspnet_UsersInRoles
  • aspnet_WebEvent_Events
2 Views
  • vw_aspnet_Applications
  • vw_aspnet_MembershipUsers
  • vw_aspnet_Profiles
  • vw_aspnet_Roles
  • vw_aspnet_Users
  • vw_aspnet_UsersInRoles
  • vw_aspnet_WebPartState_Paths
  • vw_aspnet_WebPartState_Shared
  • vw_aspnet_WebPartState_User
3 Stored Procedures
  • aspnet_AnyDataInTables
  • aspnet_Applications_CreateApplication
  • aspnet_CheckSchemaVersion
  • aspnet_Membership_ChangePasswordQuestionAndAnswer
  • aspnet_Membership_CreateUser
  • aspnet_Membership_FindUsersByEmail
  • aspnet_Membership_FindUsersByName
  • aspnet_Membership_GetAllUsers
  • aspnet_Membership_GetNumberOfUsersOnline
  • aspnet_Membership_GetPassword
  • aspnet_Membership_GetPasswordWithFormat
  • aspnet_Membership_GetUserByEmail
  • aspnet_Membership_GetUserByName
  • aspnet_Membership_GetUserByUserId
  • aspnet_Membership_ResetPassword
  • aspnet_Membership_SetPassword
  • aspnet_Membership_UnlockUser
  • aspnet_Membership_UpdateUser
  • aspnet_Membership_UpdateUserInfo
  • aspnet_Paths_CreatePath
  • aspnet_Personalization_GetApplicationId
  • aspnet_PersonalizationAdministration_DeleteAllState
  • aspnet_PersonalizationAdministration_FindState
  • aspnet_PersonalizationAdministration_GetCountOfState
  • aspnet_PersonalizationAdministration_ResetSharedState
  • aspnet_PersonalizationAdministration_ResetUserState
  • aspnet_PersonalizationAllUsers_GetPageSettings
  • aspnet_PersonalizationAllUsers_ResetPageSettings
  • aspnet_PersonalizationAllUsers_SetPageSettings
  • aspnet_PersonalizationPerUser_GetPageSettings
  • aspnet_PersonalizationPerUser_ResetPageSettings
  • aspnet_PersonalizationPerUser_SetPageSettings
  • aspnet_Profile_DeleteInactiveProfiles
  • aspnet_Profile_DeleteProfiles
  • aspnet_Profile_GetNumberOfInactiveProfiles
  • aspnet_Profile_GetProfiles
  • aspnet_Profile_GetProperties
  • aspnet_Profile_SetProperties
  • aspnet_RegisterSchemaVersion
  • aspnet_Roles_CreateRole
  • aspnet_Roles_DeleteRole
  • aspnet_Roles_GetAllRoles
  • aspnet_Roles_RoleExists
  • aspnet_Setup_RemoveAllRoleMembers
  • aspnet_Setup_RestorePermissions
  • aspnet_UnRegisterSchemaVersion
  • aspnet_Users_CreateUser
  • aspnet_Users_DeleteUser
  • aspnet_UsersInRoles_AddUsersToRoles
  • aspnet_UsersInRoles_FindUsersInRole
  • aspnet_UsersInRoles_GetRolesForUser
  • aspnet_UsersInRoles_GetUsersInRoles
4 Database Roles
  • aspnet_Membership_BasicAccess
  • aspnet_Membership_FullAccess
  • aspnet_Membership_ReportingAccess
  • aspnet_Personalization_BasicAccess
  • aspnet_Personalization_FullAccess
  • aspnet_Personalization_ReportingAccess
  • aspnet_Profile_BasicAccess
  • aspnet_Profile_FullAccess
  • aspnet_Profile_ReportingAccess
  • aspnet_Roles_BasicAccess
  • aspnet_Roles_FullAccess
  • aspnet_Roles_ReportingAccess
  • aspnet_WebEvent_FullAccess

Next Steps

  • Check out the ASP.NET SQL Server Setup Wizard in a development environment and see how this application handles security relative to your needs and/or your current techniques.
  • If this application offers additional functionality, then consider integrating this application in your environment.
  • With all of the code in an unencrypted manner, you have the opportunity to customize the code as needed or see how techniques implemented by this solution can be customized and incorporated into your applications


Last Updated: 2007-03-13


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 17, 2012 - 12:40:49 PM - Pramod Lawate Back To Top

hay hi Nice to meet unless u in online basically i want to appriciate this site ...Pramod Lawate

 


Saturday, December 04, 2010 - 7:45:00 AM - Jeremy Kadlec Back To Top

Ahmet,

It appears as if the database you are trying to install into is read-only i.e. '...because the database is read-only.'. 

Remove this configuration and try again.

Thank you,
Jeremy Kadlec


Friday, December 03, 2010 - 6:21:10 PM - ahmet Back To Top

Hi I have this error while running Asp.net Sql server Setup wizard. Would you help me please?

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 3906 and the SqlException message is: Failed to update database "C:\USERS\OFFICE DEPOT\DOCUMENTS\VISUAL STUDIO 2008\WEBSITES\LICENCEONLINEMARKET_21_KASIM_\APP_DATA\WEB_MARKET.MDF" because the database is read-only.


Learn more about SQL Server tools