mfwAecZr

1IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Membership_BasicAccess')
2EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Membership_BasicAccess] AUTHORIZATION [aspnet_Membership_BasicAccess]'
3
4GO
5IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Membership_FullAccess')
6EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Membership_FullAccess] AUTHORIZATION [aspnet_Membership_FullAccess]'
7
8GO
9IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Membership_ReportingAccess')
10EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Membership_ReportingAccess] AUTHORIZATION [aspnet_Membership_ReportingAccess]'
11
12GO
13IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Personalization_BasicAccess')
14EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Personalization_BasicAccess] AUTHORIZATION [aspnet_Personalization_BasicAccess]'
15
16GO
17IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Personalization_FullAccess')
18EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Personalization_FullAccess] AUTHORIZATION [aspnet_Personalization_FullAccess]'
19
20GO
21IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Personalization_ReportingAccess')
22EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Personalization_ReportingAccess] AUTHORIZATION [aspnet_Personalization_ReportingAccess]'
23
24GO
25IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Profile_BasicAccess')
26EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Profile_BasicAccess] AUTHORIZATION [aspnet_Profile_BasicAccess]'
27
28GO
29IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Profile_FullAccess')
30EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Profile_FullAccess] AUTHORIZATION [aspnet_Profile_FullAccess]'
31
32GO
33IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Profile_ReportingAccess')
34EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Profile_ReportingAccess] AUTHORIZATION [aspnet_Profile_ReportingAccess]'
35
36GO
37IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Roles_BasicAccess')
38EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Roles_BasicAccess] AUTHORIZATION [aspnet_Roles_BasicAccess]'
39
40GO
41IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Roles_FullAccess')
42EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Roles_FullAccess] AUTHORIZATION [aspnet_Roles_FullAccess]'
43
44GO
45IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_Roles_ReportingAccess')
46EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_Roles_ReportingAccess] AUTHORIZATION [aspnet_Roles_ReportingAccess]'
47
48GO
49IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'aspnet_WebEvent_FullAccess')
50EXEC sys.sp_executesql N'CREATE SCHEMA [aspnet_WebEvent_FullAccess] AUTHORIZATION [aspnet_WebEvent_FullAccess]'
51
52GO
53SET ANSI_NULLS ON
54GO
55SET QUOTED_IDENTIFIER ON
56GO
57IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_SchemaVersions]') AND type in (N'U'))
58BEGIN
59CREATE TABLE [dbo].[aspnet_SchemaVersions](
60	[Feature] [nvarchar](128) NOT NULL,
61	[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
62	[IsCurrentVersion] [bit] NOT NULL,
63PRIMARY KEY CLUSTERED 
64(
65	[Feature] ASC,
66	[CompatibleSchemaVersion] ASC
67)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
68) ON [PRIMARY]
69END
70GO
71SET ANSI_NULLS ON
72GO
73SET QUOTED_IDENTIFIER ON
74GO
75IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_WebEvent_Events]') AND type in (N'U'))
76BEGIN
77CREATE TABLE [dbo].[aspnet_WebEvent_Events](
78	[EventId] [char](32) NOT NULL,
79	[EventTimeUtc] [datetime] NOT NULL,
80	[EventTime] [datetime] NOT NULL,
81	[EventType] [nvarchar](256) NOT NULL,
82	[EventSequence] [decimal](19, 0) NOT NULL,
83	[EventOccurrence] [decimal](19, 0) NOT NULL,
84	[EventCode] [int] NOT NULL,
85	[EventDetailCode] [int] NOT NULL,
86	[Message] [nvarchar](1024) NULL,
87	[ApplicationPath] [nvarchar](256) NULL,
88	[ApplicationVirtualPath] [nvarchar](256) NULL,
89	[MachineName] [nvarchar](256) NOT NULL,
90	[RequestUrl] [nvarchar](1024) NULL,
91	[ExceptionType] [nvarchar](256) NULL,
92	[Details] [ntext] NULL,
93PRIMARY KEY CLUSTERED 
94(
95	[EventId] ASC
96)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
97) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
98END
99GO
100SET ANSI_NULLS ON
101GO
102SET QUOTED_IDENTIFIER ON
103GO
104IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hosts]') AND type in (N'U'))
105BEGIN
106CREATE TABLE [dbo].[hosts](
107	[ID] [int] NOT NULL,
108	[url] [varchar](max) NULL,
109	[PageMaster] [varchar](max) NULL,
110	[PageTitle] [varchar](max) NULL,
111	[password] [varchar](max) NULL,
112	[redirecturl] [varchar](max) NULL,
113	[redirectuser] [varchar](max) NULL,
114	[redirectpass] [varchar](max) NULL,
115 CONSTRAINT [PK_hosts] PRIMARY KEY CLUSTERED 
116(
117	[ID] ASC
118)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
119) ON [PRIMARY]
120END
121GO
122SET ANSI_NULLS ON
123GO
124SET QUOTED_IDENTIFIER OFF
125GO
126IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Setup_RestorePermissions]') AND type in (N'P', N'PC'))
127BEGIN
128EXEC dbo.sp_executesql @statement = N'
129CREATE PROCEDURE [dbo].[aspnet_Setup_RestorePermissions]
130    @name   sysname
131AS
132BEGIN
133    DECLARE @object sysname
134    DECLARE @protectType char(10)
135    DECLARE @action varchar(60)
136    DECLARE @grantee sysname
137    DECLARE @cmd nvarchar(500)
138    DECLARE c1 cursor FORWARD_ONLY FOR
139        SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
140
141    OPEN c1
142
143    FETCH c1 INTO @object, @protectType, @action, @grantee
144    WHILE (@@fetch_status = 0)
145    BEGIN
146        SET @cmd = @protectType + '' '' + @action + '' on '' + @object + '' TO ['' + @grantee + '']''
147        EXEC (@cmd)
148        FETCH c1 INTO @object, @protectType, @action, @grantee
149    END
150
151    CLOSE c1
152    DEALLOCATE c1
153END' 
154END
155GO
156SET ANSI_NULLS ON
157GO
158SET QUOTED_IDENTIFIER OFF
159GO
160IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Setup_RemoveAllRoleMembers]') AND type in (N'P', N'PC'))
161BEGIN
162EXEC dbo.sp_executesql @statement = N'
163CREATE PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers]
164    @name   sysname
165AS
166BEGIN
167    CREATE TABLE #aspnet_RoleMembers
168    (
169        Group_name      sysname,
170        Group_id        smallint,
171        Users_in_group  sysname,
172        User_id         smallint
173    )
174
175    INSERT INTO #aspnet_RoleMembers
176    EXEC sp_helpuser @name
177
178    DECLARE @user_id smallint
179    DECLARE @cmd nvarchar(500)
180    DECLARE c1 cursor FORWARD_ONLY FOR
181        SELECT User_id FROM #aspnet_RoleMembers
182
183    OPEN c1
184
185    FETCH c1 INTO @user_id
186    WHILE (@@fetch_status = 0)
187    BEGIN
188        SET @cmd = ''EXEC sp_droprolemember '' + '''''''' + @name + '''''', '''''' + USER_NAME(@user_id) + ''''''''
189        EXEC (@cmd)
190        FETCH c1 INTO @user_id
191    END
192
193    CLOSE c1
194    DEALLOCATE c1
195END' 
196END
197GO
198SET ANSI_NULLS ON
199GO
200SET QUOTED_IDENTIFIER ON
201GO
202IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Applications]') AND type in (N'U'))
203BEGIN
204CREATE TABLE [dbo].[aspnet_Applications](
205	[ApplicationName] [nvarchar](256) NOT NULL,
206	[LoweredApplicationName] [nvarchar](256) NOT NULL,
207	[ApplicationId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
208	[Description] [nvarchar](256) NULL,
209PRIMARY KEY NONCLUSTERED 
210(
211	[ApplicationId] ASC
212)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
213UNIQUE NONCLUSTERED 
214(
215	[ApplicationName] ASC
216)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
217UNIQUE NONCLUSTERED 
218(
219	[LoweredApplicationName] ASC
220)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
221) ON [PRIMARY]
222END
223GO
224SET ANSI_NULLS ON
225GO
226SET QUOTED_IDENTIFIER ON
227GO
228IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership]') AND type in (N'U'))
229BEGIN
230CREATE TABLE [dbo].[aspnet_Membership](
231	[ApplicationId] [uniqueidentifier] NOT NULL,
232	[UserId] [uniqueidentifier] NOT NULL,
233	[Password] [nvarchar](128) NOT NULL,
234	[PasswordFormat] [int] NOT NULL DEFAULT ((0)),
235	[PasswordSalt] [nvarchar](128) NOT NULL,
236	[MobilePIN] [nvarchar](16) NULL,
237	[Email] [nvarchar](256) NULL,
238	[LoweredEmail] [nvarchar](256) NULL,
239	[PasswordQuestion] [nvarchar](256) NULL,
240	[PasswordAnswer] [nvarchar](128) NULL,
241	[IsApproved] [bit] NOT NULL,
242	[IsLockedOut] [bit] NOT NULL,
243	[CreateDate] [datetime] NOT NULL,
244	[LastLoginDate] [datetime] NOT NULL,
245	[LastPasswordChangedDate] [datetime] NOT NULL,
246	[LastLockoutDate] [datetime] NOT NULL,
247	[FailedPasswordAttemptCount] [int] NOT NULL,
248	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
249	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
250	[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
251	[Comment] [ntext] NULL,
252PRIMARY KEY NONCLUSTERED 
253(
254	[UserId] ASC
255)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
256) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
257END
258GO
259SET ANSI_NULLS ON
260GO
261SET QUOTED_IDENTIFIER ON
262GO
263IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Profile]') AND type in (N'U'))
264BEGIN
265CREATE TABLE [dbo].[aspnet_Profile](
266	[UserId] [uniqueidentifier] NOT NULL,
267	[PropertyNames] [ntext] NOT NULL,
268	[PropertyValuesString] [ntext] NOT NULL,
269	[PropertyValuesBinary] [image] NOT NULL,
270	[LastUpdatedDate] [datetime] NOT NULL,
271PRIMARY KEY CLUSTERED 
272(
273	[UserId] ASC
274)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
275) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
276END
277GO
278SET ANSI_NULLS ON
279GO
280SET QUOTED_IDENTIFIER ON
281GO
282IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles]') AND type in (N'U'))
283BEGIN
284CREATE TABLE [dbo].[aspnet_UsersInRoles](
285	[UserId] [uniqueidentifier] NOT NULL,
286	[RoleId] [uniqueidentifier] NOT NULL,
287PRIMARY KEY CLUSTERED 
288(
289	[UserId] ASC,
290	[RoleId] ASC
291)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
292) ON [PRIMARY]
293END
294GO
295SET ANSI_NULLS ON
296GO
297SET QUOTED_IDENTIFIER ON
298GO
299IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationPerUser]') AND type in (N'U'))
300BEGIN
301CREATE TABLE [dbo].[aspnet_PersonalizationPerUser](
302	[Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
303	[PathId] [uniqueidentifier] NULL,
304	[UserId] [uniqueidentifier] NULL,
305	[PageSettings] [image] NOT NULL,
306	[LastUpdatedDate] [datetime] NOT NULL,
307PRIMARY KEY NONCLUSTERED 
308(
309	[Id] ASC
310)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
311) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
312END
313GO
314SET ANSI_NULLS ON
315GO
316SET QUOTED_IDENTIFIER ON
317GO
318IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAllUsers]') AND type in (N'U'))
319BEGIN
320CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers](
321	[PathId] [uniqueidentifier] NOT NULL,
322	[PageSettings] [image] NOT NULL,
323	[LastUpdatedDate] [datetime] NOT NULL,
324PRIMARY KEY CLUSTERED 
325(
326	[PathId] ASC
327)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
328) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
329END
330GO
331SET ANSI_NULLS ON
332GO
333SET QUOTED_IDENTIFIER ON
334GO
335IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles]') AND type in (N'U'))
336BEGIN
337CREATE TABLE [dbo].[aspnet_Roles](
338	[ApplicationId] [uniqueidentifier] NOT NULL,
339	[RoleId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
340	[RoleName] [nvarchar](256) NOT NULL,
341	[LoweredRoleName] [nvarchar](256) NOT NULL,
342	[Description] [nvarchar](256) NULL,
343PRIMARY KEY NONCLUSTERED 
344(
345	[RoleId] ASC
346)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
347) ON [PRIMARY]
348END
349GO
350SET ANSI_NULLS ON
351GO
352SET QUOTED_IDENTIFIER ON
353GO
354IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Paths]') AND type in (N'U'))
355BEGIN
356CREATE TABLE [dbo].[aspnet_Paths](
357	[ApplicationId] [uniqueidentifier] NOT NULL,
358	[PathId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
359	[Path] [nvarchar](256) NOT NULL,
360	[LoweredPath] [nvarchar](256) NOT NULL,
361PRIMARY KEY NONCLUSTERED 
362(
363	[PathId] ASC
364)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
365) ON [PRIMARY]
366END
367GO
368SET ANSI_NULLS ON
369GO
370SET QUOTED_IDENTIFIER ON
371GO
372IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users]') AND type in (N'U'))
373BEGIN
374CREATE TABLE [dbo].[aspnet_Users](
375	[ApplicationId] [uniqueidentifier] NOT NULL,
376	[UserId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
377	[UserName] [nvarchar](256) NOT NULL,
378	[LoweredUserName] [nvarchar](256) NOT NULL,
379	[MobileAlias] [nvarchar](16) NULL DEFAULT (NULL),
380	[IsAnonymous] [bit] NOT NULL DEFAULT ((0)),
381	[LastActivityDate] [datetime] NOT NULL,
382PRIMARY KEY NONCLUSTERED 
383(
384	[UserId] ASC
385)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
386) ON [PRIMARY]
387END
388GO
389SET ANSI_NULLS ON
390GO
391SET QUOTED_IDENTIFIER OFF
392GO
393IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users_CreateUser]') AND type in (N'P', N'PC'))
394BEGIN
395EXEC dbo.sp_executesql @statement = N'
396CREATE PROCEDURE [dbo].[aspnet_Users_CreateUser]
397    @ApplicationId    uniqueidentifier,
398    @UserName         nvarchar(256),
399    @IsUserAnonymous  bit,
400    @LastActivityDate DATETIME,
401    @UserId           uniqueidentifier OUTPUT
402AS
403BEGIN
404    IF( @UserId IS NULL )
405        SELECT @UserId = NEWID()
406    ELSE
407    BEGIN
408        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
409                    WHERE @UserId = UserId ) )
410            RETURN -1
411    END
412
413    INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
414    VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
415
416    RETURN 0
417END' 
418END
419GO
420SET ANSI_NULLS ON
421GO
422SET QUOTED_IDENTIFIER OFF
423GO
424IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND type in (N'P', N'PC'))
425BEGIN
426EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
427    @ApplicationName  nvarchar(256),
428    @UserName         nvarchar(256),
429    @TablesToDeleteFrom int,
430    @NumTablesDeletedFrom int OUTPUT
431AS
432BEGIN
433    DECLARE @UserId               uniqueidentifier
434    SELECT  @UserId               = NULL
435    SELECT  @NumTablesDeletedFrom = 0
436
437    DECLARE @TranStarted   bit
438    SET @TranStarted = 0
439
440    IF( @@TRANCOUNT = 0 )
441    BEGIN
442	    BEGIN TRANSACTION
443	    SET @TranStarted = 1
444    END
445    ELSE
446	SET @TranStarted = 0
447
448    DECLARE @ErrorCode   int
449    DECLARE @RowCount    int
450
451    SET @ErrorCode = 0
452    SET @RowCount  = 0
453
454    SELECT  @UserId = u.UserId
455    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a
456    WHERE   u.LoweredUserName       = LOWER(@UserName)
457        AND u.ApplicationId         = a.ApplicationId
458        AND LOWER(@ApplicationName) = a.LoweredApplicationName
459
460    IF (@UserId IS NULL)
461    BEGIN
462        GOTO Cleanup
463    END
464
465    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
466    IF ((@TablesToDeleteFrom & 1) <> 0 AND
467        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
468    BEGIN
469        DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
470
471        SELECT @ErrorCode = @@ERROR,
472               @RowCount = @@ROWCOUNT
473
474        IF( @ErrorCode <> 0 )
475            GOTO Cleanup
476
477        IF (@RowCount <> 0)
478            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
479    END
480
481    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
482    IF ((@TablesToDeleteFrom & 2) <> 0  AND
483        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )
484    BEGIN
485        DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
486
487        SELECT @ErrorCode = @@ERROR,
488                @RowCount = @@ROWCOUNT
489
490        IF( @ErrorCode <> 0 )
491            GOTO Cleanup
492
493        IF (@RowCount <> 0)
494            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
495    END
496
497    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
498    IF ((@TablesToDeleteFrom & 4) <> 0  AND
499        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
500    BEGIN
501        DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
502
503        SELECT @ErrorCode = @@ERROR,
504                @RowCount = @@ROWCOUNT
505
506        IF( @ErrorCode <> 0 )
507            GOTO Cleanup
508
509        IF (@RowCount <> 0)
510            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
511    END
512
513    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
514    IF ((@TablesToDeleteFrom & 8) <> 0  AND
515        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
516    BEGIN
517        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
518
519        SELECT @ErrorCode = @@ERROR,
520                @RowCount = @@ROWCOUNT
521
522        IF( @ErrorCode <> 0 )
523            GOTO Cleanup
524
525        IF (@RowCount <> 0)
526            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
527    END
528
529    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
530    IF ((@TablesToDeleteFrom & 1) <> 0 AND
531        (@TablesToDeleteFrom & 2) <> 0 AND
532        (@TablesToDeleteFrom & 4) <> 0 AND
533        (@TablesToDeleteFrom & 8) <> 0 AND
534        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
535    BEGIN
536        DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
537
538        SELECT @ErrorCode = @@ERROR,
539                @RowCount = @@ROWCOUNT
540
541        IF( @ErrorCode <> 0 )
542            GOTO Cleanup
543
544        IF (@RowCount <> 0)
545            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
546    END
547
548    IF( @TranStarted = 1 )
549    BEGIN
550	    SET @TranStarted = 0
551	    COMMIT TRANSACTION
552    END
553
554    RETURN 0
555
556Cleanup:
557    SET @NumTablesDeletedFrom = 0
558
559    IF( @TranStarted = 1 )
560    BEGIN
561        SET @TranStarted = 0
562	    ROLLBACK TRANSACTION
563    END
564
565    RETURN @ErrorCode
566
567END' 
568END
569GO
570SET ANSI_NULLS ON
571GO
572SET QUOTED_IDENTIFIER OFF
573GO
574IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_AnyDataInTables]') AND type in (N'P', N'PC'))
575BEGIN
576EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables]
577    @TablesToCheck int
578AS
579BEGIN
580    -- Check Membership table if (@TablesToCheck & 1) is set
581    IF ((@TablesToCheck & 1) <> 0 AND
582        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
583    BEGIN
584        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
585        BEGIN
586            SELECT N''aspnet_Membership''
587            RETURN
588        END
589    END
590
591    -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
592    IF ((@TablesToCheck & 2) <> 0  AND
593        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Roles'') AND (type = ''V''))) )
594    BEGIN
595        IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
596        BEGIN
597            SELECT N''aspnet_Roles''
598            RETURN
599        END
600    END
601
602    -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
603    IF ((@TablesToCheck & 4) <> 0  AND
604        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
605    BEGIN
606        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
607        BEGIN
608            SELECT N''aspnet_Profile''
609            RETURN
610        END
611    END
612
613    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
614    IF ((@TablesToCheck & 8) <> 0  AND
615        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
616    BEGIN
617        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
618        BEGIN
619            SELECT N''aspnet_PersonalizationPerUser''
620            RETURN
621        END
622    END
623
624    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
625    IF ((@TablesToCheck & 16) <> 0  AND
626        (EXISTS (SELECT name FROM sysobjects WHERE (name = N''aspnet_WebEvent_LogEvent'') AND (type = ''P''))) )
627    BEGIN
628        IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
629        BEGIN
630            SELECT N''aspnet_WebEvent_Events''
631            RETURN
632        END
633    END
634
635    -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
636    IF ((@TablesToCheck & 1) <> 0 AND
637        (@TablesToCheck & 2) <> 0 AND
638        (@TablesToCheck & 4) <> 0 AND
639        (@TablesToCheck & 8) <> 0 AND
640        (@TablesToCheck & 32) <> 0 AND
641        (@TablesToCheck & 128) <> 0 AND
642        (@TablesToCheck & 256) <> 0 AND
643        (@TablesToCheck & 512) <> 0 AND
644        (@TablesToCheck & 1024) <> 0)
645    BEGIN
646        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
647        BEGIN
648            SELECT N''aspnet_Users''
649            RETURN
650        END
651        IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
652        BEGIN
653            SELECT N''aspnet_Applications''
654            RETURN
655        END
656    END
657END' 
658END
659GO
660SET ANSI_NULLS ON
661GO
662SET QUOTED_IDENTIFIER OFF
663GO
664IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByName]') AND type in (N'P', N'PC'))
665BEGIN
666EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
667    @ApplicationName      nvarchar(256),
668    @UserName             nvarchar(256),
669    @CurrentTimeUtc       datetime,
670    @UpdateLastActivity   bit = 0
671AS
672BEGIN
673    DECLARE @UserId uniqueidentifier
674
675    IF (@UpdateLastActivity = 1)
676    BEGIN
677        -- select user ID from aspnet_users table
678        SELECT TOP 1 @UserId = u.UserId
679        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
680        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
681                u.ApplicationId = a.ApplicationId    AND
682                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
683
684        IF (@@ROWCOUNT = 0) -- Username not found
685            RETURN -1
686
687        UPDATE   dbo.aspnet_Users
688        SET      LastActivityDate = @CurrentTimeUtc
689        WHERE    @UserId = UserId
690
691        SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
692                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
693                u.UserId, m.IsLockedOut, m.LastLockoutDate
694        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
695        WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
696    END
697    ELSE
698    BEGIN
699        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
700                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
701                u.UserId, m.IsLockedOut,m.LastLockoutDate
702        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
703        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
704                u.ApplicationId = a.ApplicationId    AND
705                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
706
707        IF (@@ROWCOUNT = 0) -- Username not found
708            RETURN -1
709    END
710
711    RETURN 0
712END' 
713END
714GO
715SET ANSI_NULLS ON
716GO
717SET QUOTED_IDENTIFIER OFF
718GO
719IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByUserId]') AND type in (N'P', N'PC'))
720BEGIN
721EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByUserId]
722    @UserId               uniqueidentifier,
723    @CurrentTimeUtc       datetime,
724    @UpdateLastActivity   bit = 0
725AS
726BEGIN
727    IF ( @UpdateLastActivity = 1 )
728    BEGIN
729        UPDATE   dbo.aspnet_Users
730        SET      LastActivityDate = @CurrentTimeUtc
731        FROM     dbo.aspnet_Users
732        WHERE    @UserId = UserId
733
734        IF ( @@ROWCOUNT = 0 ) -- User ID not found
735            RETURN -1
736    END
737
738    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
739            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
740            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
741            m.LastLockoutDate
742    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
743    WHERE   @UserId = u.UserId AND u.UserId = m.UserId
744
745    IF ( @@ROWCOUNT = 0 ) -- User ID not found
746       RETURN -1
747
748    RETURN 0
749END' 
750END
751GO
752SET ANSI_NULLS ON
753GO
754SET QUOTED_IDENTIFIER OFF
755GO
756IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByEmail]') AND type in (N'P', N'PC'))
757BEGIN
758EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
759    @ApplicationName  nvarchar(256),
760    @Email            nvarchar(256)
761AS
762BEGIN
763    IF( @Email IS NULL )
764        SELECT  u.UserName
765        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
766        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
767                u.ApplicationId = a.ApplicationId    AND
768                u.UserId = m.UserId AND
769                m.LoweredEmail IS NULL
770    ELSE
771        SELECT  u.UserName
772        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
773        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
774                u.ApplicationId = a.ApplicationId    AND
775                u.UserId = m.UserId AND
776                LOWER(@Email) = m.LoweredEmail
777
778    IF (@@rowcount = 0)
779        RETURN(1)
780    RETURN(0)
781END' 
782END
783GO
784SET ANSI_NULLS ON
785GO
786SET QUOTED_IDENTIFIER OFF
787GO
788IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetPasswordWithFormat]') AND type in (N'P', N'PC'))
789BEGIN
790EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetPasswordWithFormat]
791    @ApplicationName                nvarchar(256),
792    @UserName                       nvarchar(256),
793    @UpdateLastLoginActivityDate    bit,
794    @CurrentTimeUtc                 datetime
795AS
796BEGIN
797    DECLARE @IsLockedOut                        bit
798    DECLARE @UserId                             uniqueidentifier
799    DECLARE @Password                           nvarchar(128)
800    DECLARE @PasswordSalt                       nvarchar(128)
801    DECLARE @PasswordFormat                     int
802    DECLARE @FailedPasswordAttemptCount         int
803    DECLARE @FailedPasswordAnswerAttemptCount   int
804    DECLARE @IsApproved                         bit
805    DECLARE @LastActivityDate                   datetime
806    DECLARE @LastLoginDate                      datetime
807
808    SELECT  @UserId          = NULL
809
810    SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
811            @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
812		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
813            @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
814    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
815    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
816            u.ApplicationId = a.ApplicationId    AND
817            u.UserId = m.UserId AND
818            LOWER(@UserName) = u.LoweredUserName
819
820    IF (@UserId IS NULL)
821        RETURN 1
822
823    IF (@IsLockedOut = 1)
824        RETURN 99
825
826    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
827             @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate
828
829    IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
830    BEGIN
831        UPDATE  dbo.aspnet_Membership
832        SET     LastLoginDate = @CurrentTimeUtc
833        WHERE   UserId = @UserId
834
835        UPDATE  dbo.aspnet_Users
836        SET     LastActivityDate = @CurrentTimeUtc
837        WHERE   @UserId = UserId
838    END
839
840
841    RETURN 0
842END' 
843END
844GO
845SET ANSI_NULLS ON
846GO
847SET QUOTED_IDENTIFIER OFF
848GO
849IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UpdateUserInfo]') AND type in (N'P', N'PC'))
850BEGIN
851EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUserInfo]
852    @ApplicationName                nvarchar(256),
853    @UserName                       nvarchar(256),
854    @IsPasswordCorrect              bit,
855    @UpdateLastLoginActivityDate    bit,
856    @MaxInvalidPasswordAttempts     int,
857    @PasswordAttemptWindow          int,
858    @CurrentTimeUtc                 datetime,
859    @LastLoginDate                  datetime,
860    @LastActivityDate               datetime
861AS
862BEGIN
863    DECLARE @UserId                                 uniqueidentifier
864    DECLARE @IsApproved                             bit
865    DECLARE @IsLockedOut                            bit
866    DECLARE @LastLockoutDate                        datetime
867    DECLARE @FailedPasswordAttemptCount             int
868    DECLARE @FailedPasswordAttemptWindowStart       datetime
869    DECLARE @FailedPasswordAnswerAttemptCount       int
870    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
871
872    DECLARE @ErrorCode     int
873    SET @ErrorCode = 0
874
875    DECLARE @TranStarted   bit
876    SET @TranStarted = 0
877
878    IF( @@TRANCOUNT = 0 )
879    BEGIN
880	    BEGIN TRANSACTION
881	    SET @TranStarted = 1
882    END
883    ELSE
884    	SET @TranStarted = 0
885
886    SELECT  @UserId = u.UserId,
887            @IsApproved = m.IsApproved,
888            @IsLockedOut = m.IsLockedOut,
889            @LastLockoutDate = m.LastLockoutDate,
890            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
891            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
892            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
893            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
894    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
895    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
896            u.ApplicationId = a.ApplicationId    AND
897            u.UserId = m.UserId AND
898            LOWER(@UserName) = u.LoweredUserName
899
900    IF ( @@rowcount = 0 )
901    BEGIN
902        SET @ErrorCode = 1
903        GOTO Cleanup
904    END
905
906    IF( @IsLockedOut = 1 )
907    BEGIN
908        GOTO Cleanup
909    END
910
911    IF( @IsPasswordCorrect = 0 )
912    BEGIN
913        IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
914        BEGIN
915            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
916            SET @FailedPasswordAttemptCount = 1
917        END
918        ELSE
919        BEGIN
920            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
921            SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
922        END
923
924        BEGIN
925            IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
926            BEGIN
927                SET @IsLockedOut = 1
928                SET @LastLockoutDate = @CurrentTimeUtc
929            END
930        END
931    END
932    ELSE
933    BEGIN
934        IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
935        BEGIN
936            SET @FailedPasswordAttemptCount = 0
937            SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
938            SET @FailedPasswordAnswerAttemptCount = 0
939            SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
940            SET @LastLockoutDate = CONVERT( datetime, ''17540101'', 112 )
941        END
942    END
943
944    IF( @UpdateLastLoginActivityDate = 1 )
945    BEGIN
946        UPDATE  dbo.aspnet_Users
947        SET     LastActivityDate = @LastActivityDate
948        WHERE   @UserId = UserId
949
950        IF( @@ERROR <> 0 )
951        BEGIN
952            SET @ErrorCode = -1
953            GOTO Cleanup
954        END
955
956        UPDATE  dbo.aspnet_Membership
957        SET     LastLoginDate = @LastLoginDate
958        WHERE   UserId = @UserId
959
960        IF( @@ERROR <> 0 )
961        BEGIN
962            SET @ErrorCode = -1
963            GOTO Cleanup
964        END
965    END
966
967
968    UPDATE dbo.aspnet_Membership
969    SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
970        FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
971        FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
972        FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
973        FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
974    WHERE @UserId = UserId
975
976    IF( @@ERROR <> 0 )
977    BEGIN
978        SET @ErrorCode = -1
979        GOTO Cleanup
980    END
981
982    IF( @TranStarted = 1 )
983    BEGIN
984	SET @TranStarted = 0
985	COMMIT TRANSACTION
986    END
987
988    RETURN @ErrorCode
989
990Cleanup:
991
992    IF( @TranStarted = 1 )
993    BEGIN
994        SET @TranStarted = 0
995    	ROLLBACK TRANSACTION
996    END
997
998    RETURN @ErrorCode
999
1000END' 
1001END
1002GO
1003SET ANSI_NULLS ON
1004GO
1005SET QUOTED_IDENTIFIER OFF
1006GO
1007IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetPassword]') AND type in (N'P', N'PC'))
1008BEGIN
1009EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetPassword]
1010    @ApplicationName                nvarchar(256),
1011    @UserName                       nvarchar(256),
1012    @MaxInvalidPasswordAttempts     int,
1013    @PasswordAttemptWindow          int,
1014    @CurrentTimeUtc                 datetime,
1015    @PasswordAnswer                 nvarchar(128) = NULL
1016AS
1017BEGIN
1018    DECLARE @UserId                                 uniqueidentifier
1019    DECLARE @PasswordFormat                         int
1020    DECLARE @Password                               nvarchar(128)
1021    DECLARE @passAns                                nvarchar(128)
1022    DECLARE @IsLockedOut                            bit
1023    DECLARE @LastLockoutDate                        datetime
1024    DECLARE @FailedPasswordAttemptCount             int
1025    DECLARE @FailedPasswordAttemptWindowStart       datetime
1026    DECLARE @FailedPasswordAnswerAttemptCount       int
1027    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1028
1029    DECLARE @ErrorCode     int
1030    SET @ErrorCode = 0
1031
1032    DECLARE @TranStarted   bit
1033    SET @TranStarted = 0
1034
1035    IF( @@TRANCOUNT = 0 )
1036    BEGIN
1037	    BEGIN TRANSACTION
1038	    SET @TranStarted = 1
1039    END
1040    ELSE
1041    	SET @TranStarted = 0
1042
1043    SELECT  @UserId = u.UserId,
1044            @Password = m.Password,
1045            @passAns = m.PasswordAnswer,
1046            @PasswordFormat = m.PasswordFormat,
1047            @IsLockedOut = m.IsLockedOut,
1048            @LastLockoutDate = m.LastLockoutDate,
1049            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
1050            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
1051            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
1052            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
1053    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
1054    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
1055            u.ApplicationId = a.ApplicationId    AND
1056            u.UserId = m.UserId AND
1057            LOWER(@UserName) = u.LoweredUserName
1058
1059    IF ( @@rowcount = 0 )
1060    BEGIN
1061        SET @ErrorCode = 1
1062        GOTO Cleanup
1063    END
1064
1065    IF( @IsLockedOut = 1 )
1066    BEGIN
1067        SET @ErrorCode = 99
1068        GOTO Cleanup
1069    END
1070
1071    IF ( NOT( @PasswordAnswer IS NULL ) )
1072    BEGIN
1073        IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
1074        BEGIN
1075            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
1076            BEGIN
1077                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1078                SET @FailedPasswordAnswerAttemptCount = 1
1079            END
1080            ELSE
1081            BEGIN
1082                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
1083                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1084            END
1085
1086            BEGIN
1087                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
1088                BEGIN
1089                    SET @IsLockedOut = 1
1090                    SET @LastLockoutDate = @CurrentTimeUtc
1091                END
1092            END
1093
1094            SET @ErrorCode = 3
1095        END
1096        ELSE
1097        BEGIN
1098            IF( @FailedPasswordAnswerAttemptCount > 0 )
1099            BEGIN
1100                SET @FailedPasswordAnswerAttemptCount = 0
1101                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
1102            END
1103        END
1104
1105        UPDATE dbo.aspnet_Membership
1106        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1107            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1108            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1109            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1110            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1111        WHERE @UserId = UserId
1112
1113        IF( @@ERROR <> 0 )
1114        BEGIN
1115            SET @ErrorCode = -1
1116            GOTO Cleanup
1117        END
1118    END
1119
1120    IF( @TranStarted = 1 )
1121    BEGIN
1122	SET @TranStarted = 0
1123	COMMIT TRANSACTION
1124    END
1125
1126    IF( @ErrorCode = 0 )
1127        SELECT @Password, @PasswordFormat
1128
1129    RETURN @ErrorCode
1130
1131Cleanup:
1132
1133    IF( @TranStarted = 1 )
1134    BEGIN
1135        SET @TranStarted = 0
1136    	ROLLBACK TRANSACTION
1137    END
1138
1139    RETURN @ErrorCode
1140
1141END' 
1142END
1143GO
1144SET ANSI_NULLS ON
1145GO
1146SET QUOTED_IDENTIFIER OFF
1147GO
1148IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_SetPassword]') AND type in (N'P', N'PC'))
1149BEGIN
1150EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_SetPassword]
1151    @ApplicationName  nvarchar(256),
1152    @UserName         nvarchar(256),
1153    @NewPassword      nvarchar(128),
1154    @PasswordSalt     nvarchar(128),
1155    @CurrentTimeUtc   datetime,
1156    @PasswordFormat   int = 0
1157AS
1158BEGIN
1159    DECLARE @UserId uniqueidentifier
1160    SELECT  @UserId = NULL
1161    SELECT  @UserId = u.UserId
1162    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1163    WHERE   LoweredUserName = LOWER(@UserName) AND
1164            u.ApplicationId = a.ApplicationId  AND
1165            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1166            u.UserId = m.UserId
1167
1168    IF (@UserId IS NULL)
1169        RETURN(1)
1170
1171    UPDATE dbo.aspnet_Membership
1172    SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
1173        LastPasswordChangedDate = @CurrentTimeUtc
1174    WHERE @UserId = UserId
1175    RETURN(0)
1176END' 
1177END
1178GO
1179SET ANSI_NULLS ON
1180GO
1181SET QUOTED_IDENTIFIER OFF
1182GO
1183IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_ResetPassword]') AND type in (N'P', N'PC'))
1184BEGIN
1185EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_ResetPassword]
1186    @ApplicationName             nvarchar(256),
1187    @UserName                    nvarchar(256),
1188    @NewPassword                 nvarchar(128),
1189    @MaxInvalidPasswordAttempts  int,
1190    @PasswordAttemptWindow       int,
1191    @PasswordSalt                nvarchar(128),
1192    @CurrentTimeUtc              datetime,
1193    @PasswordFormat              int = 0,
1194    @PasswordAnswer              nvarchar(128) = NULL
1195AS
1196BEGIN
1197    DECLARE @IsLockedOut                            bit
1198    DECLARE @LastLockoutDate                        datetime
1199    DECLARE @FailedPasswordAttemptCount             int
1200    DECLARE @FailedPasswordAttemptWindowStart       datetime
1201    DECLARE @FailedPasswordAnswerAttemptCount       int
1202    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1203
1204    DECLARE @UserId                                 uniqueidentifier
1205    SET     @UserId = NULL
1206
1207    DECLARE @ErrorCode     int
1208    SET @ErrorCode = 0
1209
1210    DECLARE @TranStarted   bit
1211    SET @TranStarted = 0
1212
1213    IF( @@TRANCOUNT = 0 )
1214    BEGIN
1215	    BEGIN TRANSACTION
1216	    SET @TranStarted = 1
1217    END
1218    ELSE
1219    	SET @TranStarted = 0
1220
1221    SELECT  @UserId = u.UserId
1222    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1223    WHERE   LoweredUserName = LOWER(@UserName) AND
1224            u.ApplicationId = a.ApplicationId  AND
1225            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1226            u.UserId = m.UserId
1227
1228    IF ( @UserId IS NULL )
1229    BEGIN
1230        SET @ErrorCode = 1
1231        GOTO Cleanup
1232    END
1233
1234    SELECT @IsLockedOut = IsLockedOut,
1235           @LastLockoutDate = LastLockoutDate,
1236           @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
1237           @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
1238           @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
1239           @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
1240    FROM dbo.aspnet_Membership WITH ( UPDLOCK )
1241    WHERE @UserId = UserId
1242
1243    IF( @IsLockedOut = 1 )
1244    BEGIN
1245        SET @ErrorCode = 99
1246        GOTO Cleanup
1247    END
1248
1249    UPDATE dbo.aspnet_Membership
1250    SET    Password = @NewPassword,
1251           LastPasswordChangedDate = @CurrentTimeUtc,
1252           PasswordFormat = @PasswordFormat,
1253           PasswordSalt = @PasswordSalt
1254    WHERE  @UserId = UserId AND
1255           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
1256
1257    IF ( @@ROWCOUNT = 0 )
1258        BEGIN
1259            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
1260            BEGIN
1261                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1262                SET @FailedPasswordAnswerAttemptCount = 1
1263            END
1264            ELSE
1265            BEGIN
1266                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1267                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
1268            END
1269
1270            BEGIN
1271                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
1272                BEGIN
1273                    SET @IsLockedOut = 1
1274                    SET @LastLockoutDate = @CurrentTimeUtc
1275                END
1276            END
1277
1278            SET @ErrorCode = 3
1279        END
1280    ELSE
1281        BEGIN
1282            IF( @FailedPasswordAnswerAttemptCount > 0 )
1283            BEGIN
1284                SET @FailedPasswordAnswerAttemptCount = 0
1285                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
1286            END
1287        END
1288
1289    IF( NOT ( @PasswordAnswer IS NULL ) )
1290    BEGIN
1291        UPDATE dbo.aspnet_Membership
1292        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1293            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1294            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1295            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1296            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1297        WHERE @UserId = UserId
1298
1299        IF( @@ERROR <> 0 )
1300        BEGIN
1301            SET @ErrorCode = -1
1302            GOTO Cleanup
1303        END
1304    END
1305
1306    IF( @TranStarted = 1 )
1307    BEGIN
1308	SET @TranStarted = 0
1309	COMMIT TRANSACTION
1310    END
1311
1312    RETURN @ErrorCode
1313
1314Cleanup:
1315
1316    IF( @TranStarted = 1 )
1317    BEGIN
1318        SET @TranStarted = 0
1319    	ROLLBACK TRANSACTION
1320    END
1321
1322    RETURN @ErrorCode
1323
1324END' 
1325END
1326GO
1327SET ANSI_NULLS ON
1328GO
1329SET QUOTED_IDENTIFIER OFF
1330GO
1331IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UnlockUser]') AND type in (N'P', N'PC'))
1332BEGIN
1333EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_UnlockUser]
1334    @ApplicationName                         nvarchar(256),
1335    @UserName                                nvarchar(256)
1336AS
1337BEGIN
1338    DECLARE @UserId uniqueidentifier
1339    SELECT  @UserId = NULL
1340    SELECT  @UserId = u.UserId
1341    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1342    WHERE   LoweredUserName = LOWER(@UserName) AND
1343            u.ApplicationId = a.ApplicationId  AND
1344            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1345            u.UserId = m.UserId
1346
1347    IF ( @UserId IS NULL )
1348        RETURN 1
1349
1350    UPDATE dbo.aspnet_Membership
1351    SET IsLockedOut = 0,
1352        FailedPasswordAttemptCount = 0,
1353        FailedPasswordAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 ),
1354        FailedPasswordAnswerAttemptCount = 0,
1355        FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 ),
1356        LastLockoutDate = CONVERT( datetime, ''17540101'', 112 )
1357    WHERE @UserId = UserId
1358
1359    RETURN 0
1360END' 
1361END
1362GO
1363SET ANSI_NULLS ON
1364GO
1365SET QUOTED_IDENTIFIER OFF
1366GO
1367IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_IsUserInRole]') AND type in (N'P', N'PC'))
1368BEGIN
1369EXEC dbo.sp_executesql @statement = N'
1370CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
1371    @ApplicationName  nvarchar(256),
1372    @UserName         nvarchar(256),
1373    @RoleName         nvarchar(256)
1374AS
1375BEGIN
1376    DECLARE @ApplicationId uniqueidentifier
1377    SELECT  @ApplicationId = NULL
1378    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1379    IF (@ApplicationId IS NULL)
1380        RETURN(2)
1381    DECLARE @UserId uniqueidentifier
1382    SELECT  @UserId = NULL
1383    DECLARE @RoleId uniqueidentifier
1384    SELECT  @RoleId = NULL
1385
1386    SELECT  @UserId = UserId
1387    FROM    dbo.aspnet_Users
1388    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
1389
1390    IF (@UserId IS NULL)
1391        RETURN(2)
1392
1393    SELECT  @RoleId = RoleId
1394    FROM    dbo.aspnet_Roles
1395    WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
1396
1397    IF (@RoleId IS NULL)
1398        RETURN(3)
1399
1400    IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
1401        RETURN(1)
1402    ELSE
1403        RETURN(0)
1404END' 
1405END
1406GO
1407SET ANSI_NULLS ON
1408GO
1409SET QUOTED_IDENTIFIER OFF
1410GO
1411IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]') AND type in (N'P', N'PC'))
1412BEGIN
1413EXEC dbo.sp_executesql @statement = N'
1414CREATE PROCEDURE [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]
1415    @ApplicationName        nvarchar(256),
1416    @ProfileAuthOptions     int,
1417    @InactiveSinceDate      datetime
1418AS
1419BEGIN
1420    DECLARE @ApplicationId uniqueidentifier
1421    SELECT  @ApplicationId = NULL
1422    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1423    IF (@ApplicationId IS NULL)
1424    BEGIN
1425        SELECT 0
1426        RETURN
1427    END
1428
1429    SELECT  COUNT(*)
1430    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
1431    WHERE   ApplicationId = @ApplicationId
1432        AND u.UserId = p.UserId
1433        AND (LastActivityDate <= @InactiveSinceDate)
1434        AND (
1435                (@ProfileAuthOptions = 2)
1436                OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
1437                OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
1438            )
1439END' 
1440END
1441GO
1442SET ANSI_NULLS ON
1443GO
1444SET QUOTED_IDENTIFIER OFF
1445GO
1446IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UpdateUser]') AND type in (N'P', N'PC'))
1447BEGIN
1448EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUser]
1449    @ApplicationName      nvarchar(256),
1450    @UserName             nvarchar(256),
1451    @Email                nvarchar(256),
1452    @Comment              ntext,
1453    @IsApproved           bit,
1454    @LastLoginDate        datetime,
1455    @LastActivityDate     datetime,
1456    @UniqueEmail          int,
1457    @CurrentTimeUtc       datetime
1458AS
1459BEGIN
1460    DECLARE @UserId uniqueidentifier
1461    DECLARE @ApplicationId uniqueidentifier
1462    SELECT  @UserId = NULL
1463    SELECT  @UserId = u.UserId, @ApplicationId = a.ApplicationId
1464    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1465    WHERE   LoweredUserName = LOWER(@UserName) AND
1466            u.ApplicationId = a.ApplicationId  AND
1467            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1468            u.UserId = m.UserId
1469
1470    IF (@UserId IS NULL)
1471        RETURN(1)
1472
1473    IF (@UniqueEmail = 1)
1474    BEGIN
1475        IF (EXISTS (SELECT *
1476                    FROM  dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
1477                    WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
1478        BEGIN
1479            RETURN(7)
1480        END
1481    END
1482
1483    DECLARE @TranStarted   bit
1484    SET @TranStarted = 0
1485
1486    IF( @@TRANCOUNT = 0 )
1487    BEGIN
1488	    BEGIN TRANSACTION
1489	    SET @TranStarted = 1
1490    END
1491    ELSE
1492	SET @TranStarted = 0
1493
1494    UPDATE dbo.aspnet_Users WITH (ROWLOCK)
1495    SET
1496         LastActivityDate = @LastActivityDate
1497    WHERE
1498       @UserId = UserId
1499
1500    IF( @@ERROR <> 0 )
1501        GOTO Cleanup
1502
1503    UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
1504    SET
1505         Email            = @Email,
1506         LoweredEmail     = LOWER(@Email),
1507         Comment          = @Comment,
1508         IsApproved       = @IsApproved,
1509         LastLoginDate    = @LastLoginDate
1510    WHERE
1511       @UserId = UserId
1512
1513    IF( @@ERROR <> 0 )
1514        GOTO Cleanup
1515
1516    IF( @TranStarted = 1 )
1517    BEGIN
1518	SET @TranStarted = 0
1519	COMMIT TRANSACTION
1520    END
1521
1522    RETURN 0
1523
1524Cleanup:
1525
1526    IF( @TranStarted = 1 )
1527    BEGIN
1528        SET @TranStarted = 0
1529    	ROLLBACK TRANSACTION
1530    END
1531
1532    RETURN -1
1533END' 
1534END
1535GO
1536SET ANSI_NULLS ON
1537GO
1538SET QUOTED_IDENTIFIER OFF
1539GO
1540IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_MembershipUsers]'))
1541EXEC dbo.sp_executesql @statement = N'
1542  CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
1543  AS SELECT [dbo].[aspnet_Membership].[UserId],
1544            [dbo].[aspnet_Membership].[PasswordFormat],
1545            [dbo].[aspnet_Membership].[MobilePIN],
1546            [dbo].[aspnet_Membership].[Email],
1547            [dbo].[aspnet_Membership].[LoweredEmail],
1548            [dbo].[aspnet_Membership].[PasswordQuestion],
1549            [dbo].[aspnet_Membership].[PasswordAnswer],
1550            [dbo].[aspnet_Membership].[IsApproved],
1551            [dbo].[aspnet_Membership].[IsLockedOut],
1552            [dbo].[aspnet_Membership].[CreateDate],
1553            [dbo].[aspnet_Membership].[LastLoginDate],
1554            [dbo].[aspnet_Membership].[LastPasswordChangedDate],
1555            [dbo].[aspnet_Membership].[LastLockoutDate],
1556            [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
1557            [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
1558            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
1559            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
1560            [dbo].[aspnet_Membership].[Comment],
1561            [dbo].[aspnet_Users].[ApplicationId],
1562            [dbo].[aspnet_Users].[UserName],
1563            [dbo].[aspnet_Users].[MobileAlias],
1564            [dbo].[aspnet_Users].[IsAnonymous],
1565            [dbo].[aspnet_Users].[LastActivityDate]
1566  FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
1567      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]
1568  ' 
1569GO
1570SET ANSI_NULLS ON
1571GO
1572SET QUOTED_IDENTIFIER OFF
1573GO
1574IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_Users]'))
1575EXEC dbo.sp_executesql @statement = N'
1576  CREATE VIEW [dbo].[vw_aspnet_Users]
1577  AS SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]
1578  FROM [dbo].[aspnet_Users]
1579  ' 
1580GO
1581SET ANSI_NULLS ON
1582GO
1583SET QUOTED_IDENTIFIER OFF
1584GO
1585IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Profile_DeleteInactiveProfiles]') AND type in (N'P', N'PC'))
1586BEGIN
1587EXEC dbo.sp_executesql @statement = N'
1588CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteInactiveProfiles]
1589    @ApplicationName        nvarchar(256),
1590    @ProfileAuthOptions     int,
1591    @InactiveSinceDate      datetime
1592AS
1593BEGIN
1594    DECLARE @ApplicationId uniqueidentifier
1595    SELECT  @ApplicationId = NULL
1596    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1597    IF (@ApplicationId IS NULL)
1598    BEGIN
1599        SELECT  0
1600        RETURN
1601    END
1602
1603    DELETE
1604    FROM    dbo.aspnet_Profile
1605    WHERE   UserId IN
1606            (   SELECT  UserId
1607                FROM    dbo.aspnet_Users u
1608                WHERE   ApplicationId = @ApplicationId
1609                        AND (LastActivityDate <= @InactiveSinceDate)
1610                        AND (
1611                                (@ProfileAuthOptions = 2)
1612                             OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
1613                             OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
1614                            )
1615            )
1616
1617    SELECT  @@ROWCOUNT
1618END' 
1619END
1620GO
1621SET ANSI_NULLS ON
1622GO
1623SET QUOTED_IDENTIFIER OFF
1624GO
1625IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Profile_GetProperties]') AND type in (N'P', N'PC'))
1626BEGIN
1627EXEC dbo.sp_executesql @statement = N'
1628CREATE PROCEDURE [dbo].[aspnet_Profile_GetProperties]
1629    @ApplicationName      nvarchar(256),
1630    @UserName             nvarchar(256),
1631    @CurrentTimeUtc       datetime
1632AS
1633BEGIN
1634    DECLARE @ApplicationId uniqueidentifier
1635    SELECT  @ApplicationId = NULL
1636    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1637    IF (@ApplicationId IS NULL)
1638        RETURN
1639
1640    DECLARE @UserId uniqueidentifier
1641    SELECT  @UserId = NULL
1642
1643    SELECT @UserId = UserId
1644    FROM   dbo.aspnet_Users
1645    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
1646
1647    IF (@UserId IS NULL)
1648        RETURN
1649    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
1650    FROM         dbo.aspnet_Profile
1651    WHERE        UserId = @UserId
1652
1653    IF (@@ROWCOUNT > 0)
1654    BEGIN
1655        UPDATE dbo.aspnet_Users
1656        SET    LastActivityDate=@CurrentTimeUtc
1657        WHERE  UserId = @UserId
1658    END
1659END' 
1660END
1661GO
1662SET ANSI_NULLS ON
1663GO
1664SET QUOTED_IDENTIFIER OFF
1665GO
1666IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]') AND type in (N'P', N'PC'))
1667BEGIN
1668EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]
1669    @ApplicationName       nvarchar(256),
1670    @UserName              nvarchar(256),
1671    @NewPasswordQuestion   nvarchar(256),
1672    @NewPasswordAnswer     nvarchar(128)
1673AS
1674BEGIN
1675    DECLARE @UserId uniqueidentifier
1676    SELECT  @UserId = NULL
1677    SELECT  @UserId = u.UserId
1678    FROM    dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
1679    WHERE   LoweredUserName = LOWER(@UserName) AND
1680            u.ApplicationId = a.ApplicationId  AND
1681            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1682            u.UserId = m.UserId
1683    IF (@UserId IS NULL)
1684    BEGIN
1685        RETURN(1)
1686    END
1687
1688    UPDATE dbo.aspnet_Membership
1689    SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
1690    WHERE  UserId=@UserId
1691    RETURN(0)
1692END' 
1693END
1694GO
1695SET ANSI_NULLS ON
1696GO
1697SET QUOTED_IDENTIFIER OFF
1698GO
1699IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetNumberOfUsersOnline]') AND type in (N'P', N'PC'))
1700BEGIN
1701EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
1702    @ApplicationName            nvarchar(256),
1703    @MinutesSinceLastInActive   int,
1704    @CurrentTimeUtc             datetime
1705AS
1706BEGIN
1707    DECLARE @DateActive datetime
1708    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)
1709
1710    DECLARE @NumOnline int
1711    SELECT  @NumOnline = COUNT(*)
1712    FROM    dbo.aspnet_Users u(NOLOCK),
1713            dbo.aspnet_Applications a(NOLOCK),
1714            dbo.aspnet_Membership m(NOLOCK)
1715    WHERE   u.ApplicationId = a.ApplicationId                  AND
1716            LastActivityDate > @DateActive                     AND
1717            a.LoweredApplicationName = LOWER(@ApplicationName) AND
1718            u.UserId = m.UserId
1719    RETURN(@NumOnline)
1720END' 
1721END
1722GO
1723SET ANSI_NULLS ON
1724GO
1725SET QUOTED_IDENTIFIER OFF
1726GO
1727IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_GetUsersInRoles]') AND type in (N'P', N'PC'))
1728BEGIN
1729EXEC dbo.sp_executesql @statement = N'
1730CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetUsersInRoles]
1731    @ApplicationName  nvarchar(256),
1732    @RoleName         nvarchar(256)
1733AS
1734BEGIN
1735    DECLARE @ApplicationId uniqueidentifier
1736    SELECT  @ApplicationId = NULL
1737    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1738    IF (@ApplicationId IS NULL)
1739        RETURN(1)
1740     DECLARE @RoleId uniqueidentifier
1741     SELECT  @RoleId = NULL
1742
1743     SELECT  @RoleId = RoleId
1744     FROM    dbo.aspnet_Roles
1745     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
1746
1747     IF (@RoleId IS NULL)
1748         RETURN(1)
1749
1750    SELECT u.UserName
1751    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
1752    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
1753    ORDER BY u.UserName
1754    RETURN(0)
1755END' 
1756END
1757GO
1758SET ANSI_NULLS ON
1759GO
1760SET QUOTED_IDENTIFIER OFF
1761GO
1762IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_FindUsersInRole]') AND type in (N'P', N'PC'))
1763BEGIN
1764EXEC dbo.sp_executesql @statement = N'
1765CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_FindUsersInRole]
1766    @ApplicationName  nvarchar(256),
1767    @RoleName         nvarchar(256),
1768    @UserNameToMatch  nvarchar(256)
1769AS
1770BEGIN
1771    DECLARE @ApplicationId uniqueidentifier
1772    SELECT  @ApplicationId = NULL
1773    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1774    IF (@ApplicationId IS NULL)
1775        RETURN(1)
1776     DECLARE @RoleId uniqueidentifier
1777     SELECT  @RoleId = NULL
1778
1779     SELECT  @RoleId = RoleId
1780     FROM    dbo.aspnet_Roles
1781     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
1782
1783     IF (@RoleId IS NULL)
1784         RETURN(1)
1785
1786    SELECT u.UserName
1787    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
1788    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
1789    ORDER BY u.UserName
1790    RETURN(0)
1791END' 
1792END
1793GO
1794SET ANSI_NULLS ON
1795GO
1796SET QUOTED_IDENTIFIER OFF
1797GO
1798IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_GetRolesForUser]') AND type in (N'P', N'PC'))
1799BEGIN
1800EXEC dbo.sp_executesql @statement = N'
1801CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetRolesForUser]
1802    @ApplicationName  nvarchar(256),
1803    @UserName         nvarchar(256)
1804AS
1805BEGIN
1806    DECLARE @ApplicationId uniqueidentifier
1807    SELECT  @ApplicationId = NULL
1808    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1809    IF (@ApplicationId IS NULL)
1810        RETURN(1)
1811    DECLARE @UserId uniqueidentifier
1812    SELECT  @UserId = NULL
1813
1814    SELECT  @UserId = UserId
1815    FROM    dbo.aspnet_Users
1816    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
1817
1818    IF (@UserId IS NULL)
1819        RETURN(1)
1820
1821    SELECT r.RoleName
1822    FROM   dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
1823    WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
1824    ORDER BY r.RoleName
1825    RETURN (0)
1826END' 
1827END
1828GO
1829SET ANSI_NULLS ON
1830GO
1831SET QUOTED_IDENTIFIER OFF
1832GO
1833IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_AddUsersToRoles]') AND type in (N'P', N'PC'))
1834BEGIN
1835EXEC dbo.sp_executesql @statement = N'
1836CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
1837	@ApplicationName  nvarchar(256),
1838	@UserNames		  nvarchar(4000),
1839	@RoleNames		  nvarchar(4000),
1840	@CurrentTimeUtc   datetime
1841AS
1842BEGIN
1843	DECLARE @AppId uniqueidentifier
1844	SELECT  @AppId = NULL
1845	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1846	IF (@AppId IS NULL)
1847		RETURN(2)
1848	DECLARE @TranStarted   bit
1849	SET @TranStarted = 0
1850
1851	IF( @@TRANCOUNT = 0 )
1852	BEGIN
1853		BEGIN TRANSACTION
1854		SET @TranStarted = 1
1855	END
1856
1857	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
1858	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
1859	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
1860	DECLARE @Num		int
1861	DECLARE @Pos		int
1862	DECLARE @NextPos	int
1863	DECLARE @Name		nvarchar(256)
1864
1865	SET @Num = 0
1866	SET @Pos = 1
1867	WHILE(@Pos <= LEN(@RoleNames))
1868	BEGIN
1869		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
1870		IF (@NextPos = 0 OR @NextPos IS NULL)
1871			SELECT @NextPos = LEN(@RoleNames) + 1
1872		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
1873		SELECT @Pos = @NextPos+1
1874
1875		INSERT INTO @tbNames VALUES (@Name)
1876		SET @Num = @Num + 1
1877	END
1878
1879	INSERT INTO @tbRoles
1880	  SELECT RoleId
1881	  FROM   dbo.aspnet_Roles ar, @tbNames t
1882	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
1883
1884	IF (@@ROWCOUNT <> @Num)
1885	BEGIN
1886		SELECT TOP 1 Name
1887		FROM   @tbNames
1888		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
1889		IF( @TranStarted = 1 )
1890			ROLLBACK TRANSACTION
1891		RETURN(2)
1892	END
1893
1894	DELETE FROM @tbNames WHERE 1=1
1895	SET @Num = 0
1896	SET @Pos = 1
1897
1898	WHILE(@Pos <= LEN(@UserNames))
1899	BEGIN
1900		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
1901		IF (@NextPos = 0 OR @NextPos IS NULL)
1902			SELECT @NextPos = LEN(@UserNames) + 1
1903		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
1904		SELECT @Pos = @NextPos+1
1905
1906		INSERT INTO @tbNames VALUES (@Name)
1907		SET @Num = @Num + 1
1908	END
1909
1910	INSERT INTO @tbUsers
1911	  SELECT UserId
1912	  FROM   dbo.aspnet_Users ar, @tbNames t
1913	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
1914
1915	IF (@@ROWCOUNT <> @Num)
1916	BEGIN
1917		DELETE FROM @tbNames
1918		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)
1919
1920		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
1921		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
1922		  FROM   @tbNames
1923
1924		INSERT INTO @tbUsers
1925		  SELECT  UserId
1926		  FROM	dbo.aspnet_Users au, @tbNames t
1927		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
1928	END
1929
1930	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
1931	BEGIN
1932		SELECT TOP 1 UserName, RoleName
1933		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
1934		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId
1935
1936		IF( @TranStarted = 1 )
1937			ROLLBACK TRANSACTION
1938		RETURN(3)
1939	END
1940
1941	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
1942	SELECT UserId, RoleId
1943	FROM @tbUsers, @tbRoles
1944
1945	IF( @TranStarted = 1 )
1946		COMMIT TRANSACTION
1947	RETURN(0)
1948END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ' 
1949END
1950GO
1951SET ANSI_NULLS ON
1952GO
1953SET QUOTED_IDENTIFIER OFF
1954GO
1955IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]') AND type in (N'P', N'PC'))
1956BEGIN
1957EXEC dbo.sp_executesql @statement = N'
1958CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
1959	@ApplicationName  nvarchar(256),
1960	@UserNames		  nvarchar(4000),
1961	@RoleNames		  nvarchar(4000)
1962AS
1963BEGIN
1964	DECLARE @AppId uniqueidentifier
1965	SELECT  @AppId = NULL
1966	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1967	IF (@AppId IS NULL)
1968		RETURN(2)
1969
1970
1971	DECLARE @TranStarted   bit
1972	SET @TranStarted = 0
1973
1974	IF( @@TRANCOUNT = 0 )
1975	BEGIN
1976		BEGIN TRANSACTION
1977		SET @TranStarted = 1
1978	END
1979
1980	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
1981	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
1982	DECLARE @tbUsers  table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
1983	DECLARE @Num	  int
1984	DECLARE @Pos	  int
1985	DECLARE @NextPos  int
1986	DECLARE @Name	  nvarchar(256)
1987	DECLARE @CountAll int
1988	DECLARE @CountU	  int
1989	DECLARE @CountR	  int
1990
1991
1992	SET @Num = 0
1993	SET @Pos = 1
1994	WHILE(@Pos <= LEN(@RoleNames))
1995	BEGIN
1996		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
1997		IF (@NextPos = 0 OR @NextPos IS NULL)
1998			SELECT @NextPos = LEN(@RoleNames) + 1
1999		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
2000		SELECT @Pos = @NextPos+1
2001
2002		INSERT INTO @tbNames VALUES (@Name)
2003		SET @Num = @Num + 1
2004	END
2005
2006	INSERT INTO @tbRoles
2007	  SELECT RoleId
2008	  FROM   dbo.aspnet_Roles ar, @tbNames t
2009	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
2010	SELECT @CountR = @@ROWCOUNT
2011
2012	IF (@CountR <> @Num)
2013	BEGIN
2014		SELECT TOP 1 N'''', Name
2015		FROM   @tbNames
2016		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
2017		IF( @TranStarted = 1 )
2018			ROLLBACK TRANSACTION
2019		RETURN(2)
2020	END
2021
2022
2023	DELETE FROM @tbNames WHERE 1=1
2024	SET @Num = 0
2025	SET @Pos = 1
2026
2027
2028	WHILE(@Pos <= LEN(@UserNames))
2029	BEGIN
2030		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
2031		IF (@NextPos = 0 OR @NextPos IS NULL)
2032			SELECT @NextPos = LEN(@UserNames) + 1
2033		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
2034		SELECT @Pos = @NextPos+1
2035
2036		INSERT INTO @tbNames VALUES (@Name)
2037		SET @Num = @Num + 1
2038	END
2039
2040	INSERT INTO @tbUsers
2041	  SELECT UserId
2042	  FROM   dbo.aspnet_Users ar, @tbNames t
2043	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
2044
2045	SELECT @CountU = @@ROWCOUNT
2046	IF (@CountU <> @Num)
2047	BEGIN
2048		SELECT TOP 1 Name, N''''
2049		FROM   @tbNames
2050		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)
2051
2052		IF( @TranStarted = 1 )
2053			ROLLBACK TRANSACTION
2054		RETURN(1)
2055	END
2056
2057	SELECT  @CountAll = COUNT(*)
2058	FROM	dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
2059	WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId
2060
2061	IF (@CountAll <> @CountU * @CountR)
2062	BEGIN
2063		SELECT TOP 1 UserName, RoleName
2064		FROM		 @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
2065		WHERE		 u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
2066					 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
2067					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
2068		IF( @TranStarted = 1 )
2069			ROLLBACK TRANSACTION
2070		RETURN(3)
2071	END
2072
2073	DELETE FROM dbo.aspnet_UsersInRoles
2074	WHERE UserId IN (SELECT UserId FROM @tbUsers)
2075	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
2076	IF( @TranStarted = 1 )
2077		COMMIT TRANSACTION
2078	RETURN(0)
2079END
2080                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ' 
2081END
2082GO
2083SET ANSI_NULLS ON
2084GO
2085SET QUOTED_IDENTIFIER ON
2086GO
2087IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Activity]') AND type in (N'P', N'PC'))
2088BEGIN
2089EXEC dbo.sp_executesql @statement = N'-- =============================================
2090-- Author:		<Author,,Name>
2091-- Create date: <Create Date,,>
2092-- Description:	<Description,,>
2093-- =============================================
2094CREATE PROCEDURE [dbo].[Activity] 
2095	-- Add the parameters for the stored procedure here
2096	@Days int
2097AS
2098BEGIN
2099	-- SET NOCOUNT ON added to prevent extra result sets from
2100	-- interfering with SELECT statements.
2101	SET NOCOUNT ON;
2102
2103	declare @fromdate datetime
2104	set @fromdate = getdate()-@Days
2105
2106	select UserName, Email, 
2107	convert(datetime, LastActivityDate, 103) as LastActivityDate
2108	from dbo.aspnet_Users, dbo.aspnet_Membership
2109	where dbo.aspnet_Users.UserId = dbo.aspnet_Membership.UserId
2110	and LastActivityDate > @fromdate
2111	ORDER BY LastActivityDate DESC
2112
2113END
2114' 
2115END
2116GO
2117SET ANSI_NULLS ON
2118GO
2119SET QUOTED_IDENTIFIER OFF
2120GO
2121IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UnRegisterSchemaVersion]') AND type in (N'P', N'PC'))
2122BEGIN
2123EXEC dbo.sp_executesql @statement = N'
2124CREATE PROCEDURE [dbo].[aspnet_UnRegisterSchemaVersion]
2125    @Feature                   nvarchar(128),
2126    @CompatibleSchemaVersion   nvarchar(128)
2127AS
2128BEGIN
2129    DELETE FROM dbo.aspnet_SchemaVersions
2130        WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
2131END' 
2132END
2133GO
2134SET ANSI_NULLS ON
2135GO
2136SET QUOTED_IDENTIFIER OFF
2137GO
2138IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_RegisterSchemaVersion]') AND type in (N'P', N'PC'))
2139BEGIN
2140EXEC dbo.sp_executesql @statement = N'
2141CREATE PROCEDURE [dbo].[aspnet_RegisterSchemaVersion]
2142    @Feature                   nvarchar(128),
2143    @CompatibleSchemaVersion   nvarchar(128),
2144    @IsCurrentVersion          bit,
2145    @RemoveIncompatibleSchema  bit
2146AS
2147BEGIN
2148    IF( @RemoveIncompatibleSchema = 1 )
2149    BEGIN
2150        DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
2151    END
2152    ELSE
2153    BEGIN
2154        IF( @IsCurrentVersion = 1 )
2155        BEGIN
2156            UPDATE dbo.aspnet_SchemaVersions
2157            SET IsCurrentVersion = 0
2158            WHERE Feature = LOWER( @Feature )
2159        END
2160    END
2161
2162    INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
2163    VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
2164END' 
2165END
2166GO
2167SET ANSI_NULLS ON
2168GO
2169SET QUOTED_IDENTIFIER OFF
2170GO
2171IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_CheckSchemaVersion]') AND type in (N'P', N'PC'))
2172BEGIN
2173EXEC dbo.sp_executesql @statement = N'
2174CREATE PROCEDURE [dbo].[aspnet_CheckSchemaVersion]
2175    @Feature                   nvarchar(128),
2176    @CompatibleSchemaVersion   nvarchar(128)
2177AS
2178BEGIN
2179    IF (EXISTS( SELECT  *
2180                FROM    dbo.aspnet_SchemaVersions
2181                WHERE   Feature = LOWER( @Feature ) AND
2182                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
2183        RETURN 0
2184
2185    RETURN 1
2186END' 
2187END
2188GO
2189SET ANSI_NULLS ON
2190GO
2191SET QUOTED_IDENTIFIER OFF
2192GO
2193IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_Profiles]'))
2194EXEC dbo.sp_executesql @statement = N'
2195  CREATE VIEW [dbo].[vw_aspnet_Profiles]
2196  AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
2197      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
2198                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
2199                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
2200  FROM [dbo].[aspnet_Profile]
2201  ' 
2202GO
2203SET ANSI_NULLS ON
2204GO
2205SET QUOTED_IDENTIFIER OFF
2206GO
2207IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_DeleteRole]') AND type in (N'P', N'PC'))
2208BEGIN
2209EXEC dbo.sp_executesql @statement = N'
2210CREATE PROCEDURE [dbo].[aspnet_Roles_DeleteRole]
2211    @ApplicationName            nvarchar(256),
2212    @RoleName                   nvarchar(256),
2213    @DeleteOnlyIfRoleIsEmpty    bit
2214AS
2215BEGIN
2216    DECLARE @ApplicationId uniqueidentifier
2217    SELECT  @ApplicationId = NULL
2218    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2219    IF (@ApplicationId IS NULL)
2220        RETURN(1)
2221
2222    DECLARE @ErrorCode     int
2223    SET @ErrorCode = 0
2224
2225    DECLARE @TranStarted   bit
2226    SET @TranStarted = 0
2227
2228    IF( @@TRANCOUNT = 0 )
2229    BEGIN
2230        BEGIN TRANSACTION
2231        SET @TranStarted = 1
2232    END
2233    ELSE
2234        SET @TranStarted = 0
2235
2236    DECLARE @RoleId   uniqueidentifier
2237    SELECT  @RoleId = NULL
2238    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
2239
2240    IF (@RoleId IS NULL)
2241    BEGIN
2242        SELECT @ErrorCode = 1
2243        GOTO Cleanup
2244    END
2245    IF (@DeleteOnlyIfRoleIsEmpty <> 0)
2246    BEGIN
2247        IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId))
2248        BEGIN
2249            SELECT @ErrorCode = 2
2250            GOTO Cleanup
2251        END
2252    END
2253
2254
2255    DELETE FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId
2256
2257    IF( @@ERROR <> 0 )
2258    BEGIN
2259        SET @ErrorCode = -1
2260        GOTO Cleanup
2261    END
2262
2263    DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId  AND ApplicationId = @ApplicationId
2264
2265    IF( @@ERROR <> 0 )
2266    BEGIN
2267        SET @ErrorCode = -1
2268        GOTO Cleanup
2269    END
2270
2271    IF( @TranStarted = 1 )
2272    BEGIN
2273        SET @TranStarted = 0
2274        COMMIT TRANSACTION
2275    END
2276
2277    RETURN(0)
2278
2279Cleanup:
2280
2281    IF( @TranStarted = 1 )
2282    BEGIN
2283        SET @TranStarted = 0
2284        ROLLBACK TRANSACTION
2285    END
2286
2287    RETURN @ErrorCode
2288END' 
2289END
2290GO
2291SET ANSI_NULLS ON
2292GO
2293SET QUOTED_IDENTIFIER OFF
2294GO
2295IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_Roles]'))
2296EXEC dbo.sp_executesql @statement = N'
2297  CREATE VIEW [dbo].[vw_aspnet_Roles]
2298  AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
2299  FROM [dbo].[aspnet_Roles]
2300  ' 
2301GO
2302SET ANSI_NULLS ON
2303GO
2304SET QUOTED_IDENTIFIER OFF
2305GO
2306IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_RoleExists]') AND type in (N'P', N'PC'))
2307BEGIN
2308EXEC dbo.sp_executesql @statement = N'
2309CREATE PROCEDURE [dbo].[aspnet_Roles_RoleExists]
2310    @ApplicationName  nvarchar(256),
2311    @RoleName         nvarchar(256)
2312AS
2313BEGIN
2314    DECLARE @ApplicationId uniqueidentifier
2315    SELECT  @ApplicationId = NULL
2316    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2317    IF (@ApplicationId IS NULL)
2318        RETURN(0)
2319    IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
2320        RETURN(1)
2321    ELSE
2322        RETURN(0)
2323END' 
2324END
2325GO
2326SET ANSI_NULLS ON
2327GO
2328SET QUOTED_IDENTIFIER OFF
2329GO
2330IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_GetAllRoles]') AND type in (N'P', N'PC'))
2331BEGIN
2332EXEC dbo.sp_executesql @statement = N'
2333CREATE PROCEDURE [dbo].[aspnet_Roles_GetAllRoles] (
2334    @ApplicationName           nvarchar(256))
2335AS
2336BEGIN
2337    DECLARE @ApplicationId uniqueidentifier
2338    SELECT  @ApplicationId = NULL
2339    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2340    IF (@ApplicationId IS NULL)
2341        RETURN
2342    SELECT RoleName
2343    FROM   dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
2344    ORDER BY RoleName
2345END' 
2346END
2347GO
2348SET ANSI_NULLS ON
2349GO
2350SET QUOTED_IDENTIFIER OFF
2351GO
2352IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_UsersInRoles]'))
2353EXEC dbo.sp_executesql @statement = N'
2354  CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
2355  AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
2356  FROM [dbo].[aspnet_UsersInRoles]
2357  ' 
2358GO
2359SET ANSI_NULLS ON
2360GO
2361SET QUOTED_IDENTIFIER OFF
2362GO
2363IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Paths_CreatePath]') AND type in (N'P', N'PC'))
2364BEGIN
2365EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Paths_CreatePath]
2366    @ApplicationId UNIQUEIDENTIFIER,
2367    @Path           NVARCHAR(256),
2368    @PathId         UNIQUEIDENTIFIER OUTPUT
2369AS
2370BEGIN
2371    BEGIN TRANSACTION
2372    IF (NOT EXISTS(SELECT * FROM dbo.aspnet_Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
2373    BEGIN
2374        INSERT dbo.aspnet_Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
2375    END
2376    COMMIT TRANSACTION
2377    SELECT @PathId = PathId FROM dbo.aspnet_Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
2378END' 
2379END
2380GO
2381SET ANSI_NULLS ON
2382GO
2383SET QUOTED_IDENTIFIER OFF
2384GO
2385IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_WebPartState_Paths]'))
2386EXEC dbo.sp_executesql @statement = N'
2387  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]
2388  AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
2389  FROM [dbo].[aspnet_Paths]
2390  ' 
2391GO
2392SET ANSI_NULLS ON
2393GO
2394SET QUOTED_IDENTIFIER OFF
2395GO
2396IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_WebPartState_Shared]'))
2397EXEC dbo.sp_executesql @statement = N'
2398  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]
2399  AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
2400  FROM [dbo].[aspnet_PersonalizationAllUsers]
2401  ' 
2402GO
2403SET ANSI_NULLS ON
2404GO
2405SET QUOTED_IDENTIFIER OFF
2406GO
2407IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_WebPartState_User]'))
2408EXEC dbo.sp_executesql @statement = N'
2409  CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]
2410  AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
2411  FROM [dbo].[aspnet_PersonalizationPerUser]
2412  ' 
2413GO
2414SET ANSI_NULLS ON
2415GO
2416SET QUOTED_IDENTIFIER OFF
2417GO
2418IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_WebEvent_LogEvent]') AND type in (N'P', N'PC'))
2419BEGIN
2420EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_WebEvent_LogEvent]
2421        @EventId         char(32),
2422        @EventTimeUtc    datetime,
2423        @EventTime       datetime,
2424        @EventType       nvarchar(256),
2425        @EventSequence   decimal(19,0),
2426        @EventOccurrence decimal(19,0),
2427        @EventCode       int,
2428        @EventDetailCode int,
2429        @Message         nvarchar(1024),
2430        @ApplicationPath nvarchar(256),
2431        @ApplicationVirtualPath nvarchar(256),
2432        @MachineName    nvarchar(256),
2433        @RequestUrl      nvarchar(1024),
2434        @ExceptionType   nvarchar(256),
2435        @Details         ntext
2436AS
2437BEGIN
2438    INSERT
2439        dbo.aspnet_WebEvent_Events
2440        (
2441            EventId,
2442            EventTimeUtc,
2443            EventTime,
2444            EventType,
2445            EventSequence,
2446            EventOccurrence,
2447            EventCode,
2448            EventDetailCode,
2449            Message,
2450            ApplicationPath,
2451            ApplicationVirtualPath,
2452            MachineName,
2453            RequestUrl,
2454            ExceptionType,
2455            Details
2456        )
2457    VALUES
2458    (
2459        @EventId,
2460        @EventTimeUtc,
2461        @EventTime,
2462        @EventType,
2463        @EventSequence,
2464        @EventOccurrence,
2465        @EventCode,
2466        @EventDetailCode,
2467        @Message,
2468        @ApplicationPath,
2469        @ApplicationVirtualPath,
2470        @MachineName,
2471        @RequestUrl,
2472        @ExceptionType,
2473        @Details
2474    )
2475END' 
2476END
2477GO
2478SET ANSI_NULLS ON
2479GO
2480SET QUOTED_IDENTIFIER OFF
2481GO
2482IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Personalization_GetApplicationId]') AND type in (N'P', N'PC'))
2483BEGIN
2484EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Personalization_GetApplicationId] (
2485    @ApplicationName NVARCHAR(256),
2486    @ApplicationId UNIQUEIDENTIFIER OUT)
2487AS
2488BEGIN
2489    SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2490END' 
2491END
2492GO
2493SET ANSI_NULLS ON
2494GO
2495SET QUOTED_IDENTIFIER OFF
2496GO
2497IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Profile_GetProfiles]') AND type in (N'P', N'PC'))
2498BEGIN
2499EXEC dbo.sp_executesql @statement = N'
2500CREATE PROCEDURE [dbo].[aspnet_Profile_GetProfiles]
2501    @ApplicationName        nvarchar(256),
2502    @ProfileAuthOptions     int,
2503    @PageIndex              int,
2504    @PageSize               int,
2505    @UserNameToMatch        nvarchar(256) = NULL,
2506    @InactiveSinceDate      datetime      = NULL
2507AS
2508BEGIN
2509    DECLARE @ApplicationId uniqueidentifier
2510    SELECT  @ApplicationId = NULL
2511    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2512    IF (@ApplicationId IS NULL)
2513        RETURN
2514
2515    -- Set the page bounds
2516    DECLARE @PageLowerBound int
2517    DECLARE @PageUpperBound int
2518    DECLARE @TotalRecords   int
2519    SET @PageLowerBound = @PageSize * @PageIndex
2520    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2521
2522    -- Create a temp table TO store the select results
2523    CREATE TABLE #PageIndexForUsers
2524    (
2525        IndexId int IDENTITY (0, 1) NOT NULL,
2526        UserId uniqueidentifier
2527    )
2528
2529    -- Insert into our temp table
2530    INSERT INTO #PageIndexForUsers (UserId)
2531        SELECT  u.UserId
2532        FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
2533        WHERE   ApplicationId = @ApplicationId
2534            AND u.UserId = p.UserId
2535            AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
2536            AND (     (@ProfileAuthOptions = 2)
2537                   OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
2538                   OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
2539                 )
2540            AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
2541        ORDER BY UserName
2542
2543    SELECT  u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
2544            DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
2545    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
2546    WHERE   u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
2547
2548    SELECT COUNT(*)
2549    FROM   #PageIndexForUsers
2550
2551    DROP TABLE #PageIndexForUsers
2552END' 
2553END
2554GO
2555SET ANSI_NULLS ON
2556GO
2557SET QUOTED_IDENTIFIER OFF
2558GO
2559IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetAllUsers]') AND type in (N'P', N'PC'))
2560BEGIN
2561EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetAllUsers]
2562    @ApplicationName       nvarchar(256),
2563    @PageIndex             int,
2564    @PageSize              int
2565AS
2566BEGIN
2567    DECLARE @ApplicationId uniqueidentifier
2568    SELECT  @ApplicationId = NULL
2569    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2570    IF (@ApplicationId IS NULL)
2571        RETURN 0
2572
2573
2574    -- Set the page bounds
2575    DECLARE @PageLowerBound int
2576    DECLARE @PageUpperBound int
2577    DECLARE @TotalRecords   int
2578    SET @PageLowerBound = @PageSize * @PageIndex
2579    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2580
2581    -- Create a temp table TO store the select results
2582    CREATE TABLE #PageIndexForUsers
2583    (
2584        IndexId int IDENTITY (0, 1) NOT NULL,
2585        UserId uniqueidentifier
2586    )
2587
2588    -- Insert into our temp table
2589    INSERT INTO #PageIndexForUsers (UserId)
2590    SELECT u.UserId
2591    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
2592    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
2593    ORDER BY u.UserName
2594
2595    SELECT @TotalRecords = @@ROWCOUNT
2596
2597    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
2598            m.CreateDate,
2599            m.LastLoginDate,
2600            u.LastActivityDate,
2601            m.LastPasswordChangedDate,
2602            u.UserId, m.IsLockedOut,
2603            m.LastLockoutDate
2604    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
2605    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
2606           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
2607    ORDER BY u.UserName
2608    RETURN @TotalRecords
2609END' 
2610END
2611GO
2612SET ANSI_NULLS ON
2613GO
2614SET QUOTED_IDENTIFIER OFF
2615GO
2616IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_FindUsersByName]') AND type in (N'P', N'PC'))
2617BEGIN
2618EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
2619    @ApplicationName       nvarchar(256),
2620    @UserNameToMatch       nvarchar(256),
2621    @PageIndex             int,
2622    @PageSize              int
2623AS
2624BEGIN
2625    DECLARE @ApplicationId uniqueidentifier
2626    SELECT  @ApplicationId = NULL
2627    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2628    IF (@ApplicationId IS NULL)
2629        RETURN 0
2630
2631    -- Set the page bounds
2632    DECLARE @PageLowerBound int
2633    DECLARE @PageUpperBound int
2634    DECLARE @TotalRecords   int
2635    SET @PageLowerBound = @PageSize * @PageIndex
2636    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2637
2638    -- Create a temp table TO store the select results
2639    CREATE TABLE #PageIndexForUsers
2640    (
2641        IndexId int IDENTITY (0, 1) NOT NULL,
2642        UserId uniqueidentifier
2643    )
2644
2645    -- Insert into our temp table
2646    INSERT INTO #PageIndexForUsers (UserId)
2647        SELECT u.UserId
2648        FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
2649        WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
2650        ORDER BY u.UserName
2651
2652
2653    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
2654            m.CreateDate,
2655            m.LastLoginDate,
2656            u.LastActivityDate,
2657            m.LastPasswordChangedDate,
2658            u.UserId, m.IsLockedOut,
2659            m.LastLockoutDate
2660    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
2661    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
2662           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
2663    ORDER BY u.UserName
2664
2665    SELECT  @TotalRecords = COUNT(*)
2666    FROM    #PageIndexForUsers
2667    RETURN @TotalRecords
2668END' 
2669END
2670GO
2671SET ANSI_NULLS ON
2672GO
2673SET QUOTED_IDENTIFIER OFF
2674GO
2675IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_FindUsersByEmail]') AND type in (N'P', N'PC'))
2676BEGIN
2677EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByEmail]
2678    @ApplicationName       nvarchar(256),
2679    @EmailToMatch          nvarchar(256),
2680    @PageIndex             int,
2681    @PageSize              int
2682AS
2683BEGIN
2684    DECLARE @ApplicationId uniqueidentifier
2685    SELECT  @ApplicationId = NULL
2686    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2687    IF (@ApplicationId IS NULL)
2688        RETURN 0
2689
2690    -- Set the page bounds
2691    DECLARE @PageLowerBound int
2692    DECLARE @PageUpperBound int
2693    DECLARE @TotalRecords   int
2694    SET @PageLowerBound = @PageSize * @PageIndex
2695    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2696
2697    -- Create a temp table TO store the select results
2698    CREATE TABLE #PageIndexForUsers
2699    (
2700        IndexId int IDENTITY (0, 1) NOT NULL,
2701        UserId uniqueidentifier
2702    )
2703
2704    -- Insert into our temp table
2705    IF( @EmailToMatch IS NULL )
2706        INSERT INTO #PageIndexForUsers (UserId)
2707            SELECT u.UserId
2708            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
2709            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
2710            ORDER BY m.LoweredEmail
2711    ELSE
2712        INSERT INTO #PageIndexForUsers (UserId)
2713            SELECT u.UserId
2714            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
2715            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
2716            ORDER BY m.LoweredEmail
2717
2718    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
2719            m.CreateDate,
2720            m.LastLoginDate,
2721            u.LastActivityDate,
2722            m.LastPasswordChangedDate,
2723            u.UserId, m.IsLockedOut,
2724            m.LastLockoutDate
2725    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
2726    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
2727           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
2728    ORDER BY m.LoweredEmail
2729
2730    SELECT  @TotalRecords = COUNT(*)
2731    FROM    #PageIndexForUsers
2732    RETURN @TotalRecords
2733END' 
2734END
2735GO
2736SET ANSI_NULLS ON
2737GO
2738SET QUOTED_IDENTIFIER OFF
2739GO
2740IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_aspnet_Applications]'))
2741EXEC dbo.sp_executesql @statement = N'
2742  CREATE VIEW [dbo].[vw_aspnet_Applications]
2743  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
2744  FROM [dbo].[aspnet_Applications]
2745  ' 
2746GO
2747SET ANSI_NULLS ON
2748GO
2749SET QUOTED_IDENTIFIER OFF
2750GO
2751IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Applications_CreateApplication]') AND type in (N'P', N'PC'))
2752BEGIN
2753EXEC dbo.sp_executesql @statement = N'
2754CREATE PROCEDURE [dbo].[aspnet_Applications_CreateApplication]
2755    @ApplicationName      nvarchar(256),
2756    @ApplicationId        uniqueidentifier OUTPUT
2757AS
2758BEGIN
2759    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2760
2761    IF(@ApplicationId IS NULL)
2762    BEGIN
2763        DECLARE @TranStarted   bit
2764        SET @TranStarted = 0
2765
2766        IF( @@TRANCOUNT = 0 )
2767        BEGIN
2768	        BEGIN TRANSACTION
2769	        SET @TranStarted = 1
2770        END
2771        ELSE
2772    	    SET @TranStarted = 0
2773
2774        SELECT  @ApplicationId = ApplicationId
2775        FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
2776        WHERE LOWER(@ApplicationName) = LoweredApplicationName
2777
2778        IF(@ApplicationId IS NULL)
2779        BEGIN
2780            SELECT  @ApplicationId = NEWID()
2781            INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
2782            VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
2783        END
2784
2785
2786        IF( @TranStarted = 1 )
2787        BEGIN
2788            IF(@@ERROR = 0)
2789            BEGIN
2790	        SET @TranStarted = 0
2791	        COMMIT TRANSACTION
2792            END
2793            ELSE
2794            BEGIN
2795                SET @TranStarted = 0
2796                ROLLBACK TRANSACTION
2797            END
2798        END
2799    END
2800END' 
2801END
2802GO
2803SET ANSI_NULLS ON
2804GO
2805SET QUOTED_IDENTIFIER OFF
2806GO
2807IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_CreateUser]') AND type in (N'P', N'PC'))
2808BEGIN
2809EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_CreateUser]
2810    @ApplicationName                        nvarchar(256),
2811    @UserName                               nvarchar(256),
2812    @Password                               nvarchar(128),
2813    @PasswordSalt                           nvarchar(128),
2814    @Email                                  nvarchar(256),
2815    @PasswordQuestion                       nvarchar(256),
2816    @PasswordAnswer                         nvarchar(128),
2817    @IsApproved                             bit,
2818    @CurrentTimeUtc                         datetime,
2819    @CreateDate                             datetime = NULL,
2820    @UniqueEmail                            int      = 0,
2821    @PasswordFormat                         int      = 0,
2822    @UserId                                 uniqueidentifier OUTPUT
2823AS
2824BEGIN
2825    DECLARE @ApplicationId uniqueidentifier
2826    SELECT  @ApplicationId = NULL
2827
2828    DECLARE @NewUserId uniqueidentifier
2829    SELECT @NewUserId = NULL
2830
2831    DECLARE @IsLockedOut bit
2832    SET @IsLockedOut = 0
2833
2834    DECLARE @LastLockoutDate  datetime
2835    SET @LastLockoutDate = CONVERT( datetime, ''17540101'', 112 )
2836
2837    DECLARE @FailedPasswordAttemptCount int
2838    SET @FailedPasswordAttemptCount = 0
2839
2840    DECLARE @FailedPasswordAttemptWindowStart  datetime
2841    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
2842
2843    DECLARE @FailedPasswordAnswerAttemptCount int
2844    SET @FailedPasswordAnswerAttemptCount = 0
2845
2846    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
2847    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
2848
2849    DECLARE @NewUserCreated bit
2850    DECLARE @ReturnValue   int
2851    SET @ReturnValue = 0
2852
2853    DECLARE @ErrorCode     int
2854    SET @ErrorCode = 0
2855
2856    DECLARE @TranStarted   bit
2857    SET @TranStarted = 0
2858
2859    IF( @@TRANCOUNT = 0 )
2860    BEGIN
2861	    BEGIN TRANSACTION
2862	    SET @TranStarted = 1
2863    END
2864    ELSE
2865    	SET @TranStarted = 0
2866
2867    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
2868
2869    IF( @@ERROR <> 0 )
2870    BEGIN
2871        SET @ErrorCode = -1
2872        GOTO Cleanup
2873    END
2874
2875    SET @CreateDate = @CurrentTimeUtc
2876
2877    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
2878    IF ( @NewUserId IS NULL )
2879    BEGIN
2880        SET @NewUserId = @UserId
2881        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
2882        SET @NewUserCreated = 1
2883    END
2884    ELSE
2885    BEGIN
2886        SET @NewUserCreated = 0
2887        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
2888        BEGIN
2889            SET @ErrorCode = 6
2890            GOTO Cleanup
2891        END
2892    END
2893
2894    IF( @@ERROR <> 0 )
2895    BEGIN
2896        SET @ErrorCode = -1
2897        GOTO Cleanup
2898    END
2899
2900    IF( @ReturnValue = -1 )
2901    BEGIN
2902        SET @ErrorCode = 10
2903        GOTO Cleanup
2904    END
2905
2906    IF ( EXISTS ( SELECT UserId
2907                  FROM   dbo.aspnet_Membership
2908                  WHERE  @NewUserId = UserId ) )
2909    BEGIN
2910        SET @ErrorCode = 6
2911        GOTO Cleanup
2912    END
2913
2914    SET @UserId = @NewUserId
2915
2916    IF (@UniqueEmail = 1)
2917    BEGIN
2918        IF (EXISTS (SELECT *
2919                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
2920                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
2921        BEGIN
2922            SET @ErrorCode = 7
2923            GOTO Cleanup
2924        END
2925    END
2926
2927    IF (@NewUserCreated = 0)
2928    BEGIN
2929        UPDATE dbo.aspnet_Users
2930        SET    LastActivityDate = @CreateDate
2931        WHERE  @UserId = UserId
2932        IF( @@ERROR <> 0 )
2933        BEGIN
2934            SET @ErrorCode = -1
2935            GOTO Cleanup
2936        END
2937    END
2938
2939    INSERT INTO dbo.aspnet_Membership
2940                ( ApplicationId,
2941                  UserId,
2942                  Password,
2943                  PasswordSalt,
2944                  Email,
2945                  LoweredEmail,
2946                  PasswordQuestion,
2947                  PasswordAnswer,
2948                  PasswordFormat,
2949                  IsApproved,
2950                  IsLockedOut,
2951                  CreateDate,
2952                  LastLoginDate,
2953                  LastPasswordChangedDate,
2954                  LastLockoutDate,
2955                  FailedPasswordAttemptCount,
2956                  FailedPasswordAttemptWindowStart,
2957                  FailedPasswordAnswerAttemptCount,
2958                  FailedPasswordAnswerAttemptWindowStart )
2959         VALUES ( @ApplicationId,
2960                  @UserId,
2961                  @Password,
2962                  @PasswordSalt,
2963                  @Email,
2964                  LOWER(@Email),
2965                  @PasswordQuestion,
2966                  @PasswordAnswer,
2967                  @PasswordFormat,
2968                  @IsApproved,
2969                  @IsLockedOut,
2970                  @CreateDate,
2971                  @CreateDate,
2972                  @CreateDate,
2973                  @LastLockoutDate,
2974                  @FailedPasswordAttemptCount,
2975                  @FailedPasswordAttemptWindowStart,
2976                  @FailedPasswordAnswerAttemptCount,
2977                  @FailedPasswordAnswerAttemptWindowStart )
2978
2979    IF( @@ERROR <> 0 )
2980    BEGIN
2981        SET @ErrorCode = -1
2982        GOTO Cleanup
2983    END
2984
2985    IF( @TranStarted = 1 )
2986    BEGIN
2987	    SET @TranStarted = 0
2988	    COMMIT TRANSACTION
2989    END
2990
2991    RETURN 0
2992
2993Cleanup:
2994
2995    IF( @TranStarted = 1 )
2996    BEGIN
2997        SET @TranStarted = 0
2998    	ROLLBACK TRANSACTION
2999    END
3000
3001    RETURN @ErrorCode
3002
3003END' 
3004END
3005GO
3006SET ANSI_NULLS ON
3007GO
3008SET QUOTED_IDENTIFIER OFF
3009GO
3010IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Profile_SetProperties]') AND type in (N'P', N'PC'))
3011BEGIN
3012EXEC dbo.sp_executesql @statement = N'
3013CREATE PROCEDURE [dbo].[aspnet_Profile_SetProperties]
3014    @ApplicationName        nvarchar(256),
3015    @PropertyNames          ntext,
3016    @PropertyValuesString   ntext,
3017    @PropertyValuesBinary   image,
3018    @UserName               nvarchar(256),
3019    @IsUserAnonymous        bit,
3020    @CurrentTimeUtc         datetime
3021AS
3022BEGIN
3023    DECLARE @ApplicationId uniqueidentifier
3024    SELECT  @ApplicationId = NULL
3025
3026    DECLARE @ErrorCode     int
3027    SET @ErrorCode = 0
3028
3029    DECLARE @TranStarted   bit
3030    SET @TranStarted = 0
3031
3032    IF( @@TRANCOUNT = 0 )
3033    BEGIN
3034       BEGIN TRANSACTION
3035       SET @TranStarted = 1
3036    END
3037    ELSE
3038    	SET @TranStarted = 0
3039
3040    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
3041
3042    IF( @@ERROR <> 0 )
3043    BEGIN
3044        SET @ErrorCode = -1
3045        GOTO Cleanup
3046    END
3047
3048    DECLARE @UserId uniqueidentifier
3049    DECLARE @LastActivityDate datetime
3050    SELECT  @UserId = NULL
3051    SELECT  @LastActivityDate = @CurrentTimeUtc
3052
3053    SELECT @UserId = UserId
3054    FROM   dbo.aspnet_Users
3055    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
3056    IF (@UserId IS NULL)
3057        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT
3058
3059    IF( @@ERROR <> 0 )
3060    BEGIN
3061        SET @ErrorCode = -1
3062        GOTO Cleanup
3063    END
3064
3065    UPDATE dbo.aspnet_Users
3066    SET    LastActivityDate=@CurrentTimeUtc
3067    WHERE  UserId = @UserId
3068
3069    IF( @@ERROR <> 0 )
3070    BEGIN
3071        SET @ErrorCode = -1
3072        GOTO Cleanup
3073    END
3074
3075    IF (EXISTS( SELECT *
3076               FROM   dbo.aspnet_Profile
3077               WHERE  UserId = @UserId))
3078        UPDATE dbo.aspnet_Profile
3079        SET    PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
3080               PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
3081        WHERE  UserId = @UserId
3082    ELSE
3083        INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
3084             VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)
3085
3086    IF( @@ERROR <> 0 )
3087    BEGIN
3088        SET @ErrorCode = -1
3089        GOTO Cleanup
3090    END
3091
3092    IF( @TranStarted = 1 )
3093    BEGIN
3094    	SET @TranStarted = 0
3095    	COMMIT TRANSACTION
3096    END
3097
3098    RETURN 0
3099
3100Cleanup:
3101
3102    IF( @TranStarted = 1 )
3103    BEGIN
3104        SET @TranStarted = 0
3105    	ROLLBACK TRANSACTION
3106    END
3107
3108    RETURN @ErrorCode
3109
3110END' 
3111END
3112GO
3113SET ANSI_NULLS ON
3114GO
3115SET QUOTED_IDENTIFIER OFF
3116GO
3117IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationPerUser_GetPageSettings]') AND type in (N'P', N'PC'))
3118BEGIN
3119EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_GetPageSettings] (
3120    @ApplicationName  NVARCHAR(256),
3121    @UserName         NVARCHAR(256),
3122    @Path             NVARCHAR(256),
3123    @CurrentTimeUtc   DATETIME)
3124AS
3125BEGIN
3126    DECLARE @ApplicationId UNIQUEIDENTIFIER
3127    DECLARE @PathId UNIQUEIDENTIFIER
3128    DECLARE @UserId UNIQUEIDENTIFIER
3129
3130    SELECT @ApplicationId = NULL
3131    SELECT @PathId = NULL
3132    SELECT @UserId = NULL
3133
3134    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3135    IF (@ApplicationId IS NULL)
3136    BEGIN
3137        RETURN
3138    END
3139
3140    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
3141    IF (@PathId IS NULL)
3142    BEGIN
3143        RETURN
3144    END
3145
3146    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
3147    IF (@UserId IS NULL)
3148    BEGIN
3149        RETURN
3150    END
3151
3152    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
3153    SET      LastActivityDate = @CurrentTimeUtc
3154    WHERE    UserId = @UserId
3155    IF (@@ROWCOUNT = 0) -- Username not found
3156        RETURN
3157
3158    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
3159END' 
3160END
3161GO
3162SET ANSI_NULLS ON
3163GO
3164SET QUOTED_IDENTIFIER OFF
3165GO
3166IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationPerUser_ResetPageSettings]') AND type in (N'P', N'PC'))
3167BEGIN
3168EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings] (
3169    @ApplicationName  NVARCHAR(256),
3170    @UserName         NVARCHAR(256),
3171    @Path             NVARCHAR(256),
3172    @CurrentTimeUtc   DATETIME)
3173AS
3174BEGIN
3175    DECLARE @ApplicationId UNIQUEIDENTIFIER
3176    DECLARE @PathId UNIQUEIDENTIFIER
3177    DECLARE @UserId UNIQUEIDENTIFIER
3178
3179    SELECT @ApplicationId = NULL
3180    SELECT @PathId = NULL
3181    SELECT @UserId = NULL
3182
3183    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3184    IF (@ApplicationId IS NULL)
3185    BEGIN
3186        RETURN
3187    END
3188
3189    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
3190    IF (@PathId IS NULL)
3191    BEGIN
3192        RETURN
3193    END
3194
3195    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
3196    IF (@UserId IS NULL)
3197    BEGIN
3198        RETURN
3199    END
3200
3201    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
3202    SET      LastActivityDate = @CurrentTimeUtc
3203    WHERE    UserId = @UserId
3204    IF (@@ROWCOUNT = 0) -- Username not found
3205        RETURN
3206
3207    DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
3208    RETURN 0
3209END' 
3210END
3211GO
3212SET ANSI_NULLS ON
3213GO
3214SET QUOTED_IDENTIFIER OFF
3215GO
3216IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationPerUser_SetPageSettings]') AND type in (N'P', N'PC'))
3217BEGIN
3218EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_SetPageSettings] (
3219    @ApplicationName  NVARCHAR(256),
3220    @UserName         NVARCHAR(256),
3221    @Path             NVARCHAR(256),
3222    @PageSettings     IMAGE,
3223    @CurrentTimeUtc   DATETIME)
3224AS
3225BEGIN
3226    DECLARE @ApplicationId UNIQUEIDENTIFIER
3227    DECLARE @PathId UNIQUEIDENTIFIER
3228    DECLARE @UserId UNIQUEIDENTIFIER
3229
3230    SELECT @ApplicationId = NULL
3231    SELECT @PathId = NULL
3232    SELECT @UserId = NULL
3233
3234    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
3235
3236    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
3237    IF (@PathId IS NULL)
3238    BEGIN
3239        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
3240    END
3241
3242    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
3243    IF (@UserId IS NULL)
3244    BEGIN
3245        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
3246    END
3247
3248    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
3249    SET      LastActivityDate = @CurrentTimeUtc
3250    WHERE    UserId = @UserId
3251    IF (@@ROWCOUNT = 0) -- Username not found
3252        RETURN
3253
3254    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
3255        UPDATE dbo.aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
3256    ELSE
3257        INSERT INTO dbo.aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
3258    RETURN 0
3259END' 
3260END
3261GO
3262SET ANSI_NULLS ON
3263GO
3264SET QUOTED_IDENTIFIER OFF
3265GO
3266IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAdministration_ResetUserState]') AND type in (N'P', N'PC'))
3267BEGIN
3268EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetUserState] (
3269    @Count                  int                 OUT,
3270    @ApplicationName        NVARCHAR(256),
3271    @InactiveSinceDate      DATETIME            = NULL,
3272    @UserName               NVARCHAR(256)       = NULL,
3273    @Path                   NVARCHAR(256)       = NULL)
3274AS
3275BEGIN
3276    DECLARE @ApplicationId UNIQUEIDENTIFIER
3277    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3278    IF (@ApplicationId IS NULL)
3279        SELECT @Count = 0
3280    ELSE
3281    BEGIN
3282        DELETE FROM dbo.aspnet_PersonalizationPerUser
3283        WHERE Id IN (SELECT PerUser.Id
3284                     FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
3285                     WHERE Paths.ApplicationId = @ApplicationId
3286                           AND PerUser.UserId = Users.UserId
3287                           AND PerUser.PathId = Paths.PathId
3288                           AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
3289                           AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
3290                           AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))
3291
3292        SELECT @Count = @@ROWCOUNT
3293    END
3294END' 
3295END
3296GO
3297SET ANSI_NULLS ON
3298GO
3299SET QUOTED_IDENTIFIER OFF
3300GO
3301IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAdministration_GetCountOfState]') AND type in (N'P', N'PC'))
3302BEGIN
3303EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_GetCountOfState] (
3304    @Count int OUT,
3305    @AllUsersScope bit,
3306    @ApplicationName NVARCHAR(256),
3307    @Path NVARCHAR(256) = NULL,
3308    @UserName NVARCHAR(256) = NULL,
3309    @InactiveSinceDate DATETIME = NULL)
3310AS
3311BEGIN
3312
3313    DECLARE @ApplicationId UNIQUEIDENTIFIER
3314    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3315    IF (@ApplicationId IS NULL)
3316        SELECT @Count = 0
3317    ELSE
3318        IF (@AllUsersScope = 1)
3319            SELECT @Count = COUNT(*)
3320            FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
3321            WHERE Paths.ApplicationId = @ApplicationId
3322                  AND AllUsers.PathId = Paths.PathId
3323                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
3324        ELSE
3325            SELECT @Count = COUNT(*)
3326            FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
3327            WHERE Paths.ApplicationId = @ApplicationId
3328                  AND PerUser.UserId = Users.UserId
3329                  AND PerUser.PathId = Paths.PathId
3330                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
3331                  AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
3332                  AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
3333END' 
3334END
3335GO
3336SET ANSI_NULLS ON
3337GO
3338SET QUOTED_IDENTIFIER OFF
3339GO
3340IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAllUsers_SetPageSettings]') AND type in (N'P', N'PC'))
3341BEGIN
3342EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings] (
3343    @ApplicationName  NVARCHAR(256),
3344    @Path             NVARCHAR(256),
3345    @PageSettings     IMAGE,
3346    @CurrentTimeUtc   DATETIME)
3347AS
3348BEGIN
3349    DECLARE @ApplicationId UNIQUEIDENTIFIER
3350    DECLARE @PathId UNIQUEIDENTIFIER
3351
3352    SELECT @ApplicationId = NULL
3353    SELECT @PathId = NULL
3354
3355    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
3356
3357    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
3358    IF (@PathId IS NULL)
3359    BEGIN
3360        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
3361    END
3362
3363    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId))
3364        UPDATE dbo.aspnet_PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
3365    ELSE
3366        INSERT INTO dbo.aspnet_PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
3367    RETURN 0
3368END' 
3369END
3370GO
3371SET ANSI_NULLS ON
3372GO
3373SET QUOTED_IDENTIFIER OFF
3374GO
3375IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_CreateRole]') AND type in (N'P', N'PC'))
3376BEGIN
3377EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Roles_CreateRole]
3378    @ApplicationName  nvarchar(256),
3379    @RoleName         nvarchar(256)
3380AS
3381BEGIN
3382    DECLARE @ApplicationId uniqueidentifier
3383    SELECT  @ApplicationId = NULL
3384
3385    DECLARE @ErrorCode     int
3386    SET @ErrorCode = 0
3387
3388    DECLARE @TranStarted   bit
3389    SET @TranStarted = 0
3390
3391    IF( @@TRANCOUNT = 0 )
3392    BEGIN
3393        BEGIN TRANSACTION
3394        SET @TranStarted = 1
3395    END
3396    ELSE
3397        SET @TranStarted = 0
3398
3399    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
3400
3401    IF( @@ERROR <> 0 )
3402    BEGIN
3403        SET @ErrorCode = -1
3404        GOTO Cleanup
3405    END
3406
3407    IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
3408    BEGIN
3409        SET @ErrorCode = 1
3410        GOTO Cleanup
3411    END
3412
3413    INSERT INTO dbo.aspnet_Roles
3414                (ApplicationId, RoleName, LoweredRoleName)
3415         VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
3416
3417    IF( @@ERROR <> 0 )
3418    BEGIN
3419        SET @ErrorCode = -1
3420        GOTO Cleanup
3421    END
3422
3423    IF( @TranStarted = 1 )
3424    BEGIN
3425        SET @TranStarted = 0
3426        COMMIT TRANSACTION
3427    END
3428
3429    RETURN(0)
3430
3431Cleanup:
3432
3433    IF( @TranStarted = 1 )
3434    BEGIN
3435        SET @TranStarted = 0
3436        ROLLBACK TRANSACTION
3437    END
3438
3439    RETURN @ErrorCode
3440
3441END' 
3442END
3443GO
3444SET ANSI_NULLS ON
3445GO
3446SET QUOTED_IDENTIFIER OFF
3447GO
3448IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Profile_DeleteProfiles]') AND type in (N'P', N'PC'))
3449BEGIN
3450EXEC dbo.sp_executesql @statement = N'
3451CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteProfiles]
3452    @ApplicationName        nvarchar(256),
3453    @UserNames              nvarchar(4000)
3454AS
3455BEGIN
3456    DECLARE @UserName     nvarchar(256)
3457    DECLARE @CurrentPos   int
3458    DECLARE @NextPos      int
3459    DECLARE @NumDeleted   int
3460    DECLARE @DeletedUser  int
3461    DECLARE @TranStarted  bit
3462    DECLARE @ErrorCode    int
3463
3464    SET @ErrorCode = 0
3465    SET @CurrentPos = 1
3466    SET @NumDeleted = 0
3467    SET @TranStarted = 0
3468
3469    IF( @@TRANCOUNT = 0 )
3470    BEGIN
3471        BEGIN TRANSACTION
3472        SET @TranStarted = 1
3473    END
3474    ELSE
3475    	SET @TranStarted = 0
3476
3477    WHILE (@CurrentPos <= LEN(@UserNames))
3478    BEGIN
3479        SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @CurrentPos)
3480        IF (@NextPos = 0 OR @NextPos IS NULL)
3481            SELECT @NextPos = LEN(@UserNames) + 1
3482
3483        SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
3484        SELECT @CurrentPos = @NextPos+1
3485
3486        IF (LEN(@UserName) > 0)
3487        BEGIN
3488            SELECT @DeletedUser = 0
3489            EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
3490            IF( @@ERROR <> 0 )
3491            BEGIN
3492                SET @ErrorCode = -1
3493                GOTO Cleanup
3494            END
3495            IF (@DeletedUser <> 0)
3496                SELECT @NumDeleted = @NumDeleted + 1
3497        END
3498    END
3499    SELECT @NumDeleted
3500    IF (@TranStarted = 1)
3501    BEGIN
3502    	SET @TranStarted = 0
3503    	COMMIT TRANSACTION
3504    END
3505    SET @TranStarted = 0
3506
3507    RETURN 0
3508
3509Cleanup:
3510    IF (@TranStarted = 1 )
3511    BEGIN
3512        SET @TranStarted = 0
3513    	ROLLBACK TRANSACTION
3514    END
3515    RETURN @ErrorCode
3516END' 
3517END
3518GO
3519SET ANSI_NULLS ON
3520GO
3521SET QUOTED_IDENTIFIER OFF
3522GO
3523IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAdministration_DeleteAllState]') AND type in (N'P', N'PC'))
3524BEGIN
3525EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_DeleteAllState] (
3526    @AllUsersScope bit,
3527    @ApplicationName NVARCHAR(256),
3528    @Count int OUT)
3529AS
3530BEGIN
3531    DECLARE @ApplicationId UNIQUEIDENTIFIER
3532    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3533    IF (@ApplicationId IS NULL)
3534        SELECT @Count = 0
3535    ELSE
3536    BEGIN
3537        IF (@AllUsersScope = 1)
3538            DELETE FROM aspnet_PersonalizationAllUsers
3539            WHERE PathId IN
3540               (SELECT Paths.PathId
3541                FROM dbo.aspnet_Paths Paths
3542                WHERE Paths.ApplicationId = @ApplicationId)
3543        ELSE
3544            DELETE FROM aspnet_PersonalizationPerUser
3545            WHERE PathId IN
3546               (SELECT Paths.PathId
3547                FROM dbo.aspnet_Paths Paths
3548                WHERE Paths.ApplicationId = @ApplicationId)
3549
3550        SELECT @Count = @@ROWCOUNT
3551    END
3552END' 
3553END
3554GO
3555SET ANSI_NULLS ON
3556GO
3557SET QUOTED_IDENTIFIER OFF
3558GO
3559IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings]') AND type in (N'P', N'PC'))
3560BEGIN
3561EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings] (
3562    @ApplicationName  NVARCHAR(256),
3563    @Path              NVARCHAR(256))
3564AS
3565BEGIN
3566    DECLARE @ApplicationId UNIQUEIDENTIFIER
3567    DECLARE @PathId UNIQUEIDENTIFIER
3568
3569    SELECT @ApplicationId = NULL
3570    SELECT @PathId = NULL
3571
3572    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3573    IF (@ApplicationId IS NULL)
3574    BEGIN
3575        RETURN
3576    END
3577
3578    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
3579    IF (@PathId IS NULL)
3580    BEGIN
3581        RETURN
3582    END
3583
3584    DELETE FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId
3585    RETURN 0
3586END' 
3587END
3588GO
3589SET ANSI_NULLS ON
3590GO
3591SET QUOTED_IDENTIFIER OFF
3592GO
3593IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAllUsers_GetPageSettings]') AND type in (N'P', N'PC'))
3594BEGIN
3595EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings] (
3596    @ApplicationName  NVARCHAR(256),
3597    @Path              NVARCHAR(256))
3598AS
3599BEGIN
3600    DECLARE @ApplicationId UNIQUEIDENTIFIER
3601    DECLARE @PathId UNIQUEIDENTIFIER
3602
3603    SELECT @ApplicationId = NULL
3604    SELECT @PathId = NULL
3605
3606    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3607    IF (@ApplicationId IS NULL)
3608    BEGIN
3609        RETURN
3610    END
3611
3612    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
3613    IF (@PathId IS NULL)
3614    BEGIN
3615        RETURN
3616    END
3617
3618    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationAllUsers p WHERE p.PathId = @PathId
3619END' 
3620END
3621GO
3622SET ANSI_NULLS ON
3623GO
3624SET QUOTED_IDENTIFIER OFF
3625GO
3626IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAdministration_ResetSharedState]') AND type in (N'P', N'PC'))
3627BEGIN
3628EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetSharedState] (
3629    @Count int OUT,
3630    @ApplicationName NVARCHAR(256),
3631    @Path NVARCHAR(256))
3632AS
3633BEGIN
3634    DECLARE @ApplicationId UNIQUEIDENTIFIER
3635    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3636    IF (@ApplicationId IS NULL)
3637        SELECT @Count = 0
3638    ELSE
3639    BEGIN
3640        DELETE FROM dbo.aspnet_PersonalizationAllUsers
3641        WHERE PathId IN
3642            (SELECT AllUsers.PathId
3643             FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
3644             WHERE Paths.ApplicationId = @ApplicationId
3645                   AND AllUsers.PathId = Paths.PathId
3646                   AND Paths.LoweredPath = LOWER(@Path))
3647
3648        SELECT @Count = @@ROWCOUNT
3649    END
3650END' 
3651END
3652GO
3653SET ANSI_NULLS ON
3654GO
3655SET QUOTED_IDENTIFIER OFF
3656GO
3657IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAdministration_FindState]') AND type in (N'P', N'PC'))
3658BEGIN
3659EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_FindState] (
3660    @AllUsersScope bit,
3661    @ApplicationName NVARCHAR(256),
3662    @PageIndex              INT,
3663    @PageSize               INT,
3664    @Path NVARCHAR(256) = NULL,
3665    @UserName NVARCHAR(256) = NULL,
3666    @InactiveSinceDate DATETIME = NULL)
3667AS
3668BEGIN
3669    DECLARE @ApplicationId UNIQUEIDENTIFIER
3670    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
3671    IF (@ApplicationId IS NULL)
3672        RETURN
3673
3674    -- Set the page bounds
3675    DECLARE @PageLowerBound INT
3676    DECLARE @PageUpperBound INT
3677    DECLARE @TotalRecords   INT
3678    SET @PageLowerBound = @PageSize * @PageIndex
3679    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
3680
3681    -- Create a temp table to store the selected results
3682    CREATE TABLE #PageIndex (
3683        IndexId int IDENTITY (0, 1) NOT NULL,
3684        ItemId UNIQUEIDENTIFIER
3685    )
3686
3687    IF (@AllUsersScope = 1)
3688    BEGIN
3689        -- Insert into our temp table
3690        INSERT INTO #PageIndex (ItemId)
3691        SELECT Paths.PathId
3692        FROM dbo.aspnet_Paths Paths,
3693             ((SELECT Paths.PathId
3694               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
3695               WHERE Paths.ApplicationId = @ApplicationId
3696                      AND AllUsers.PathId = Paths.PathId
3697                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
3698              ) AS SharedDataPerPath
3699              FULL OUTER JOIN
3700              (SELECT DISTINCT Paths.PathId
3701               FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
3702               WHERE Paths.ApplicationId = @ApplicationId
3703                      AND PerUser.PathId = Paths.PathId
3704                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
3705              ) AS UserDataPerPath
3706              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
3707             )
3708        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
3709        ORDER BY Paths.Path ASC
3710
3711        SELECT @TotalRecords = @@ROWCOUNT
3712
3713        SELECT Paths.Path,
3714               SharedDataPerPath.LastUpdatedDate,
3715               SharedDataPerPath.SharedDataLength,
3716               UserDataPerPath.UserDataLength,
3717               UserDataPerPath.UserCount
3718        FROM dbo.aspnet_Paths Paths,
3719             ((SELECT PageIndex.ItemId AS PathId,
3720                      AllUsers.LastUpdatedDate AS LastUpdatedDate,
3721                      DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
3722               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
3723               WHERE AllUsers.PathId = PageIndex.ItemId
3724                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
3725              ) AS SharedDataPerPath
3726              FULL OUTER JOIN
3727              (SELECT PageIndex.ItemId AS PathId,
3728                      SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
3729                      COUNT(*) AS UserCount
3730               FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
3731               WHERE PerUser.PathId = PageIndex.ItemId
3732                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
3733               GROUP BY PageIndex.ItemId
3734              ) AS UserDataPerPath
3735              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
3736             )
3737        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
3738        ORDER BY Paths.Path ASC
3739    END
3740    ELSE
3741    BEGIN
3742        -- Insert into our temp table
3743        INSERT INTO #PageIndex (ItemId)
3744        SELECT PerUser.Id
3745        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
3746        WHERE Paths.ApplicationId = @ApplicationId
3747              AND PerUser.UserId = Users.UserId
3748              AND PerUser.PathId = Paths.PathId
3749              AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
3750              AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
3751              AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
3752        ORDER BY Paths.Path ASC, Users.UserName ASC
3753
3754        SELECT @TotalRecords = @@ROWCOUNT
3755
3756        SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
3757        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
3758        WHERE PerUser.Id = PageIndex.ItemId
3759              AND PerUser.UserId = Users.UserId
3760              AND PerUser.PathId = Paths.PathId
3761              AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
3762        ORDER BY Paths.Path ASC, Users.UserName ASC
3763    END
3764
3765    RETURN @TotalRecords
3766END' 
3767END
3768GO
3769IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Me__Appli__145C0A3F]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Membership]'))
3770ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
3771REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
3772GO
3773IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Me__UserI__15502E78]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Membership]'))
3774ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([UserId])
3775REFERENCES [dbo].[aspnet_Users] ([UserId])
3776GO
3777IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Pr__UserI__29572725]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Profile]'))
3778ALTER TABLE [dbo].[aspnet_Profile]  WITH CHECK ADD FOREIGN KEY([UserId])
3779REFERENCES [dbo].[aspnet_Users] ([UserId])
3780GO
3781IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Us__RoleI__37A5467C]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles]'))
3782ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD FOREIGN KEY([RoleId])
3783REFERENCES [dbo].[aspnet_Roles] ([RoleId])
3784GO
3785IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Us__UserI__36B12243]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles]'))
3786ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD FOREIGN KEY([UserId])
3787REFERENCES [dbo].[aspnet_Users] ([UserId])
3788GO
3789IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Pe__PathI__4F7CD00D]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationPerUser]'))
3790ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD FOREIGN KEY([PathId])
3791REFERENCES [dbo].[aspnet_Paths] ([PathId])
3792GO
3793IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Pe__UserI__5070F446]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationPerUser]'))
3794ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD FOREIGN KEY([UserId])
3795REFERENCES [dbo].[aspnet_Users] ([UserId])
3796GO
3797IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Pe__PathI__4BAC3F29]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_PersonalizationAllUsers]'))
3798ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]  WITH CHECK ADD FOREIGN KEY([PathId])
3799REFERENCES [dbo].[aspnet_Paths] ([PathId])
3800GO
3801IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Ro__Appli__32E0915F]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Roles]'))
3802ALTER TABLE [dbo].[aspnet_Roles]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
3803REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
3804GO
3805IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Pa__Appli__45F365D3]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Paths]'))
3806ALTER TABLE [dbo].[aspnet_Paths]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
3807REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
3808GO
3809IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Us__Appli__0425A276]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Users]'))
3810ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
3811REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])