So I’m really starting to do some work with the Azure cloud platform and I’m working on getting MVC running on it. I followed these excellent hints at “Cloudy in Seattle”.
As well as working on Windows Azure running an MVC app, I’m also trying to use SQL Azure. And as is noted in the referenced article, the aspnet_regsql.exe is not supported with SQL Azure. Now Jim does mention that they will be coming up with a script for that and he will update that post…. but it doesn’t look like he’s gotten around to it.
So I decided to figure it out. Below is the script I used, and it seems to work, took a little time to figure out, but not too much! You can download the SQL file here if you want.
/****** Object: Role [aspnet_Membership_BasicAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Membership_BasicAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Membership_FullAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Membership_FullAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Membership_ReportingAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Membership_ReportingAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Personalization_BasicAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Personalization_BasicAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Personalization_FullAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Personalization_FullAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Personalization_ReportingAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Personalization_ReportingAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Profile_BasicAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Profile_BasicAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Profile_FullAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Profile_FullAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Profile_ReportingAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Profile_ReportingAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Roles_BasicAccess] Script Date: 12/09/2009 09:44:23 ******/
CREATE ROLE [aspnet_Roles_BasicAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Roles_FullAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE ROLE [aspnet_Roles_FullAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_Roles_ReportingAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE ROLE [aspnet_Roles_ReportingAccess] AUTHORIZATION [dbo]
GO
/****** Object: Role [aspnet_WebEvent_FullAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE ROLE [aspnet_WebEvent_FullAccess] AUTHORIZATION [dbo]
GO
/****** Object: Schema [aspnet_Membership_BasicAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Membership_BasicAccess] AUTHORIZATION [aspnet_Membership_BasicAccess]
GO
/****** Object: Schema [aspnet_Membership_FullAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Membership_FullAccess] AUTHORIZATION [aspnet_Membership_FullAccess]
GO
/****** Object: Schema [aspnet_Membership_ReportingAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Membership_ReportingAccess] AUTHORIZATION [aspnet_Membership_ReportingAccess]
GO
/****** Object: Schema [aspnet_Personalization_BasicAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Personalization_BasicAccess] AUTHORIZATION [aspnet_Personalization_BasicAccess]
GO
/****** Object: Schema [aspnet_Personalization_FullAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Personalization_FullAccess] AUTHORIZATION [aspnet_Personalization_FullAccess]
GO
/****** Object: Schema [aspnet_Personalization_ReportingAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Personalization_ReportingAccess] AUTHORIZATION [aspnet_Personalization_ReportingAccess]
GO
/****** Object: Schema [aspnet_Profile_BasicAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Profile_BasicAccess] AUTHORIZATION [aspnet_Profile_BasicAccess]
GO
/****** Object: Schema [aspnet_Profile_FullAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Profile_FullAccess] AUTHORIZATION [aspnet_Profile_FullAccess]
GO
/****** Object: Schema [aspnet_Profile_ReportingAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Profile_ReportingAccess] AUTHORIZATION [aspnet_Profile_ReportingAccess]
GO
/****** Object: Schema [aspnet_Roles_BasicAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Roles_BasicAccess] AUTHORIZATION [aspnet_Roles_BasicAccess]
GO
/****** Object: Schema [aspnet_Roles_FullAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Roles_FullAccess] AUTHORIZATION [aspnet_Roles_FullAccess]
GO
/****** Object: Schema [aspnet_Roles_ReportingAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_Roles_ReportingAccess] AUTHORIZATION [aspnet_Roles_ReportingAccess]
GO
/****** Object: Schema [aspnet_WebEvent_FullAccess] Script Date: 12/09/2009 09:44:24 ******/
CREATE SCHEMA [aspnet_WebEvent_FullAccess] AUTHORIZATION [aspnet_WebEvent_FullAccess]
GO
/****** Object: Table [dbo].[aspnet_Applications] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [aspnet_Applications](
[ApplicationName] [nvarchar](256) NOT NULL,
[LoweredApplicationName] [nvarchar](256) NOT NULL,
[ApplicationId] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY CLUSTERED
(
[ApplicationId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
UNIQUE NONCLUSTERED
(
[LoweredApplicationName] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF),
UNIQUE NONCLUSTERED
(
[ApplicationName] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: Table [dbo].[aspnet_WebEvent_Events] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [aspnet_WebEvent_Events](
[EventId] [char](32) NOT NULL,
[EventTimeUtc] [datetime] NOT NULL,
[EventTime] [datetime] NOT NULL,
[EventType] [nvarchar](256) NOT NULL,
[EventSequence] [decimal](19, 0) NOT NULL,
[EventOccurrence] [decimal](19, 0) NOT NULL,
[EventCode] [int] NOT NULL,
[EventDetailCode] [int] NOT NULL,
[Message] [nvarchar](1024) NULL,
[ApplicationPath] [nvarchar](256) NULL,
[ApplicationVirtualPath] [nvarchar](256) NULL,
[MachineName] [nvarchar](256) NOT NULL,
[RequestUrl] [nvarchar](1024) NULL,
[ExceptionType] [nvarchar](256) NULL,
[Details] [ntext] NULL,
PRIMARY KEY CLUSTERED
(
[EventId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[aspnet_Setup_RestorePermissions] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [aspnet_Setup_RestorePermissions]
@name sysname
AS
BEGIN
DECLARE @object sysname
DECLARE @protectType char(10)
DECLARE @action varchar(60)
DECLARE @grantee sysname
DECLARE @cmd nvarchar(500)
DECLARE c1 cursor FORWARD_ONLY FOR
SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
OPEN c1
FETCH c1 INTO @object, @protectType, @action, @grantee
WHILE (@@fetch_status = 0)
BEGIN
SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
EXEC (@cmd)
FETCH c1 INTO @object, @protectType, @action, @grantee
END
CLOSE c1
DEALLOCATE c1
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers]
@name sysname
AS
BEGIN
CREATE TABLE #aspnet_RoleMembers
(
Group_name sysname,
Group_id smallint,
Users_in_group sysname,
User_id smallint
)
INSERT INTO #aspnet_RoleMembers
EXEC sp_helpuser @name
DECLARE @user_id smallint
DECLARE @cmd nvarchar(500)
DECLARE c1 cursor FORWARD_ONLY FOR
SELECT User_id FROM #aspnet_RoleMembers
OPEN c1
FETCH c1 INTO @user_id
WHILE (@@fetch_status = 0)
BEGIN
SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
EXEC (@cmd)
FETCH c1 INTO @user_id
END
CLOSE c1
DEALLOCATE c1
END
GO
/****** Object: Table [dbo].[aspnet_SchemaVersions] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_SchemaVersions](
[Feature] [nvarchar](128) NOT NULL,
[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
[IsCurrentVersion] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Feature] ASC,
[CompatibleSchemaVersion] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: View [dbo].[vw_aspnet_Applications] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Applications]
AS SELECT [aspnet_Applications].[ApplicationName], [aspnet_Applications].[LoweredApplicationName], [aspnet_Applications].[ApplicationId], [aspnet_Applications].[Description]
FROM [dbo].[aspnet_Applications]
GO
/****** Object: StoredProcedure [dbo].[aspnet_WebEvent_LogEvent] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_WebEvent_LogEvent]
@EventId char(32),
@EventTimeUtc datetime,
@EventTime datetime,
@EventType nvarchar(256),
@EventSequence decimal(19,0),
@EventOccurrence decimal(19,0),
@EventCode int,
@EventDetailCode int,
@Message nvarchar(1024),
@ApplicationPath nvarchar(256),
@ApplicationVirtualPath nvarchar(256),
@MachineName nvarchar(256),
@RequestUrl nvarchar(1024),
@ExceptionType nvarchar(256),
@Details ntext
AS
BEGIN
INSERT
dbo.aspnet_WebEvent_Events
(
EventId,
EventTimeUtc,
EventTime,
EventType,
EventSequence,
EventOccurrence,
EventCode,
EventDetailCode,
Message,
ApplicationPath,
ApplicationVirtualPath,
MachineName,
RequestUrl,
ExceptionType,
Details
)
VALUES
(
@EventId,
@EventTimeUtc,
@EventTime,
@EventType,
@EventSequence,
@EventOccurrence,
@EventCode,
@EventDetailCode,
@Message,
@ApplicationPath,
@ApplicationVirtualPath,
@MachineName,
@RequestUrl,
@ExceptionType,
@Details
)
END
GO
/****** Object: Table [dbo].[aspnet_Users] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Users](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](256) NOT NULL,
[LoweredUserName] [nvarchar](256) NOT NULL,
[MobileAlias] [nvarchar](16) NULL,
[IsAnonymous] [bit] NOT NULL,
[LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: StoredProcedure [dbo].[aspnet_UnRegisterSchemaVersion] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_UnRegisterSchemaVersion]
@Feature nvarchar(128),
@CompatibleSchemaVersion nvarchar(128)
AS
BEGIN
DELETE FROM dbo.aspnet_SchemaVersions
WHERE Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_CheckSchemaVersion] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_CheckSchemaVersion]
@Feature nvarchar(128),
@CompatibleSchemaVersion nvarchar(128)
AS
BEGIN
IF (EXISTS( SELECT *
FROM dbo.aspnet_SchemaVersions
WHERE Feature = LOWER( @Feature ) AND
CompatibleSchemaVersion = @CompatibleSchemaVersion ))
RETURN 0
RETURN 1
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Applications_CreateApplication] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Applications_CreateApplication]
@ApplicationName nvarchar(256),
@ApplicationId uniqueidentifier OUTPUT
AS
BEGIN
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF(@ApplicationId IS NULL)
BEGIN
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @ApplicationId = ApplicationId
FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF(@ApplicationId IS NULL)
BEGIN
SELECT @ApplicationId = NEWID()
INSERT dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
VALUES (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
END
IF( @TranStarted = 1 )
BEGIN
IF(@@ERROR = 0)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
END
END
END
GO
/****** Object: Table [dbo].[aspnet_Paths] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Paths](
[ApplicationId] [uniqueidentifier] NOT NULL,
[PathId] [uniqueidentifier] NOT NULL,
[Path] [nvarchar](256) NOT NULL,
[LoweredPath] [nvarchar](256) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PathId] ASC
)WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: StoredProcedure [dbo].[aspnet_Personalization_GetApplicationId] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Personalization_GetApplicationId] (
@ApplicationName NVARCHAR(256),
@ApplicationId UNIQUEIDENTIFIER OUT)
AS
BEGIN
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
END
GO
/****** Object: Table [dbo].[aspnet_Roles] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Roles](
[ApplicationId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
[LoweredRoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY CLUSTERED
(
[RoleId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: StoredProcedure [dbo].[aspnet_RegisterSchemaVersion] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_RegisterSchemaVersion]
@Feature nvarchar(128),
@CompatibleSchemaVersion nvarchar(128),
@IsCurrentVersion bit,
@RemoveIncompatibleSchema bit
AS
BEGIN
IF( @RemoveIncompatibleSchema = 1 )
BEGIN
DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
END
ELSE
BEGIN
IF( @IsCurrentVersion = 1 )
BEGIN
UPDATE dbo.aspnet_SchemaVersions
SET IsCurrentVersion = 0
WHERE Feature = LOWER( @Feature )
END
END
INSERT dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
END
GO
/****** Object: Table [dbo].[aspnet_PersonalizationPerUser] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_PersonalizationPerUser](
[Id] [uniqueidentifier] NOT NULL,
[PathId] [uniqueidentifier] NULL,
[UserId] [uniqueidentifier] NULL,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: Table [dbo].[aspnet_Profile] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Profile](
[UserId] [uniqueidentifier] NOT NULL,
[PropertyNames] [ntext] NOT NULL,
[PropertyValuesString] [ntext] NOT NULL,
[PropertyValuesBinary] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: Table [dbo].[aspnet_Membership] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Membership](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Password] [nvarchar](128) NOT NULL,
[PasswordFormat] [int] NOT NULL,
[PasswordSalt] [nvarchar](128) NOT NULL,
[MobilePIN] [nvarchar](16) NULL,
[Email] [nvarchar](256) NULL,
[LoweredEmail] [nvarchar](256) NULL,
[PasswordQuestion] [nvarchar](256) NULL,
[PasswordAnswer] [nvarchar](128) NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[LastLoginDate] [datetime] NOT NULL,
[LastPasswordChangedDate] [datetime] NOT NULL,
[LastLockoutDate] [datetime] NOT NULL,
[FailedPasswordAttemptCount] [int] NOT NULL,
[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
[Comment] [ntext] NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: StoredProcedure [dbo].[aspnet_Paths_CreatePath] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Paths_CreatePath]
@ApplicationId UNIQUEIDENTIFIER,
@Path NVARCHAR(256),
@PathId UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
BEGIN TRANSACTION
IF (NOT EXISTS(SELECT * FROM dbo.aspnet_Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
BEGIN
INSERT dbo.aspnet_Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
END
COMMIT TRANSACTION
SELECT @PathId = PathId FROM dbo.aspnet_Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
END
GO
/****** Object: Table [dbo].[aspnet_PersonalizationAllUsers] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers](
[PathId] [uniqueidentifier] NOT NULL,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[PathId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: StoredProcedure [dbo].[aspnet_Users_CreateUser] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Users_CreateUser]
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
IF( @UserId IS NULL )
SELECT @UserId = NEWID()
ELSE
BEGIN
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Roles_RoleExists] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Roles_RoleExists]
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(0)
IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
RETURN(1)
ELSE
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Roles_GetAllRoles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Roles_GetAllRoles] (
@ApplicationName nvarchar(256))
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN
SELECT RoleName
FROM dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
ORDER BY RoleName
END
GO
/****** Object: Table [dbo].[aspnet_UsersInRoles] Script Date: 12/09/2009 09:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_UsersInRoles](
[UserId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
/****** Object: StoredProcedure [dbo].[aspnet_Roles_CreateRole] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Roles_CreateRole]
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
INSERT INTO dbo.aspnet_Roles
(ApplicationId, RoleName, LoweredRoleName)
VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: View [dbo].[vw_aspnet_Users] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Users]
AS SELECT [aspnet_Users].[ApplicationId], [aspnet_Users].[UserId], [aspnet_Users].[UserName], [aspnet_Users].[LoweredUserName], [aspnet_Users].[MobileAlias], [aspnet_Users].[IsAnonymous], [aspnet_Users].[LastActivityDate]
FROM [dbo].[aspnet_Users]
GO
/****** Object: View [dbo].[vw_aspnet_Roles] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Roles]
AS SELECT [aspnet_Roles].[ApplicationId], [aspnet_Roles].[RoleId], [aspnet_Roles].[RoleName], [aspnet_Roles].[LoweredRoleName], [aspnet_Roles].[Description]
FROM [dbo].[aspnet_Roles]
GO
/****** Object: View [dbo].[vw_aspnet_WebPartState_Paths] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]
AS SELECT [aspnet_Paths].[ApplicationId], [aspnet_Paths].[PathId], [aspnet_Paths].[Path], [aspnet_Paths].[LoweredPath]
FROM [dbo].[aspnet_Paths]
GO
/****** Object: View [dbo].[vw_aspnet_WebPartState_User] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]
AS SELECT [aspnet_PersonalizationPerUser].[PathId], [aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([aspnet_PersonalizationPerUser].[PageSettings]), [aspnet_PersonalizationPerUser].[LastUpdatedDate]
FROM [dbo].[aspnet_PersonalizationPerUser]
GO
/****** Object: View [dbo].[vw_aspnet_WebPartState_Shared] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]
AS SELECT [aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([aspnet_PersonalizationAllUsers].[PageSettings]), [aspnet_PersonalizationAllUsers].[LastUpdatedDate]
FROM [dbo].[aspnet_PersonalizationAllUsers]
GO
/****** Object: View [dbo].[vw_aspnet_UsersInRoles] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
AS SELECT [aspnet_UsersInRoles].[UserId], [aspnet_UsersInRoles].[RoleId]
FROM [dbo].[aspnet_UsersInRoles]
GO
/****** Object: View [dbo].[vw_aspnet_Profiles] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Profiles]
AS SELECT [aspnet_Profile].[UserId], [aspnet_Profile].[LastUpdatedDate],
[DataSize]= DATALENGTH([aspnet_Profile].[PropertyNames])
+ DATALENGTH([aspnet_Profile].[PropertyValuesString])
+ DATALENGTH([aspnet_Profile].[PropertyValuesBinary])
FROM [dbo].[aspnet_Profile]
GO
/****** Object: View [dbo].[vw_aspnet_MembershipUsers] Script Date: 12/09/2009 09:44:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
AS SELECT [aspnet_Membership].[UserId],
[aspnet_Membership].[PasswordFormat],
[aspnet_Membership].[MobilePIN],
[aspnet_Membership].[Email],
[aspnet_Membership].[LoweredEmail],
[aspnet_Membership].[PasswordQuestion],
[aspnet_Membership].[PasswordAnswer],
[aspnet_Membership].[IsApproved],
[aspnet_Membership].[IsLockedOut],
[aspnet_Membership].[CreateDate],
[aspnet_Membership].[LastLoginDate],
[aspnet_Membership].[LastPasswordChangedDate],
[aspnet_Membership].[LastLockoutDate],
[aspnet_Membership].[FailedPasswordAttemptCount],
[aspnet_Membership].[FailedPasswordAttemptWindowStart],
[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
[aspnet_Membership].[Comment],
[aspnet_Users].[ApplicationId],
[aspnet_Users].[UserName],
[aspnet_Users].[MobileAlias],
[aspnet_Users].[IsAnonymous],
[aspnet_Users].[LastActivityDate]
FROM [aspnet_Membership] INNER JOIN [aspnet_Users]
ON [aspnet_Membership].[UserId] = [aspnet_Users].[UserId]
GO
/****** Object: StoredProcedure [dbo].[aspnet_Profile_DeleteInactiveProfiles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteInactiveProfiles]
@ApplicationName nvarchar(256),
@ProfileAuthOptions int,
@InactiveSinceDate datetime
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
BEGIN
SELECT 0
RETURN
END
DELETE
FROM dbo.aspnet_Profile
WHERE UserId IN
( SELECT UserId
FROM dbo.aspnet_Users u
WHERE ApplicationId = @ApplicationId
AND (LastActivityDate <= @InactiveSinceDate)
AND (
(@ProfileAuthOptions = 2)
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)
)
SELECT @@ROWCOUNT
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000)
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256)
DECLARE @CountAll int
DECLARE @CountU int
DECLARE @CountR int
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
SELECT @CountR = @@ROWCOUNT
IF (@CountR <> @Num)
BEGIN
SELECT TOP 1 N'', Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
SELECT @CountU = @@ROWCOUNT
IF (@CountU <> @Num)
BEGIN
SELECT TOP 1 Name, N''
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(1)
END
SELECT @CountAll = COUNT(*)
FROM dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId
IF (@CountAll <> @CountU * @CountR)
BEGIN
SELECT TOP 1 UserName, RoleName
FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
DELETE FROM dbo.aspnet_UsersInRoles
WHERE UserId IN (SELECT UserId FROM @tbUsers)
AND RoleId IN (SELECT RoleId FROM @tbRoles)
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(2)
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
IF (@UserId IS NULL)
RETURN(2)
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN(3)
IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId))
RETURN(1)
ELSE
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetUsersInRoles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetUsersInRoles]
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN(1)
SELECT u.UserName
FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
ORDER BY u.UserName
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetRolesForUser] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetRolesForUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
IF (@UserId IS NULL)
RETURN(1)
SELECT r.RoleName
FROM dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
WHERE r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
ORDER BY r.RoleName
RETURN (0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_FindUsersInRole] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_FindUsersInRole]
@ApplicationName nvarchar(256),
@RoleName nvarchar(256),
@UserNameToMatch nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN(1)
SELECT u.UserName
FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
ORDER BY u.UserName
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000),
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256)
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
IF (@@ROWCOUNT <> @Num)
BEGIN
SELECT TOP 1 Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
IF (@@ROWCOUNT <> @Num)
BEGIN
DELETE FROM @tbNames
WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE au.UserId = u.UserId)
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
FROM @tbNames
INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users au, @tbNames t
WHERE LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
END
IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
BEGIN
SELECT TOP 1 UserName, RoleName
FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
SELECT UserId, RoleId
FROM @tbUsers, @tbRoles
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom int OUTPUT
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode int
DECLARE @RowCount int
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER(@UserName)
AND u.ApplicationId = a.ApplicationId
AND LOWER(@ApplicationName) = a.LoweredApplicationName
IF (@UserId IS NULL)
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF ((@TablesToDeleteFrom & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF ((@TablesToDeleteFrom & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF ((@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(@TablesToDeleteFrom & 2) <> 0 AND
(@TablesToDeleteFrom & 4) <> 0 AND
(@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
SET @NumTablesDeletedFrom = 0
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Roles_DeleteRole] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Roles_DeleteRole]
@ApplicationName nvarchar(256),
@RoleName nvarchar(256),
@DeleteOnlyIfRoleIsEmpty bit
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(1)
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
BEGIN
SELECT @ErrorCode = 1
GOTO Cleanup
END
IF (@DeleteOnlyIfRoleIsEmpty <> 0)
BEGIN
IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId))
BEGIN
SELECT @ErrorCode = 2
GOTO Cleanup
END
END
DELETE FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId AND ApplicationId = @ApplicationId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUserInfo] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUserInfo]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@IsPasswordCorrect bit,
@UpdateLastLoginActivityDate bit,
@MaxInvalidPasswordAttempts int,
@PasswordAttemptWindow int,
@CurrentTimeUtc datetime,
@LastLoginDate datetime,
@LastActivityDate datetime
AS
BEGIN
DECLARE @UserId uniqueidentifier
DECLARE @IsApproved bit
DECLARE @IsLockedOut bit
DECLARE @LastLockoutDate datetime
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAttemptWindowStart datetime
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @UserId = u.UserId,
@IsApproved = m.IsApproved,
@IsLockedOut = m.IsLockedOut,
@LastLockoutDate = m.LastLockoutDate,
@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
IF ( @@rowcount = 0 )
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
IF( @IsLockedOut = 1 )
BEGIN
GOTO Cleanup
END
IF( @IsPasswordCorrect = 0 )
BEGIN
IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
BEGIN
SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAttemptCount = 1
END
ELSE
BEGIN
SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
END
BEGIN
IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @CurrentTimeUtc
END
END
END
ELSE
BEGIN
IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
BEGIN
SET @FailedPasswordAttemptCount = 0
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
END
END
IF( @UpdateLastLoginActivityDate = 1 )
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @LastActivityDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
UPDATE dbo.aspnet_Membership
SET LastLoginDate = @LastLoginDate
WHERE UserId = @UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUser] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Email nvarchar(256),
@Comment ntext,
@IsApproved bit,
@LastLoginDate datetime,
@LastActivityDate datetime,
@UniqueEmail int,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @UserId uniqueidentifier
DECLARE @ApplicationId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId, @ApplicationId = a.ApplicationId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF (@UserId IS NULL)
RETURN(1)
IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
WHERE ApplicationId = @ApplicationId AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
BEGIN
RETURN(7)
END
END
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
UPDATE dbo.aspnet_Users WITH (ROWLOCK)
SET
LastActivityDate = @LastActivityDate
WHERE
@UserId = UserId
IF( @@ERROR <> 0 )
GOTO Cleanup
UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
SET
Email = @Email,
LoweredEmail = LOWER(@Email),
Comment = @Comment,
IsApproved = @IsApproved,
LastLoginDate = @LastLoginDate
WHERE
@UserId = UserId
IF( @@ERROR <> 0 )
GOTO Cleanup
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN -1
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_UnlockUser] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_UnlockUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256)
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF ( @UserId IS NULL )
RETURN 1
UPDATE dbo.aspnet_Membership
SET IsLockedOut = 0,
FailedPasswordAttemptCount = 0,
FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
FailedPasswordAnswerAttemptCount = 0,
FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
LastLockoutDate = CONVERT( datetime, '17540101', 112 )
WHERE @UserId = UserId
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_SetPassword] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_SetPassword]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@NewPassword nvarchar(128),
@PasswordSalt nvarchar(128),
@CurrentTimeUtc datetime,
@PasswordFormat int = 0
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF (@UserId IS NULL)
RETURN(1)
UPDATE dbo.aspnet_Membership
SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
LastPasswordChangedDate = @CurrentTimeUtc
WHERE @UserId = UserId
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_ResetPassword] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_ResetPassword]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@NewPassword nvarchar(128),
@MaxInvalidPasswordAttempts int,
@PasswordAttemptWindow int,
@PasswordSalt nvarchar(128),
@CurrentTimeUtc datetime,
@PasswordFormat int = 0,
@PasswordAnswer nvarchar(128) = NULL
AS
BEGIN
DECLARE @IsLockedOut bit
DECLARE @LastLockoutDate datetime
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAttemptWindowStart datetime
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
DECLARE @UserId uniqueidentifier
SET @UserId = NULL
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF ( @UserId IS NULL )
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
SELECT @IsLockedOut = IsLockedOut,
@LastLockoutDate = LastLockoutDate,
@FailedPasswordAttemptCount = FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Membership WITH ( UPDLOCK )
WHERE @UserId = UserId
IF( @IsLockedOut = 1 )
BEGIN
SET @ErrorCode = 99
GOTO Cleanup
END
UPDATE dbo.aspnet_Membership
SET Password = @NewPassword,
LastPasswordChangedDate = @CurrentTimeUtc,
PasswordFormat = @PasswordFormat,
PasswordSalt = @PasswordSalt
WHERE @UserId = UserId AND
( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
IF ( @@ROWCOUNT = 0 )
BEGIN
IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAnswerAttemptCount = 1
END
ELSE
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
END
BEGIN
IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @CurrentTimeUtc
END
END
SET @ErrorCode = 3
END
ELSE
BEGIN
IF( @FailedPasswordAnswerAttemptCount > 0 )
BEGIN
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
END
END
IF( NOT ( @PasswordAnswer IS NULL ) )
BEGIN
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByUserId] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByUserId]
@UserId uniqueidentifier,
@CurrentTimeUtc datetime,
@UpdateLastActivity bit = 0
AS
BEGIN
IF ( @UpdateLastActivity = 1 )
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
FROM dbo.aspnet_Users
WHERE @UserId = UserId
IF ( @@ROWCOUNT = 0 ) -- User ID not found
RETURN -1
END
SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate,
m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE @UserId = u.UserId AND u.UserId = m.UserId
IF ( @@ROWCOUNT = 0 ) -- User ID not found
RETURN -1
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByName] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@CurrentTimeUtc datetime,
@UpdateLastActivity bit = 0
AS
BEGIN
DECLARE @UserId uniqueidentifier
IF (@UpdateLastActivity = 1)
BEGIN
-- select user ID from aspnet_users table
SELECT TOP 1 @UserId = u.UserId
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
IF (@@ROWCOUNT = 0) -- Username not found
RETURN -1
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
WHERE @UserId = UserId
SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut, m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE @UserId = u.UserId AND u.UserId = m.UserId
END
ELSE
BEGIN
SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,m.LastLockoutDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
IF (@@ROWCOUNT = 0) -- Username not found
RETURN -1
END
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByEmail] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
@ApplicationName nvarchar(256),
@Email nvarchar(256)
AS
BEGIN
IF( @Email IS NULL )
SELECT u.UserName
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
m.LoweredEmail IS NULL
ELSE
SELECT u.UserName
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@Email) = m.LoweredEmail
IF (@@rowcount = 0)
RETURN(1)
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPasswordWithFormat] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetPasswordWithFormat]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@UpdateLastLoginActivityDate bit,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @IsLockedOut bit
DECLARE @UserId uniqueidentifier
DECLARE @Password nvarchar(128)
DECLARE @PasswordSalt nvarchar(128)
DECLARE @PasswordFormat int
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @IsApproved bit
DECLARE @LastActivityDate datetime
DECLARE @LastLoginDate datetime
SELECT @UserId = NULL
SELECT @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
@PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
@FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
@LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
IF (@UserId IS NULL)
RETURN 1
IF (@IsLockedOut = 1)
RETURN 99
SELECT @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
@FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate
IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
BEGIN
UPDATE dbo.aspnet_Membership
SET LastLoginDate = @CurrentTimeUtc
WHERE UserId = @UserId
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
WHERE @UserId = UserId
END
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPassword] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetPassword]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@MaxInvalidPasswordAttempts int,
@PasswordAttemptWindow int,
@CurrentTimeUtc datetime,
@PasswordAnswer nvarchar(128) = NULL
AS
BEGIN
DECLARE @UserId uniqueidentifier
DECLARE @PasswordFormat int
DECLARE @Password nvarchar(128)
DECLARE @passAns nvarchar(128)
DECLARE @IsLockedOut bit
DECLARE @LastLockoutDate datetime
DECLARE @FailedPasswordAttemptCount int
DECLARE @FailedPasswordAttemptWindowStart datetime
DECLARE @FailedPasswordAnswerAttemptCount int
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SELECT @UserId = u.UserId,
@Password = m.Password,
@passAns = m.PasswordAnswer,
@PasswordFormat = m.PasswordFormat,
@IsLockedOut = m.IsLockedOut,
@LastLockoutDate = m.LastLockoutDate,
@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
IF ( @@rowcount = 0 )
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
IF( @IsLockedOut = 1 )
BEGIN
SET @ErrorCode = 99
GOTO Cleanup
END
IF ( NOT( @PasswordAnswer IS NULL ) )
BEGIN
IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
BEGIN
IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
SET @FailedPasswordAnswerAttemptCount = 1
END
ELSE
BEGIN
SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
END
BEGIN
IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @CurrentTimeUtc
END
END
SET @ErrorCode = 3
END
ELSE
BEGIN
IF( @FailedPasswordAnswerAttemptCount > 0 )
BEGIN
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
END
END
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
IF( @ErrorCode = 0 )
SELECT @Password, @PasswordFormat
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
@ApplicationName nvarchar(256),
@MinutesSinceLastInActive int,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @DateActive datetime
SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc)
DECLARE @NumOnline int
SELECT @NumOnline = COUNT(*)
FROM dbo.aspnet_Users u,
dbo.aspnet_Applications a,
dbo.aspnet_Membership m
WHERE u.ApplicationId = a.ApplicationId AND
LastActivityDate > @DateActive AND
a.LoweredApplicationName = LOWER(@ApplicationName) AND
u.UserId = m.UserId
RETURN(@NumOnline)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetAllUsers] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetAllUsers]
@ApplicationName nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u
WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
ORDER BY u.UserName
SELECT @TotalRecords = @@ROWCOUNT
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName
RETURN @TotalRecords
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByName] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
@ApplicationName nvarchar(256),
@UserNameToMatch nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
ORDER BY u.UserName
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName
SELECT @TotalRecords = COUNT(*)
FROM #PageIndexForUsers
RETURN @TotalRecords
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByEmail] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByEmail]
@ApplicationName nvarchar(256),
@EmailToMatch nvarchar(256),
@PageIndex int,
@PageSize int
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
IF( @EmailToMatch IS NULL )
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
ORDER BY m.LoweredEmail
ELSE
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
ORDER BY m.LoweredEmail
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY m.LoweredEmail
SELECT @TotalRecords = COUNT(*)
FROM #PageIndexForUsers
RETURN @TotalRecords
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_CreateUser] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_CreateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL
DECLARE @IsLockedOut bit
SET @IsLockedOut = 0
DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0
DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
SET @CreateDate = @CurrentTimeUtc
SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END
IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
SET @UserId = @NewUserId
IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END
IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@NewPasswordQuestion nvarchar(256),
@NewPasswordAnswer nvarchar(128)
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId
IF (@UserId IS NULL)
BEGIN
RETURN(1)
END
UPDATE dbo.aspnet_Membership
SET PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
WHERE UserId=@UserId
RETURN(0)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_AnyDataInTables] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables]
@TablesToCheck int
AS
BEGIN
-- Check Membership table if (@TablesToCheck & 1) is set
IF ((@TablesToCheck & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
BEGIN
SELECT N'aspnet_Membership'
RETURN
END
END
-- Check aspnet_Roles table if (@TablesToCheck & 2) is set
IF ((@TablesToCheck & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
BEGIN
SELECT N'aspnet_Roles'
RETURN
END
END
-- Check aspnet_Profile table if (@TablesToCheck & 4) is set
IF ((@TablesToCheck & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
BEGIN
SELECT N'aspnet_Profile'
RETURN
END
END
-- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
IF ((@TablesToCheck & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
BEGIN
SELECT N'aspnet_PersonalizationPerUser'
RETURN
END
END
-- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
IF ((@TablesToCheck & 16) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
BEGIN
IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
BEGIN
SELECT N'aspnet_WebEvent_Events'
RETURN
END
END
-- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
IF ((@TablesToCheck & 1) <> 0 AND
(@TablesToCheck & 2) <> 0 AND
(@TablesToCheck & 4) <> 0 AND
(@TablesToCheck & 8) <> 0 AND
(@TablesToCheck & 32) <> 0 AND
(@TablesToCheck & 128) <> 0 AND
(@TablesToCheck & 256) <> 0 AND
(@TablesToCheck & 512) <> 0 AND
(@TablesToCheck & 1024) <> 0)
BEGIN
IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
BEGIN
SELECT N'aspnet_Users'
RETURN
END
IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
BEGIN
SELECT N'aspnet_Applications'
RETURN
END
END
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_ResetUserState] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetUserState] (
@Count int OUT,
@ApplicationName NVARCHAR(256),
@InactiveSinceDate DATETIME = NULL,
@UserName NVARCHAR(256) = NULL,
@Path NVARCHAR(256) = NULL)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
SELECT @Count = 0
ELSE
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser
WHERE Id IN (SELECT PerUser.Id
FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId
AND PerUser.UserId = Users.UserId
AND PerUser.PathId = Paths.PathId
AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))
SELECT @Count = @@ROWCOUNT
END
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_ResetSharedState] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetSharedState] (
@Count int OUT,
@ApplicationName NVARCHAR(256),
@Path NVARCHAR(256))
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
SELECT @Count = 0
ELSE
BEGIN
DELETE FROM dbo.aspnet_PersonalizationAllUsers
WHERE PathId IN
(SELECT AllUsers.PathId
FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId
AND AllUsers.PathId = Paths.PathId
AND Paths.LoweredPath = LOWER(@Path))
SELECT @Count = @@ROWCOUNT
END
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_GetCountOfState] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_GetCountOfState] (
@Count int OUT,
@AllUsersScope bit,
@ApplicationName NVARCHAR(256),
@Path NVARCHAR(256) = NULL,
@UserName NVARCHAR(256) = NULL,
@InactiveSinceDate DATETIME = NULL)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
SELECT @Count = 0
ELSE
IF (@AllUsersScope = 1)
SELECT @Count = COUNT(*)
FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId
AND AllUsers.PathId = Paths.PathId
AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
ELSE
SELECT @Count = COUNT(*)
FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId
AND PerUser.UserId = Users.UserId
AND PerUser.PathId = Paths.PathId
AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_FindState] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_FindState] (
@AllUsersScope bit,
@ApplicationName NVARCHAR(256),
@PageIndex INT,
@PageSize INT,
@Path NVARCHAR(256) = NULL,
@UserName NVARCHAR(256) = NULL,
@InactiveSinceDate DATETIME = NULL)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
RETURN
-- Set the page bounds
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @TotalRecords INT
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table to store the selected results
CREATE TABLE #PageIndex (
IndexId int IDENTITY (0, 1) NOT NULL,
ItemId UNIQUEIDENTIFIER
)
IF (@AllUsersScope = 1)
BEGIN
-- Insert into our temp table
INSERT INTO #PageIndex (ItemId)
SELECT Paths.PathId
FROM dbo.aspnet_Paths Paths,
((SELECT Paths.PathId
FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId
AND AllUsers.PathId = Paths.PathId
AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
) AS SharedDataPerPath
FULL OUTER JOIN
(SELECT DISTINCT Paths.PathId
FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId
AND PerUser.PathId = Paths.PathId
AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
) AS UserDataPerPath
ON SharedDataPerPath.PathId = UserDataPerPath.PathId
)
WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
ORDER BY Paths.Path ASC
SELECT @TotalRecords = @@ROWCOUNT
SELECT Paths.Path,
SharedDataPerPath.LastUpdatedDate,
SharedDataPerPath.SharedDataLength,
UserDataPerPath.UserDataLength,
UserDataPerPath.UserCount
FROM dbo.aspnet_Paths Paths,
((SELECT PageIndex.ItemId AS PathId,
AllUsers.LastUpdatedDate AS LastUpdatedDate,
DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
WHERE AllUsers.PathId = PageIndex.ItemId
AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
) AS SharedDataPerPath
FULL OUTER JOIN
(SELECT PageIndex.ItemId AS PathId,
SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
COUNT(*) AS UserCount
FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
WHERE PerUser.PathId = PageIndex.ItemId
AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
GROUP BY PageIndex.ItemId
) AS UserDataPerPath
ON SharedDataPerPath.PathId = UserDataPerPath.PathId
)
WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
ORDER BY Paths.Path ASC
END
ELSE
BEGIN
-- Insert into our temp table
INSERT INTO #PageIndex (ItemId)
SELECT PerUser.Id
FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId
AND PerUser.UserId = Users.UserId
AND PerUser.PathId = Paths.PathId
AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
ORDER BY Paths.Path ASC, Users.UserName ASC
SELECT @TotalRecords = @@ROWCOUNT
SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
WHERE PerUser.Id = PageIndex.ItemId
AND PerUser.UserId = Users.UserId
AND PerUser.PathId = Paths.PathId
AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
ORDER BY Paths.Path ASC, Users.UserName ASC
END
RETURN @TotalRecords
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAdministration_DeleteAllState] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_DeleteAllState] (
@AllUsersScope bit,
@ApplicationName NVARCHAR(256),
@Count int OUT)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
SELECT @Count = 0
ELSE
BEGIN
IF (@AllUsersScope = 1)
DELETE FROM aspnet_PersonalizationAllUsers
WHERE PathId IN
(SELECT Paths.PathId
FROM dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId)
ELSE
DELETE FROM aspnet_PersonalizationPerUser
WHERE PathId IN
(SELECT Paths.PathId
FROM dbo.aspnet_Paths Paths
WHERE Paths.ApplicationId = @ApplicationId)
SELECT @Count = @@ROWCOUNT
END
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationPerUser_SetPageSettings] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_SetPageSettings] (
@ApplicationName NVARCHAR(256),
@UserName NVARCHAR(256),
@Path NVARCHAR(256),
@PageSettings IMAGE,
@CurrentTimeUtc DATETIME)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
DECLARE @PathId UNIQUEIDENTIFIER
DECLARE @UserId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @PathId = NULL
SELECT @UserId = NULL
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
IF (@PathId IS NULL)
BEGIN
EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
END
SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
IF (@UserId IS NULL)
BEGIN
EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
END
UPDATE dbo.aspnet_Users WITH (ROWLOCK)
SET LastActivityDate = @CurrentTimeUtc
WHERE UserId = @UserId
IF (@@ROWCOUNT = 0) -- Username not found
RETURN
IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
UPDATE dbo.aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
ELSE
INSERT INTO dbo.aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings] (
@ApplicationName NVARCHAR(256),
@UserName NVARCHAR(256),
@Path NVARCHAR(256),
@CurrentTimeUtc DATETIME)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
DECLARE @PathId UNIQUEIDENTIFIER
DECLARE @UserId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @PathId = NULL
SELECT @UserId = NULL
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
BEGIN
RETURN
END
SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
IF (@PathId IS NULL)
BEGIN
RETURN
END
SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
IF (@UserId IS NULL)
BEGIN
RETURN
END
UPDATE dbo.aspnet_Users WITH (ROWLOCK)
SET LastActivityDate = @CurrentTimeUtc
WHERE UserId = @UserId
IF (@@ROWCOUNT = 0) -- Username not found
RETURN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationPerUser_GetPageSettings] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_GetPageSettings] (
@ApplicationName NVARCHAR(256),
@UserName NVARCHAR(256),
@Path NVARCHAR(256),
@CurrentTimeUtc DATETIME)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
DECLARE @PathId UNIQUEIDENTIFIER
DECLARE @UserId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @PathId = NULL
SELECT @UserId = NULL
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
BEGIN
RETURN
END
SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
IF (@PathId IS NULL)
BEGIN
RETURN
END
SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
IF (@UserId IS NULL)
BEGIN
RETURN
END
UPDATE dbo.aspnet_Users WITH (ROWLOCK)
SET LastActivityDate = @CurrentTimeUtc
WHERE UserId = @UserId
IF (@@ROWCOUNT = 0) -- Username not found
RETURN
SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings] (
@ApplicationName NVARCHAR(256),
@Path NVARCHAR(256),
@PageSettings IMAGE,
@CurrentTimeUtc DATETIME)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
DECLARE @PathId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @PathId = NULL
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
IF (@PathId IS NULL)
BEGIN
EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
END
IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId))
UPDATE dbo.aspnet_PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
ELSE
INSERT INTO dbo.aspnet_PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings] (
@ApplicationName NVARCHAR(256),
@Path NVARCHAR(256))
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
DECLARE @PathId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @PathId = NULL
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
BEGIN
RETURN
END
SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
IF (@PathId IS NULL)
BEGIN
RETURN
END
DELETE FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId
RETURN 0
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings] (
@ApplicationName NVARCHAR(256),
@Path NVARCHAR(256))
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
DECLARE @PathId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @PathId = NULL
EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
IF (@ApplicationId IS NULL)
BEGIN
RETURN
END
SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
IF (@PathId IS NULL)
BEGIN
RETURN
END
SELECT p.PageSettings FROM dbo.aspnet_PersonalizationAllUsers p WHERE p.PathId = @PathId
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Profile_SetProperties] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Profile_SetProperties]
@ApplicationName nvarchar(256),
@PropertyNames ntext,
@PropertyValuesString ntext,
@PropertyValuesBinary image,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
DECLARE @UserId uniqueidentifier
DECLARE @LastActivityDate datetime
SELECT @UserId = NULL
SELECT @LastActivityDate = @CurrentTimeUtc
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
IF (@UserId IS NULL)
EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
UPDATE dbo.aspnet_Users
SET LastActivityDate=@CurrentTimeUtc
WHERE UserId = @UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF (EXISTS( SELECT *
FROM dbo.aspnet_Profile
WHERE UserId = @UserId))
UPDATE dbo.aspnet_Profile
SET PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
WHERE UserId = @UserId
ELSE
INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Profile_GetProperties] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Profile_GetProperties]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
IF (@UserId IS NULL)
RETURN
SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
FROM dbo.aspnet_Profile
WHERE UserId = @UserId
IF (@@ROWCOUNT > 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate=@CurrentTimeUtc
WHERE UserId = @UserId
END
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Profile_GetProfiles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Profile_GetProfiles]
@ApplicationName nvarchar(256),
@ProfileAuthOptions int,
@PageIndex int,
@PageSize int,
@UserNameToMatch nvarchar(256) = NULL,
@InactiveSinceDate datetime = NULL
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p
WHERE ApplicationId = @ApplicationId
AND u.UserId = p.UserId
AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
AND ( (@ProfileAuthOptions = 2)
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)
AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
ORDER BY UserName
SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
SELECT COUNT(*)
FROM #PageIndexForUsers
DROP TABLE #PageIndexForUsers
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]
@ApplicationName nvarchar(256),
@ProfileAuthOptions int,
@InactiveSinceDate datetime
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
BEGIN
SELECT 0
RETURN
END
SELECT COUNT(*)
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p
WHERE ApplicationId = @ApplicationId
AND u.UserId = p.UserId
AND (LastActivityDate <= @InactiveSinceDate)
AND (
(@ProfileAuthOptions = 2)
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)
END
GO
/****** Object: StoredProcedure [dbo].[aspnet_Profile_DeleteProfiles] Script Date: 12/09/2009 09:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteProfiles]
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000)
AS
BEGIN
DECLARE @UserName nvarchar(256)
DECLARE @CurrentPos int
DECLARE @NextPos int
DECLARE @NumDeleted int
DECLARE @DeletedUser int
DECLARE @TranStarted bit
DECLARE @ErrorCode int
SET @ErrorCode = 0
SET @CurrentPos = 1
SET @NumDeleted = 0
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
WHILE (@CurrentPos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @UserNames, @CurrentPos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
SELECT @CurrentPos = @NextPos+1
IF (LEN(@UserName) > 0)
BEGIN
SELECT @DeletedUser = 0
EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF (@DeletedUser <> 0)
SELECT @NumDeleted = @NumDeleted + 1
END
END
SELECT @NumDeleted
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
SET @TranStarted = 0
RETURN 0
Cleanup:
IF (@TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: Default [DF__aspnet_Ap__Appli__08EA5793] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Applications] ADD DEFAULT (newid()) FOR [ApplicationId]
GO
/****** Object: Default [DF__aspnet_Me__Passw__239E4DCF] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Membership] ADD DEFAULT ((0)) FOR [PasswordFormat]
GO
/****** Object: Default [DF__aspnet_Pa__PathI__5BE2A6F2] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Paths] ADD DEFAULT (newid()) FOR [PathId]
GO
/****** Object: Default [DF__aspnet_Perso__Id__6754599E] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] ADD DEFAULT (newid()) FOR [Id]
GO
/****** Object: Default [DF__aspnet_Ro__RoleI__44FF419A] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Roles] ADD DEFAULT (newid()) FOR [RoleId]
GO
/****** Object: Default [DF__aspnet_Us__UserI__0EA330E9] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (newid()) FOR [UserId]
GO
/****** Object: Default [DF__aspnet_Us__Mobil__0F975522] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (NULL) FOR [MobileAlias]
GO
/****** Object: Default [DF__aspnet_Us__IsAno__108B795B] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT ((0)) FOR [IsAnonymous]
GO
/****** Object: ForeignKey [FK__aspnet_Me__Appli__21B6055D] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
/****** Object: ForeignKey [FK__aspnet_Me__UserI__22AA2996] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
/****** Object: ForeignKey [FK__aspnet_Pa__Appli__5AEE82B9] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Paths] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
/****** Object: ForeignKey [FK__aspnet_Pe__PathI__628FA481] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers] WITH CHECK ADD FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
GO
/****** Object: ForeignKey [FK__aspnet_Pe__PathI__68487DD7] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
GO
/****** Object: ForeignKey [FK__aspnet_Pe__UserI__693CA210] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
/****** Object: ForeignKey [FK__aspnet_Pr__UserI__38996AB5] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Profile] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
/****** Object: ForeignKey [FK__aspnet_Ro__Appli__440B1D61] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Roles] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
/****** Object: ForeignKey [FK__aspnet_Us__Appli__0DAF0CB0] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
/****** Object: ForeignKey [FK__aspnet_Us__RoleI__4AB81AF0] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])
GO
/****** Object: ForeignKey [FK__aspnet_Us__UserI__49C3F6B7] Script Date: 12/09/2009 09:44:12 ******/
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
Insert into [aspnet_SchemaVersions] Values('common', '1', 1)
GO
Insert into [aspnet_SchemaVersions] Values('health monitoring', '1', 1)
GO
Insert into [aspnet_SchemaVersions] Values('membership', '1', 1)
GO
Insert into [aspnet_SchemaVersions] Values('personalization', '1', 1)
GO
Insert into [aspnet_SchemaVersions] Values('profile', '1', 1)
GO
Insert into [aspnet_SchemaVersions] Values('role manager', '1', 1)
GO
