Adding Roles and Membership to an SQL Server Database

January 8 2011 08:57 / by: Bryan Didas / Filed in:ASP.NET

The following steps describe adding roles and membership to an SQL Server database that stores user information and allows for authentication within ASP.NET applications. This happens to be one the many little tasks that I seem to do once every 3-6 months that I need to stop and think; where is the setup wizard located? The information or instructions exist in many places on the net, do a Google search on the above title or follow the steps below. I have put this post together so that in the future I hopefully wont have to stop and think so long.

Step –1: Navigate to the folder shown below in Fig -1a, this is the path on a typical WinXP machine. Double click aspnet_regsql.exe to start the wizard.

Figure - 1a

For a Windows 7 system the path is slightly different, Figure – 1b. Also depending on the frame works installed you can find the aspnet_regsql.exe in the V4.0 framework folder.

Win7 RegSQL 
Figure – 1b

As you can see on the welcome screen of the setup wizard, you can also configure roles and membership from a command line. The are additional features that can be setup which need to be configured from the command prompt and will not be covered in the post.

Figure - 2

Step –2, Setup Option: Select either configure SQL server for application services, which is the default or the option to remove application services from an existing database. Figure –3

Figure - 3

Step –3: This is the most complicated step in the series. You will need the server name and instance name. You can use either Widows authentication or SQL Server authentication. Once you enter and server information and the authentication credentials a list of available databases will be shown in the drop down list. Select the appropriate database and then select next. Figure - 4

Select Database 
Figure - 4

Step – 4: Confirmation of settings you entered in step 3.

Figure - 5

Step – 5: Finish. Well that is it, simple. Open up your database and you can see the changes

Figure - 6


Figure 7 shows the tables that are added to the selected database.

Figure - 7


An upcoming post will walk through the methods used to configure your application to utilize your database Roles and Memberships.