MagnZki5

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