Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Setup_RestorePermissions]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Setup_RemoveAllRoleMembers]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UnRegisterSchemaVersion] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UnRegisterSchemaVersion]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_CheckSchemaVersion] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_CheckSchemaVersion]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Applications_CreateApplication] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Applications_CreateApplication]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_RegisterSchemaVersion] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_RegisterSchemaVersion]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_RoleExists] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_RoleExists]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_GetAllRoles] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_GetAllRoles]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_CreateRole] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_CreateRole]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Users_CreateUser] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users_CreateUser]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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
- '
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_IsUserInRole]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetUsersInRoles] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_GetUsersInRoles]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetRolesForUser] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_GetRolesForUser]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_FindUsersInRole] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_FindUsersInRole]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_AddUsersToRoles]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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 '
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Roles_DeleteRole] Script Date: 04/09/2012 20:01:32 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_DeleteRole]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'
- 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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUserInfo] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UpdateUserInfo]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUser] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UpdateUser]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_UnlockUser] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UnlockUser]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_SetPassword] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_SetPassword]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_ResetPassword] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_ResetPassword]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByUserId] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByUserId]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByName] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByName]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByEmail] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByEmail]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPasswordWithFormat] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetPasswordWithFormat]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPassword] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetPassword]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetNumberOfUsersOnline]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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(NOLOCK),
- dbo.aspnet_Applications a(NOLOCK),
- dbo.aspnet_Membership m(NOLOCK)
- WHERE u.ApplicationId = a.ApplicationId AND
- LastActivityDate > @DateActive AND
- a.LoweredApplicationName = LOWER(@ApplicationName) AND
- u.UserId = m.UserId
- RETURN(@NumOnline)
- END'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_GetAllUsers] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetAllUsers]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByName] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_FindUsersByName]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByEmail] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_FindUsersByEmail]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_CreateUser] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_CreateUser]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
- /****** Object: StoredProcedure [dbo].[aspnet_AnyDataInTables] Script Date: 04/09/2012 20:01:31 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER OFF
- GO
- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_AnyDataInTables]') AND type in (N'P', N'PC'))
- BEGIN
- EXEC dbo.sp_executesql @statement = N'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'
- END
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement