Need help moving from dbo.Users to dbo.aspnet_Users table.

Hi.

I have recently added the ASPNETDB tables to my database so I can take
advantage of
autentication / roles / groups and so on. From the online errata it
looks like I need to give
up my current Users table and add the columns I need from it to the
aspnet_Users table.
Is this correct? If so, I am worried about the migration from old to
new. Namely, I have some
constraints on the old Users table for checking if the same email
address exists on an insert and so on. Also, for columns that are
similar (such as userid) I am noticing that the types are different.
Would somebody mind advising me on the most graceful way to move (add
the columns and constraints) from the old Users table to aspnet_Users
without causing the application to go haywire? A plus is that this
application is in production so, there are currently no active users.

Thanks in advance.

USE [MyDBDev]
GO
/****** Object: Table [dbo].[Users] Script Date: 12/10/2008
14:04:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[firstname] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[email] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[alternateemail] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[password] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[securityquestion] [varchar](50) COLLATE Latin1_General_CI_AI NOT
NULL,
[securityanswer] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[zipcode] [int] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[gender] [varchar](10) COLLATE Latin1_General_CI_AI NULL,
[city] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[country] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[registerdate] [datetime] NOT NULL,
[editdate] [datetime] NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3]
DEFAULT ((0)),
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

USE [MyDBDev]
GO
/****** Object: Table [dbo].[aspnet_Users] Script Date: 12/10/2008
14:04:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Users](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
[UserName] [nvarchar](256) COLLATE Latin1_General_CI_AI NOT NULL,
[LoweredUserName] [nvarchar](256) COLLATE Latin1_General_CI_AI NOT
NULL,
[MobileAlias] [nvarchar](16) COLLATE Latin1_General_CI_AI NULL
DEFAULT (NULL),
[IsAnonymous] [bit] NOT NULL DEFAULT ((0)),
[LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[UserId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [MyDBDev]
GO
ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD FOREIGN KEY
([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]

pbd22 ( ) writes:
> I have recently added the ASPNETDB tables to my database so I can take
> advantage of
> autentication / roles / groups and so on. From the online errata it
> looks like I need to give
> up my current Users table and add the columns I need from it to the
> aspnet_Users table.
> Is this correct?
> If so, I am worried about the migration from old to
> new. Namely, I have some
> constraints on the old Users table for checking if the same email
> address exists on an insert and so on. Also, for columns that are
> similar (such as userid) I am noticing that the types are different.
> Would somebody mind advising me on the most graceful way to move (add
> the columns and constraints) from the old Users table to aspnet_Users
> without causing the application to go haywire? A plus is that this
> application is in production so, there are currently no active users.

The aspnet_Users is unknown to me, but my gut reaction is that you
should add a ASP_UserID column to your users table that would hold the
user id in aspnet_Users. But I don't know how you add users to this
aspnet_Users table. I notice that its user id is a GUID with a default
of newid(). You would need a way to get that newid() to maintain your
own table.

You may be better off asking in an ASP .Net group where they may know
more about ASPNETDB.


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx