· 10 years ago · Jul 26, 2016, 06:15 PM
1SET ANSI_NULLS ON
2GO
3SET QUOTED_IDENTIFIER OFF
4GO
5IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Setup_RestorePermissions]') AND type in (N'P', N'PC'))
6BEGIN
7EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Setup_RestorePermissions]
8 @name sysname
9AS
10BEGIN
11 DECLARE @object sysname
12 DECLARE @protectType char(10)
13 DECLARE @action varchar(60)
14 DECLARE @grantee sysname
15 DECLARE @cmd nvarchar(500)
16 DECLARE c1 cursor FORWARD_ONLY FOR
17 SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
18
19 OPEN c1
20
21 FETCH c1 INTO @object, @protectType, @action, @grantee
22 WHILE (@@fetch_status = 0)
23 BEGIN
24 SET @cmd = @protectType + '' '' + @action + '' on '' + @object + '' TO ['' + @grantee + '']''
25 EXEC (@cmd)
26 FETCH c1 INTO @object, @protectType, @action, @grantee
27 END
28
29 CLOSE c1
30 DEALLOCATE c1
31END'
32END
33GO
34/****** Object: StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers] Script Date: 04/09/2012 20:01:32 ******/
35SET ANSI_NULLS ON
36GO
37SET QUOTED_IDENTIFIER OFF
38GO
39IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Setup_RemoveAllRoleMembers]') AND type in (N'P', N'PC'))
40BEGIN
41EXEC dbo.sp_executesql @statement = N'
42CREATE PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers]
43 @name sysname
44AS
45BEGIN
46 CREATE TABLE #aspnet_RoleMembers
47 (
48 Group_name sysname,
49 Group_id smallint,
50 Users_in_group sysname,
51 User_id smallint
52 )
53
54 INSERT INTO #aspnet_RoleMembers
55 EXEC sp_helpuser @name
56
57 DECLARE @user_id smallint
58 DECLARE @cmd nvarchar(500)
59 DECLARE c1 cursor FORWARD_ONLY FOR
60 SELECT User_id FROM #aspnet_RoleMembers
61
62 OPEN c1
63
64 FETCH c1 INTO @user_id
65 WHILE (@@fetch_status = 0)
66 BEGIN
67 SET @cmd = ''EXEC sp_droprolemember '' + '''''''' + @name + '''''', '''''' + USER_NAME(@user_id) + ''''''''
68 EXEC (@cmd)
69 FETCH c1 INTO @user_id
70 END
71
72 CLOSE c1
73 DEALLOCATE c1
74END'
75END
76GO
77/****** Object: StoredProcedure [dbo].[aspnet_UnRegisterSchemaVersion] Script Date: 04/09/2012 20:01:32 ******/
78SET ANSI_NULLS ON
79GO
80SET QUOTED_IDENTIFIER OFF
81GO
82IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UnRegisterSchemaVersion]') AND type in (N'P', N'PC'))
83BEGIN
84EXEC dbo.sp_executesql @statement = N'
85CREATE PROCEDURE [dbo].[aspnet_UnRegisterSchemaVersion]
86 @Feature nvarchar(128),
87 @CompatibleSchemaVersion nvarchar(128)
88AS
89BEGIN
90 DELETE FROM dbo.aspnet_SchemaVersions
91 WHERE Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
92END'
93END
94GO
95/****** Object: StoredProcedure [dbo].[aspnet_CheckSchemaVersion] Script Date: 04/09/2012 20:01:31 ******/
96SET ANSI_NULLS ON
97GO
98SET QUOTED_IDENTIFIER OFF
99GO
100IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_CheckSchemaVersion]') AND type in (N'P', N'PC'))
101BEGIN
102EXEC dbo.sp_executesql @statement = N'
103CREATE PROCEDURE [dbo].[aspnet_CheckSchemaVersion]
104 @Feature nvarchar(128),
105 @CompatibleSchemaVersion nvarchar(128)
106AS
107BEGIN
108 IF (EXISTS( SELECT *
109 FROM dbo.aspnet_SchemaVersions
110 WHERE Feature = LOWER( @Feature ) AND
111 CompatibleSchemaVersion = @CompatibleSchemaVersion ))
112 RETURN 0
113
114 RETURN 1
115END'
116END
117GO
118/****** Object: StoredProcedure [dbo].[aspnet_Applications_CreateApplication] Script Date: 04/09/2012 20:01:31 ******/
119SET ANSI_NULLS ON
120GO
121SET QUOTED_IDENTIFIER OFF
122GO
123IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Applications_CreateApplication]') AND type in (N'P', N'PC'))
124BEGIN
125EXEC dbo.sp_executesql @statement = N'
126CREATE PROCEDURE [dbo].[aspnet_Applications_CreateApplication]
127 @ApplicationName nvarchar(256),
128 @ApplicationId uniqueidentifier OUTPUT
129AS
130BEGIN
131 SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
132
133 IF(@ApplicationId IS NULL)
134 BEGIN
135 DECLARE @TranStarted bit
136 SET @TranStarted = 0
137
138 IF( @@TRANCOUNT = 0 )
139 BEGIN
140 BEGIN TRANSACTION
141 SET @TranStarted = 1
142 END
143 ELSE
144 SET @TranStarted = 0
145
146 SELECT @ApplicationId = ApplicationId
147 FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
148 WHERE LOWER(@ApplicationName) = LoweredApplicationName
149
150 IF(@ApplicationId IS NULL)
151 BEGIN
152 SELECT @ApplicationId = NEWID()
153 INSERT dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
154 VALUES (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
155 END
156
157
158 IF( @TranStarted = 1 )
159 BEGIN
160 IF(@@ERROR = 0)
161 BEGIN
162 SET @TranStarted = 0
163 COMMIT TRANSACTION
164 END
165 ELSE
166 BEGIN
167 SET @TranStarted = 0
168 ROLLBACK TRANSACTION
169 END
170 END
171 END
172END'
173END
174GO
175/****** Object: StoredProcedure [dbo].[aspnet_RegisterSchemaVersion] Script Date: 04/09/2012 20:01:31 ******/
176SET ANSI_NULLS ON
177GO
178SET QUOTED_IDENTIFIER OFF
179GO
180IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_RegisterSchemaVersion]') AND type in (N'P', N'PC'))
181BEGIN
182EXEC dbo.sp_executesql @statement = N'
183CREATE PROCEDURE [dbo].[aspnet_RegisterSchemaVersion]
184 @Feature nvarchar(128),
185 @CompatibleSchemaVersion nvarchar(128),
186 @IsCurrentVersion bit,
187 @RemoveIncompatibleSchema bit
188AS
189BEGIN
190 IF( @RemoveIncompatibleSchema = 1 )
191 BEGIN
192 DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
193 END
194 ELSE
195 BEGIN
196 IF( @IsCurrentVersion = 1 )
197 BEGIN
198 UPDATE dbo.aspnet_SchemaVersions
199 SET IsCurrentVersion = 0
200 WHERE Feature = LOWER( @Feature )
201 END
202 END
203
204 INSERT dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
205 VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
206END'
207END
208GO
209/****** Object: StoredProcedure [dbo].[aspnet_Roles_RoleExists] Script Date: 04/09/2012 20:01:32 ******/
210SET ANSI_NULLS ON
211GO
212SET QUOTED_IDENTIFIER OFF
213GO
214IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_RoleExists]') AND type in (N'P', N'PC'))
215BEGIN
216EXEC dbo.sp_executesql @statement = N'
217CREATE PROCEDURE [dbo].[aspnet_Roles_RoleExists]
218 @ApplicationName nvarchar(256),
219 @RoleName nvarchar(256)
220AS
221BEGIN
222 DECLARE @ApplicationId uniqueidentifier
223 SELECT @ApplicationId = NULL
224 SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
225 IF (@ApplicationId IS NULL)
226 RETURN(0)
227 IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
228 RETURN(1)
229 ELSE
230 RETURN(0)
231END'
232END
233GO
234/****** Object: StoredProcedure [dbo].[aspnet_Roles_GetAllRoles] Script Date: 04/09/2012 20:01:32 ******/
235SET ANSI_NULLS ON
236GO
237SET QUOTED_IDENTIFIER OFF
238GO
239IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_GetAllRoles]') AND type in (N'P', N'PC'))
240BEGIN
241EXEC dbo.sp_executesql @statement = N'
242CREATE PROCEDURE [dbo].[aspnet_Roles_GetAllRoles] (
243 @ApplicationName nvarchar(256))
244AS
245BEGIN
246 DECLARE @ApplicationId uniqueidentifier
247 SELECT @ApplicationId = NULL
248 SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
249 IF (@ApplicationId IS NULL)
250 RETURN
251 SELECT RoleName
252 FROM dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
253 ORDER BY RoleName
254END'
255END
256GO
257/****** Object: StoredProcedure [dbo].[aspnet_Roles_CreateRole] Script Date: 04/09/2012 20:01:31 ******/
258SET ANSI_NULLS ON
259GO
260SET QUOTED_IDENTIFIER OFF
261GO
262IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_CreateRole]') AND type in (N'P', N'PC'))
263BEGIN
264EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Roles_CreateRole]
265 @ApplicationName nvarchar(256),
266 @RoleName nvarchar(256)
267AS
268BEGIN
269 DECLARE @ApplicationId uniqueidentifier
270 SELECT @ApplicationId = NULL
271
272 DECLARE @ErrorCode int
273 SET @ErrorCode = 0
274
275 DECLARE @TranStarted bit
276 SET @TranStarted = 0
277
278 IF( @@TRANCOUNT = 0 )
279 BEGIN
280 BEGIN TRANSACTION
281 SET @TranStarted = 1
282 END
283 ELSE
284 SET @TranStarted = 0
285
286 EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
287
288 IF( @@ERROR <> 0 )
289 BEGIN
290 SET @ErrorCode = -1
291 GOTO Cleanup
292 END
293
294 IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
295 BEGIN
296 SET @ErrorCode = 1
297 GOTO Cleanup
298 END
299
300 INSERT INTO dbo.aspnet_Roles
301 (ApplicationId, RoleName, LoweredRoleName)
302 VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
303
304 IF( @@ERROR <> 0 )
305 BEGIN
306 SET @ErrorCode = -1
307 GOTO Cleanup
308 END
309
310 IF( @TranStarted = 1 )
311 BEGIN
312 SET @TranStarted = 0
313 COMMIT TRANSACTION
314 END
315
316 RETURN(0)
317
318Cleanup:
319
320 IF( @TranStarted = 1 )
321 BEGIN
322 SET @TranStarted = 0
323 ROLLBACK TRANSACTION
324 END
325
326 RETURN @ErrorCode
327
328END'
329END
330GO
331/****** Object: StoredProcedure [dbo].[aspnet_Users_CreateUser] Script Date: 04/09/2012 20:01:32 ******/
332SET ANSI_NULLS ON
333GO
334SET QUOTED_IDENTIFIER OFF
335GO
336IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users_CreateUser]') AND type in (N'P', N'PC'))
337BEGIN
338EXEC dbo.sp_executesql @statement = N'
339CREATE PROCEDURE [dbo].[aspnet_Users_CreateUser]
340 @ApplicationId uniqueidentifier,
341 @UserName nvarchar(256),
342 @IsUserAnonymous bit,
343 @LastActivityDate DATETIME,
344 @UserId uniqueidentifier OUTPUT
345AS
346BEGIN
347 IF( @UserId IS NULL )
348 SELECT @UserId = NEWID()
349 ELSE
350 BEGIN
351 IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
352 WHERE @UserId = UserId ) )
353 RETURN -1
354 END
355
356 INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
357 VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
358
359 RETURN 0
360END'
361END
362GO
363/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 04/09/2012 20:01:32 ******/
364SET ANSI_NULLS ON
365GO
366SET QUOTED_IDENTIFIER OFF
367GO
368IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]') AND type in (N'P', N'PC'))
369BEGIN
370EXEC dbo.sp_executesql @statement = N'
371CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
372 @ApplicationName nvarchar(256),
373 @UserNames nvarchar(4000),
374 @RoleNames nvarchar(4000)
375AS
376BEGIN
377 DECLARE @AppId uniqueidentifier
378 SELECT @AppId = NULL
379 SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
380 IF (@AppId IS NULL)
381 RETURN(2)
382
383
384 DECLARE @TranStarted bit
385 SET @TranStarted = 0
386
387 IF( @@TRANCOUNT = 0 )
388 BEGIN
389 BEGIN TRANSACTION
390 SET @TranStarted = 1
391 END
392
393 DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
394 DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
395 DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
396 DECLARE @Num int
397 DECLARE @Pos int
398 DECLARE @NextPos int
399 DECLARE @Name nvarchar(256)
400 DECLARE @CountAll int
401 DECLARE @CountU int
402 DECLARE @CountR int
403
404
405 SET @Num = 0
406 SET @Pos = 1
407 WHILE(@Pos <= LEN(@RoleNames))
408 BEGIN
409 SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos)
410 IF (@NextPos = 0 OR @NextPos IS NULL)
411 SELECT @NextPos = LEN(@RoleNames) + 1
412 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
413 SELECT @Pos = @NextPos+1
414
415 INSERT INTO @tbNames VALUES (@Name)
416 SET @Num = @Num + 1
417 END
418
419 INSERT INTO @tbRoles
420 SELECT RoleId
421 FROM dbo.aspnet_Roles ar, @tbNames t
422 WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
423 SELECT @CountR = @@ROWCOUNT
424
425 IF (@CountR <> @Num)
426 BEGIN
427 SELECT TOP 1 N'''', Name
428 FROM @tbNames
429 WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
430 IF( @TranStarted = 1 )
431 ROLLBACK TRANSACTION
432 RETURN(2)
433 END
434
435
436 DELETE FROM @tbNames WHERE 1=1
437 SET @Num = 0
438 SET @Pos = 1
439
440
441 WHILE(@Pos <= LEN(@UserNames))
442 BEGIN
443 SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos)
444 IF (@NextPos = 0 OR @NextPos IS NULL)
445 SELECT @NextPos = LEN(@UserNames) + 1
446 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
447 SELECT @Pos = @NextPos+1
448
449 INSERT INTO @tbNames VALUES (@Name)
450 SET @Num = @Num + 1
451 END
452
453 INSERT INTO @tbUsers
454 SELECT UserId
455 FROM dbo.aspnet_Users ar, @tbNames t
456 WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
457
458 SELECT @CountU = @@ROWCOUNT
459 IF (@CountU <> @Num)
460 BEGIN
461 SELECT TOP 1 Name, N''''
462 FROM @tbNames
463 WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)
464
465 IF( @TranStarted = 1 )
466 ROLLBACK TRANSACTION
467 RETURN(1)
468 END
469
470 SELECT @CountAll = COUNT(*)
471 FROM dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
472 WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId
473
474 IF (@CountAll <> @CountU * @CountR)
475 BEGIN
476 SELECT TOP 1 UserName, RoleName
477 FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
478 WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
479 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
480 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
481 IF( @TranStarted = 1 )
482 ROLLBACK TRANSACTION
483 RETURN(3)
484 END
485
486 DELETE FROM dbo.aspnet_UsersInRoles
487 WHERE UserId IN (SELECT UserId FROM @tbUsers)
488 AND RoleId IN (SELECT RoleId FROM @tbRoles)
489 IF( @TranStarted = 1 )
490 COMMIT TRANSACTION
491 RETURN(0)
492END
493 '
494END
495GO
496/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] Script Date: 04/09/2012 20:01:32 ******/
497SET ANSI_NULLS ON
498GO
499SET QUOTED_IDENTIFIER OFF
500GO
501IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_IsUserInRole]') AND type in (N'P', N'PC'))
502BEGIN
503EXEC dbo.sp_executesql @statement = N'
504CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
505 @ApplicationName nvarchar(256),
506 @UserName nvarchar(256),
507 @RoleName nvarchar(256)
508AS
509BEGIN
510 DECLARE @ApplicationId uniqueidentifier
511 SELECT @ApplicationId = NULL
512 SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
513 IF (@ApplicationId IS NULL)
514 RETURN(2)
515 DECLARE @UserId uniqueidentifier
516 SELECT @UserId = NULL
517 DECLARE @RoleId uniqueidentifier
518 SELECT @RoleId = NULL
519
520 SELECT @UserId = UserId
521 FROM dbo.aspnet_Users
522 WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
523
524 IF (@UserId IS NULL)
525 RETURN(2)
526
527 SELECT @RoleId = RoleId
528 FROM dbo.aspnet_Roles
529 WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
530
531 IF (@RoleId IS NULL)
532 RETURN(3)
533
534 IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId))
535 RETURN(1)
536 ELSE
537 RETURN(0)
538END'
539END
540GO
541/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetUsersInRoles] Script Date: 04/09/2012 20:01:32 ******/
542SET ANSI_NULLS ON
543GO
544SET QUOTED_IDENTIFIER OFF
545GO
546IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_GetUsersInRoles]') AND type in (N'P', N'PC'))
547BEGIN
548EXEC dbo.sp_executesql @statement = N'
549CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetUsersInRoles]
550 @ApplicationName nvarchar(256),
551 @RoleName nvarchar(256)
552AS
553BEGIN
554 DECLARE @ApplicationId uniqueidentifier
555 SELECT @ApplicationId = NULL
556 SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
557 IF (@ApplicationId IS NULL)
558 RETURN(1)
559 DECLARE @RoleId uniqueidentifier
560 SELECT @RoleId = NULL
561
562 SELECT @RoleId = RoleId
563 FROM dbo.aspnet_Roles
564 WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
565
566 IF (@RoleId IS NULL)
567 RETURN(1)
568
569 SELECT u.UserName
570 FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
571 WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
572 ORDER BY u.UserName
573 RETURN(0)
574END'
575END
576GO
577/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_GetRolesForUser] Script Date: 04/09/2012 20:01:32 ******/
578SET ANSI_NULLS ON
579GO
580SET QUOTED_IDENTIFIER OFF
581GO
582IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_GetRolesForUser]') AND type in (N'P', N'PC'))
583BEGIN
584EXEC dbo.sp_executesql @statement = N'
585CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetRolesForUser]
586 @ApplicationName nvarchar(256),
587 @UserName nvarchar(256)
588AS
589BEGIN
590 DECLARE @ApplicationId uniqueidentifier
591 SELECT @ApplicationId = NULL
592 SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
593 IF (@ApplicationId IS NULL)
594 RETURN(1)
595 DECLARE @UserId uniqueidentifier
596 SELECT @UserId = NULL
597
598 SELECT @UserId = UserId
599 FROM dbo.aspnet_Users
600 WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
601
602 IF (@UserId IS NULL)
603 RETURN(1)
604
605 SELECT r.RoleName
606 FROM dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
607 WHERE r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
608 ORDER BY r.RoleName
609 RETURN (0)
610END'
611END
612GO
613/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_FindUsersInRole] Script Date: 04/09/2012 20:01:32 ******/
614SET ANSI_NULLS ON
615GO
616SET QUOTED_IDENTIFIER OFF
617GO
618IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_FindUsersInRole]') AND type in (N'P', N'PC'))
619BEGIN
620EXEC dbo.sp_executesql @statement = N'
621CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_FindUsersInRole]
622 @ApplicationName nvarchar(256),
623 @RoleName nvarchar(256),
624 @UserNameToMatch nvarchar(256)
625AS
626BEGIN
627 DECLARE @ApplicationId uniqueidentifier
628 SELECT @ApplicationId = NULL
629 SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
630 IF (@ApplicationId IS NULL)
631 RETURN(1)
632 DECLARE @RoleId uniqueidentifier
633 SELECT @RoleId = NULL
634
635 SELECT @RoleId = RoleId
636 FROM dbo.aspnet_Roles
637 WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
638
639 IF (@RoleId IS NULL)
640 RETURN(1)
641
642 SELECT u.UserName
643 FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
644 WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
645 ORDER BY u.UserName
646 RETURN(0)
647END'
648END
649GO
650/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles] Script Date: 04/09/2012 20:01:32 ******/
651SET ANSI_NULLS ON
652GO
653SET QUOTED_IDENTIFIER OFF
654GO
655IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_AddUsersToRoles]') AND type in (N'P', N'PC'))
656BEGIN
657EXEC dbo.sp_executesql @statement = N'
658CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
659 @ApplicationName nvarchar(256),
660 @UserNames nvarchar(4000),
661 @RoleNames nvarchar(4000),
662 @CurrentTimeUtc datetime
663AS
664BEGIN
665 DECLARE @AppId uniqueidentifier
666 SELECT @AppId = NULL
667 SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
668 IF (@AppId IS NULL)
669 RETURN(2)
670 DECLARE @TranStarted bit
671 SET @TranStarted = 0
672
673 IF( @@TRANCOUNT = 0 )
674 BEGIN
675 BEGIN TRANSACTION
676 SET @TranStarted = 1
677 END
678
679 DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
680 DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
681 DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
682 DECLARE @Num int
683 DECLARE @Pos int
684 DECLARE @NextPos int
685 DECLARE @Name nvarchar(256)
686
687 SET @Num = 0
688 SET @Pos = 1
689 WHILE(@Pos <= LEN(@RoleNames))
690 BEGIN
691 SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos)
692 IF (@NextPos = 0 OR @NextPos IS NULL)
693 SELECT @NextPos = LEN(@RoleNames) + 1
694 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
695 SELECT @Pos = @NextPos+1
696
697 INSERT INTO @tbNames VALUES (@Name)
698 SET @Num = @Num + 1
699 END
700
701 INSERT INTO @tbRoles
702 SELECT RoleId
703 FROM dbo.aspnet_Roles ar, @tbNames t
704 WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
705
706 IF (@@ROWCOUNT <> @Num)
707 BEGIN
708 SELECT TOP 1 Name
709 FROM @tbNames
710 WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
711 IF( @TranStarted = 1 )
712 ROLLBACK TRANSACTION
713 RETURN(2)
714 END
715
716 DELETE FROM @tbNames WHERE 1=1
717 SET @Num = 0
718 SET @Pos = 1
719
720 WHILE(@Pos <= LEN(@UserNames))
721 BEGIN
722 SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos)
723 IF (@NextPos = 0 OR @NextPos IS NULL)
724 SELECT @NextPos = LEN(@UserNames) + 1
725 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
726 SELECT @Pos = @NextPos+1
727
728 INSERT INTO @tbNames VALUES (@Name)
729 SET @Num = @Num + 1
730 END
731
732 INSERT INTO @tbUsers
733 SELECT UserId
734 FROM dbo.aspnet_Users ar, @tbNames t
735 WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
736
737 IF (@@ROWCOUNT <> @Num)
738 BEGIN
739 DELETE FROM @tbNames
740 WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE au.UserId = u.UserId)
741
742 INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
743 SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
744 FROM @tbNames
745
746 INSERT INTO @tbUsers
747 SELECT UserId
748 FROM dbo.aspnet_Users au, @tbNames t
749 WHERE LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
750 END
751
752 IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
753 BEGIN
754 SELECT TOP 1 UserName, RoleName
755 FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
756 WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId
757
758 IF( @TranStarted = 1 )
759 ROLLBACK TRANSACTION
760 RETURN(3)
761 END
762
763 INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
764 SELECT UserId, RoleId
765 FROM @tbUsers, @tbRoles
766
767 IF( @TranStarted = 1 )
768 COMMIT TRANSACTION
769 RETURN(0)
770END '
771END
772GO
773/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 04/09/2012 20:01:32 ******/
774SET ANSI_NULLS ON
775GO
776SET QUOTED_IDENTIFIER OFF
777GO
778IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND type in (N'P', N'PC'))
779BEGIN
780EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
781 @ApplicationName nvarchar(256),
782 @UserName nvarchar(256),
783 @TablesToDeleteFrom int,
784 @NumTablesDeletedFrom int OUTPUT
785AS
786BEGIN
787 DECLARE @UserId uniqueidentifier
788 SELECT @UserId = NULL
789 SELECT @NumTablesDeletedFrom = 0
790
791 DECLARE @TranStarted bit
792 SET @TranStarted = 0
793
794 IF( @@TRANCOUNT = 0 )
795 BEGIN
796 BEGIN TRANSACTION
797 SET @TranStarted = 1
798 END
799 ELSE
800 SET @TranStarted = 0
801
802 DECLARE @ErrorCode int
803 DECLARE @RowCount int
804
805 SET @ErrorCode = 0
806 SET @RowCount = 0
807
808 SELECT @UserId = u.UserId
809 FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
810 WHERE u.LoweredUserName = LOWER(@UserName)
811 AND u.ApplicationId = a.ApplicationId
812 AND LOWER(@ApplicationName) = a.LoweredApplicationName
813
814 IF (@UserId IS NULL)
815 BEGIN
816 GOTO Cleanup
817 END
818
819 -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
820 IF ((@TablesToDeleteFrom & 1) <> 0 AND
821 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
822 BEGIN
823 DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
824
825 SELECT @ErrorCode = @@ERROR,
826 @RowCount = @@ROWCOUNT
827
828 IF( @ErrorCode <> 0 )
829 GOTO Cleanup
830
831 IF (@RowCount <> 0)
832 SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
833 END
834
835 -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
836 IF ((@TablesToDeleteFrom & 2) <> 0 AND
837 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )
838 BEGIN
839 DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
840
841 SELECT @ErrorCode = @@ERROR,
842 @RowCount = @@ROWCOUNT
843
844 IF( @ErrorCode <> 0 )
845 GOTO Cleanup
846
847 IF (@RowCount <> 0)
848 SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
849 END
850
851 -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
852 IF ((@TablesToDeleteFrom & 4) <> 0 AND
853 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
854 BEGIN
855 DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
856
857 SELECT @ErrorCode = @@ERROR,
858 @RowCount = @@ROWCOUNT
859
860 IF( @ErrorCode <> 0 )
861 GOTO Cleanup
862
863 IF (@RowCount <> 0)
864 SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
865 END
866
867 -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
868 IF ((@TablesToDeleteFrom & 8) <> 0 AND
869 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
870 BEGIN
871 DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
872
873 SELECT @ErrorCode = @@ERROR,
874 @RowCount = @@ROWCOUNT
875
876 IF( @ErrorCode <> 0 )
877 GOTO Cleanup
878
879 IF (@RowCount <> 0)
880 SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
881 END
882
883 -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
884 IF ((@TablesToDeleteFrom & 1) <> 0 AND
885 (@TablesToDeleteFrom & 2) <> 0 AND
886 (@TablesToDeleteFrom & 4) <> 0 AND
887 (@TablesToDeleteFrom & 8) <> 0 AND
888 (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
889 BEGIN
890 DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
891
892 SELECT @ErrorCode = @@ERROR,
893 @RowCount = @@ROWCOUNT
894
895 IF( @ErrorCode <> 0 )
896 GOTO Cleanup
897
898 IF (@RowCount <> 0)
899 SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
900 END
901
902 IF( @TranStarted = 1 )
903 BEGIN
904 SET @TranStarted = 0
905 COMMIT TRANSACTION
906 END
907
908 RETURN 0
909
910Cleanup:
911 SET @NumTablesDeletedFrom = 0
912
913 IF( @TranStarted = 1 )
914 BEGIN
915 SET @TranStarted = 0
916 ROLLBACK TRANSACTION
917 END
918
919 RETURN @ErrorCode
920
921END'
922END
923GO
924/****** Object: StoredProcedure [dbo].[aspnet_Roles_DeleteRole] Script Date: 04/09/2012 20:01:32 ******/
925SET ANSI_NULLS ON
926GO
927SET QUOTED_IDENTIFIER OFF
928GO
929IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Roles_DeleteRole]') AND type in (N'P', N'PC'))
930BEGIN
931EXEC dbo.sp_executesql @statement = N'
932CREATE PROCEDURE [dbo].[aspnet_Roles_DeleteRole]
933 @ApplicationName nvarchar(256),
934 @RoleName nvarchar(256),
935 @DeleteOnlyIfRoleIsEmpty bit
936AS
937BEGIN
938 DECLARE @ApplicationId uniqueidentifier
939 SELECT @ApplicationId = NULL
940 SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
941 IF (@ApplicationId IS NULL)
942 RETURN(1)
943
944 DECLARE @ErrorCode int
945 SET @ErrorCode = 0
946
947 DECLARE @TranStarted bit
948 SET @TranStarted = 0
949
950 IF( @@TRANCOUNT = 0 )
951 BEGIN
952 BEGIN TRANSACTION
953 SET @TranStarted = 1
954 END
955 ELSE
956 SET @TranStarted = 0
957
958 DECLARE @RoleId uniqueidentifier
959 SELECT @RoleId = NULL
960 SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
961
962 IF (@RoleId IS NULL)
963 BEGIN
964 SELECT @ErrorCode = 1
965 GOTO Cleanup
966 END
967 IF (@DeleteOnlyIfRoleIsEmpty <> 0)
968 BEGIN
969 IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId))
970 BEGIN
971 SELECT @ErrorCode = 2
972 GOTO Cleanup
973 END
974 END
975
976
977 DELETE FROM dbo.aspnet_UsersInRoles WHERE @RoleId = RoleId
978
979 IF( @@ERROR <> 0 )
980 BEGIN
981 SET @ErrorCode = -1
982 GOTO Cleanup
983 END
984
985 DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId AND ApplicationId = @ApplicationId
986
987 IF( @@ERROR <> 0 )
988 BEGIN
989 SET @ErrorCode = -1
990 GOTO Cleanup
991 END
992
993 IF( @TranStarted = 1 )
994 BEGIN
995 SET @TranStarted = 0
996 COMMIT TRANSACTION
997 END
998
999 RETURN(0)
1000
1001Cleanup:
1002
1003 IF( @TranStarted = 1 )
1004 BEGIN
1005 SET @TranStarted = 0
1006 ROLLBACK TRANSACTION
1007 END
1008
1009 RETURN @ErrorCode
1010END'
1011END
1012GO
1013/****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUserInfo] Script Date: 04/09/2012 20:01:31 ******/
1014SET ANSI_NULLS ON
1015GO
1016SET QUOTED_IDENTIFIER OFF
1017GO
1018IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UpdateUserInfo]') AND type in (N'P', N'PC'))
1019BEGIN
1020EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUserInfo]
1021 @ApplicationName nvarchar(256),
1022 @UserName nvarchar(256),
1023 @IsPasswordCorrect bit,
1024 @UpdateLastLoginActivityDate bit,
1025 @MaxInvalidPasswordAttempts int,
1026 @PasswordAttemptWindow int,
1027 @CurrentTimeUtc datetime,
1028 @LastLoginDate datetime,
1029 @LastActivityDate datetime
1030AS
1031BEGIN
1032 DECLARE @UserId uniqueidentifier
1033 DECLARE @IsApproved bit
1034 DECLARE @IsLockedOut bit
1035 DECLARE @LastLockoutDate datetime
1036 DECLARE @FailedPasswordAttemptCount int
1037 DECLARE @FailedPasswordAttemptWindowStart datetime
1038 DECLARE @FailedPasswordAnswerAttemptCount int
1039 DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1040
1041 DECLARE @ErrorCode int
1042 SET @ErrorCode = 0
1043
1044 DECLARE @TranStarted bit
1045 SET @TranStarted = 0
1046
1047 IF( @@TRANCOUNT = 0 )
1048 BEGIN
1049 BEGIN TRANSACTION
1050 SET @TranStarted = 1
1051 END
1052 ELSE
1053 SET @TranStarted = 0
1054
1055 SELECT @UserId = u.UserId,
1056 @IsApproved = m.IsApproved,
1057 @IsLockedOut = m.IsLockedOut,
1058 @LastLockoutDate = m.LastLockoutDate,
1059 @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
1060 @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
1061 @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
1062 @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
1063 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
1064 WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
1065 u.ApplicationId = a.ApplicationId AND
1066 u.UserId = m.UserId AND
1067 LOWER(@UserName) = u.LoweredUserName
1068
1069 IF ( @@rowcount = 0 )
1070 BEGIN
1071 SET @ErrorCode = 1
1072 GOTO Cleanup
1073 END
1074
1075 IF( @IsLockedOut = 1 )
1076 BEGIN
1077 GOTO Cleanup
1078 END
1079
1080 IF( @IsPasswordCorrect = 0 )
1081 BEGIN
1082 IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
1083 BEGIN
1084 SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
1085 SET @FailedPasswordAttemptCount = 1
1086 END
1087 ELSE
1088 BEGIN
1089 SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
1090 SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
1091 END
1092
1093 BEGIN
1094 IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
1095 BEGIN
1096 SET @IsLockedOut = 1
1097 SET @LastLockoutDate = @CurrentTimeUtc
1098 END
1099 END
1100 END
1101 ELSE
1102 BEGIN
1103 IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
1104 BEGIN
1105 SET @FailedPasswordAttemptCount = 0
1106 SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
1107 SET @FailedPasswordAnswerAttemptCount = 0
1108 SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
1109 SET @LastLockoutDate = CONVERT( datetime, ''17540101'', 112 )
1110 END
1111 END
1112
1113 IF( @UpdateLastLoginActivityDate = 1 )
1114 BEGIN
1115 UPDATE dbo.aspnet_Users
1116 SET LastActivityDate = @LastActivityDate
1117 WHERE @UserId = UserId
1118
1119 IF( @@ERROR <> 0 )
1120 BEGIN
1121 SET @ErrorCode = -1
1122 GOTO Cleanup
1123 END
1124
1125 UPDATE dbo.aspnet_Membership
1126 SET LastLoginDate = @LastLoginDate
1127 WHERE UserId = @UserId
1128
1129 IF( @@ERROR <> 0 )
1130 BEGIN
1131 SET @ErrorCode = -1
1132 GOTO Cleanup
1133 END
1134 END
1135
1136
1137 UPDATE dbo.aspnet_Membership
1138 SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1139 FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1140 FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1141 FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1142 FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1143 WHERE @UserId = UserId
1144
1145 IF( @@ERROR <> 0 )
1146 BEGIN
1147 SET @ErrorCode = -1
1148 GOTO Cleanup
1149 END
1150
1151 IF( @TranStarted = 1 )
1152 BEGIN
1153 SET @TranStarted = 0
1154 COMMIT TRANSACTION
1155 END
1156
1157 RETURN @ErrorCode
1158
1159Cleanup:
1160
1161 IF( @TranStarted = 1 )
1162 BEGIN
1163 SET @TranStarted = 0
1164 ROLLBACK TRANSACTION
1165 END
1166
1167 RETURN @ErrorCode
1168
1169END'
1170END
1171GO
1172/****** Object: StoredProcedure [dbo].[aspnet_Membership_UpdateUser] Script Date: 04/09/2012 20:01:31 ******/
1173SET ANSI_NULLS ON
1174GO
1175SET QUOTED_IDENTIFIER OFF
1176GO
1177IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UpdateUser]') AND type in (N'P', N'PC'))
1178BEGIN
1179EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUser]
1180 @ApplicationName nvarchar(256),
1181 @UserName nvarchar(256),
1182 @Email nvarchar(256),
1183 @Comment ntext,
1184 @IsApproved bit,
1185 @LastLoginDate datetime,
1186 @LastActivityDate datetime,
1187 @UniqueEmail int,
1188 @CurrentTimeUtc datetime
1189AS
1190BEGIN
1191 DECLARE @UserId uniqueidentifier
1192 DECLARE @ApplicationId uniqueidentifier
1193 SELECT @UserId = NULL
1194 SELECT @UserId = u.UserId, @ApplicationId = a.ApplicationId
1195 FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1196 WHERE LoweredUserName = LOWER(@UserName) AND
1197 u.ApplicationId = a.ApplicationId AND
1198 LOWER(@ApplicationName) = a.LoweredApplicationName AND
1199 u.UserId = m.UserId
1200
1201 IF (@UserId IS NULL)
1202 RETURN(1)
1203
1204 IF (@UniqueEmail = 1)
1205 BEGIN
1206 IF (EXISTS (SELECT *
1207 FROM dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
1208 WHERE ApplicationId = @ApplicationId AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
1209 BEGIN
1210 RETURN(7)
1211 END
1212 END
1213
1214 DECLARE @TranStarted bit
1215 SET @TranStarted = 0
1216
1217 IF( @@TRANCOUNT = 0 )
1218 BEGIN
1219 BEGIN TRANSACTION
1220 SET @TranStarted = 1
1221 END
1222 ELSE
1223 SET @TranStarted = 0
1224
1225 UPDATE dbo.aspnet_Users WITH (ROWLOCK)
1226 SET
1227 LastActivityDate = @LastActivityDate
1228 WHERE
1229 @UserId = UserId
1230
1231 IF( @@ERROR <> 0 )
1232 GOTO Cleanup
1233
1234 UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
1235 SET
1236 Email = @Email,
1237 LoweredEmail = LOWER(@Email),
1238 Comment = @Comment,
1239 IsApproved = @IsApproved,
1240 LastLoginDate = @LastLoginDate
1241 WHERE
1242 @UserId = UserId
1243
1244 IF( @@ERROR <> 0 )
1245 GOTO Cleanup
1246
1247 IF( @TranStarted = 1 )
1248 BEGIN
1249 SET @TranStarted = 0
1250 COMMIT TRANSACTION
1251 END
1252
1253 RETURN 0
1254
1255Cleanup:
1256
1257 IF( @TranStarted = 1 )
1258 BEGIN
1259 SET @TranStarted = 0
1260 ROLLBACK TRANSACTION
1261 END
1262
1263 RETURN -1
1264END'
1265END
1266GO
1267/****** Object: StoredProcedure [dbo].[aspnet_Membership_UnlockUser] Script Date: 04/09/2012 20:01:31 ******/
1268SET ANSI_NULLS ON
1269GO
1270SET QUOTED_IDENTIFIER OFF
1271GO
1272IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_UnlockUser]') AND type in (N'P', N'PC'))
1273BEGIN
1274EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_UnlockUser]
1275 @ApplicationName nvarchar(256),
1276 @UserName nvarchar(256)
1277AS
1278BEGIN
1279 DECLARE @UserId uniqueidentifier
1280 SELECT @UserId = NULL
1281 SELECT @UserId = u.UserId
1282 FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1283 WHERE LoweredUserName = LOWER(@UserName) AND
1284 u.ApplicationId = a.ApplicationId AND
1285 LOWER(@ApplicationName) = a.LoweredApplicationName AND
1286 u.UserId = m.UserId
1287
1288 IF ( @UserId IS NULL )
1289 RETURN 1
1290
1291 UPDATE dbo.aspnet_Membership
1292 SET IsLockedOut = 0,
1293 FailedPasswordAttemptCount = 0,
1294 FailedPasswordAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 ),
1295 FailedPasswordAnswerAttemptCount = 0,
1296 FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 ),
1297 LastLockoutDate = CONVERT( datetime, ''17540101'', 112 )
1298 WHERE @UserId = UserId
1299
1300 RETURN 0
1301END'
1302END
1303GO
1304/****** Object: StoredProcedure [dbo].[aspnet_Membership_SetPassword] Script Date: 04/09/2012 20:01:31 ******/
1305SET ANSI_NULLS ON
1306GO
1307SET QUOTED_IDENTIFIER OFF
1308GO
1309IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_SetPassword]') AND type in (N'P', N'PC'))
1310BEGIN
1311EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_SetPassword]
1312 @ApplicationName nvarchar(256),
1313 @UserName nvarchar(256),
1314 @NewPassword nvarchar(128),
1315 @PasswordSalt nvarchar(128),
1316 @CurrentTimeUtc datetime,
1317 @PasswordFormat int = 0
1318AS
1319BEGIN
1320 DECLARE @UserId uniqueidentifier
1321 SELECT @UserId = NULL
1322 SELECT @UserId = u.UserId
1323 FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1324 WHERE LoweredUserName = LOWER(@UserName) AND
1325 u.ApplicationId = a.ApplicationId AND
1326 LOWER(@ApplicationName) = a.LoweredApplicationName AND
1327 u.UserId = m.UserId
1328
1329 IF (@UserId IS NULL)
1330 RETURN(1)
1331
1332 UPDATE dbo.aspnet_Membership
1333 SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
1334 LastPasswordChangedDate = @CurrentTimeUtc
1335 WHERE @UserId = UserId
1336 RETURN(0)
1337END'
1338END
1339GO
1340/****** Object: StoredProcedure [dbo].[aspnet_Membership_ResetPassword] Script Date: 04/09/2012 20:01:31 ******/
1341SET ANSI_NULLS ON
1342GO
1343SET QUOTED_IDENTIFIER OFF
1344GO
1345IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_ResetPassword]') AND type in (N'P', N'PC'))
1346BEGIN
1347EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_ResetPassword]
1348 @ApplicationName nvarchar(256),
1349 @UserName nvarchar(256),
1350 @NewPassword nvarchar(128),
1351 @MaxInvalidPasswordAttempts int,
1352 @PasswordAttemptWindow int,
1353 @PasswordSalt nvarchar(128),
1354 @CurrentTimeUtc datetime,
1355 @PasswordFormat int = 0,
1356 @PasswordAnswer nvarchar(128) = NULL
1357AS
1358BEGIN
1359 DECLARE @IsLockedOut bit
1360 DECLARE @LastLockoutDate datetime
1361 DECLARE @FailedPasswordAttemptCount int
1362 DECLARE @FailedPasswordAttemptWindowStart datetime
1363 DECLARE @FailedPasswordAnswerAttemptCount int
1364 DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1365
1366 DECLARE @UserId uniqueidentifier
1367 SET @UserId = NULL
1368
1369 DECLARE @ErrorCode int
1370 SET @ErrorCode = 0
1371
1372 DECLARE @TranStarted bit
1373 SET @TranStarted = 0
1374
1375 IF( @@TRANCOUNT = 0 )
1376 BEGIN
1377 BEGIN TRANSACTION
1378 SET @TranStarted = 1
1379 END
1380 ELSE
1381 SET @TranStarted = 0
1382
1383 SELECT @UserId = u.UserId
1384 FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1385 WHERE LoweredUserName = LOWER(@UserName) AND
1386 u.ApplicationId = a.ApplicationId AND
1387 LOWER(@ApplicationName) = a.LoweredApplicationName AND
1388 u.UserId = m.UserId
1389
1390 IF ( @UserId IS NULL )
1391 BEGIN
1392 SET @ErrorCode = 1
1393 GOTO Cleanup
1394 END
1395
1396 SELECT @IsLockedOut = IsLockedOut,
1397 @LastLockoutDate = LastLockoutDate,
1398 @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
1399 @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
1400 @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
1401 @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
1402 FROM dbo.aspnet_Membership WITH ( UPDLOCK )
1403 WHERE @UserId = UserId
1404
1405 IF( @IsLockedOut = 1 )
1406 BEGIN
1407 SET @ErrorCode = 99
1408 GOTO Cleanup
1409 END
1410
1411 UPDATE dbo.aspnet_Membership
1412 SET Password = @NewPassword,
1413 LastPasswordChangedDate = @CurrentTimeUtc,
1414 PasswordFormat = @PasswordFormat,
1415 PasswordSalt = @PasswordSalt
1416 WHERE @UserId = UserId AND
1417 ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
1418
1419 IF ( @@ROWCOUNT = 0 )
1420 BEGIN
1421 IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
1422 BEGIN
1423 SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1424 SET @FailedPasswordAnswerAttemptCount = 1
1425 END
1426 ELSE
1427 BEGIN
1428 SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1429 SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
1430 END
1431
1432 BEGIN
1433 IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
1434 BEGIN
1435 SET @IsLockedOut = 1
1436 SET @LastLockoutDate = @CurrentTimeUtc
1437 END
1438 END
1439
1440 SET @ErrorCode = 3
1441 END
1442 ELSE
1443 BEGIN
1444 IF( @FailedPasswordAnswerAttemptCount > 0 )
1445 BEGIN
1446 SET @FailedPasswordAnswerAttemptCount = 0
1447 SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
1448 END
1449 END
1450
1451 IF( NOT ( @PasswordAnswer IS NULL ) )
1452 BEGIN
1453 UPDATE dbo.aspnet_Membership
1454 SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1455 FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1456 FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1457 FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1458 FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1459 WHERE @UserId = UserId
1460
1461 IF( @@ERROR <> 0 )
1462 BEGIN
1463 SET @ErrorCode = -1
1464 GOTO Cleanup
1465 END
1466 END
1467
1468 IF( @TranStarted = 1 )
1469 BEGIN
1470 SET @TranStarted = 0
1471 COMMIT TRANSACTION
1472 END
1473
1474 RETURN @ErrorCode
1475
1476Cleanup:
1477
1478 IF( @TranStarted = 1 )
1479 BEGIN
1480 SET @TranStarted = 0
1481 ROLLBACK TRANSACTION
1482 END
1483
1484 RETURN @ErrorCode
1485
1486END'
1487END
1488GO
1489/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByUserId] Script Date: 04/09/2012 20:01:31 ******/
1490SET ANSI_NULLS ON
1491GO
1492SET QUOTED_IDENTIFIER OFF
1493GO
1494IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByUserId]') AND type in (N'P', N'PC'))
1495BEGIN
1496EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByUserId]
1497 @UserId uniqueidentifier,
1498 @CurrentTimeUtc datetime,
1499 @UpdateLastActivity bit = 0
1500AS
1501BEGIN
1502 IF ( @UpdateLastActivity = 1 )
1503 BEGIN
1504 UPDATE dbo.aspnet_Users
1505 SET LastActivityDate = @CurrentTimeUtc
1506 FROM dbo.aspnet_Users
1507 WHERE @UserId = UserId
1508
1509 IF ( @@ROWCOUNT = 0 ) -- User ID not found
1510 RETURN -1
1511 END
1512
1513 SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1514 m.CreateDate, m.LastLoginDate, u.LastActivityDate,
1515 m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
1516 m.LastLockoutDate
1517 FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
1518 WHERE @UserId = u.UserId AND u.UserId = m.UserId
1519
1520 IF ( @@ROWCOUNT = 0 ) -- User ID not found
1521 RETURN -1
1522
1523 RETURN 0
1524END'
1525END
1526GO
1527/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByName] Script Date: 04/09/2012 20:01:31 ******/
1528SET ANSI_NULLS ON
1529GO
1530SET QUOTED_IDENTIFIER OFF
1531GO
1532IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByName]') AND type in (N'P', N'PC'))
1533BEGIN
1534EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
1535 @ApplicationName nvarchar(256),
1536 @UserName nvarchar(256),
1537 @CurrentTimeUtc datetime,
1538 @UpdateLastActivity bit = 0
1539AS
1540BEGIN
1541 DECLARE @UserId uniqueidentifier
1542
1543 IF (@UpdateLastActivity = 1)
1544 BEGIN
1545 -- select user ID from aspnet_users table
1546 SELECT TOP 1 @UserId = u.UserId
1547 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1548 WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
1549 u.ApplicationId = a.ApplicationId AND
1550 LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
1551
1552 IF (@@ROWCOUNT = 0) -- Username not found
1553 RETURN -1
1554
1555 UPDATE dbo.aspnet_Users
1556 SET LastActivityDate = @CurrentTimeUtc
1557 WHERE @UserId = UserId
1558
1559 SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1560 m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
1561 u.UserId, m.IsLockedOut, m.LastLockoutDate
1562 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1563 WHERE @UserId = u.UserId AND u.UserId = m.UserId
1564 END
1565 ELSE
1566 BEGIN
1567 SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1568 m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
1569 u.UserId, m.IsLockedOut,m.LastLockoutDate
1570 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1571 WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
1572 u.ApplicationId = a.ApplicationId AND
1573 LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
1574
1575 IF (@@ROWCOUNT = 0) -- Username not found
1576 RETURN -1
1577 END
1578
1579 RETURN 0
1580END'
1581END
1582GO
1583/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetUserByEmail] Script Date: 04/09/2012 20:01:31 ******/
1584SET ANSI_NULLS ON
1585GO
1586SET QUOTED_IDENTIFIER OFF
1587GO
1588IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByEmail]') AND type in (N'P', N'PC'))
1589BEGIN
1590EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
1591 @ApplicationName nvarchar(256),
1592 @Email nvarchar(256)
1593AS
1594BEGIN
1595 IF( @Email IS NULL )
1596 SELECT u.UserName
1597 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1598 WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
1599 u.ApplicationId = a.ApplicationId AND
1600 u.UserId = m.UserId AND
1601 m.LoweredEmail IS NULL
1602 ELSE
1603 SELECT u.UserName
1604 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1605 WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
1606 u.ApplicationId = a.ApplicationId AND
1607 u.UserId = m.UserId AND
1608 LOWER(@Email) = m.LoweredEmail
1609
1610 IF (@@rowcount = 0)
1611 RETURN(1)
1612 RETURN(0)
1613END'
1614END
1615GO
1616/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPasswordWithFormat] Script Date: 04/09/2012 20:01:31 ******/
1617SET ANSI_NULLS ON
1618GO
1619SET QUOTED_IDENTIFIER OFF
1620GO
1621IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetPasswordWithFormat]') AND type in (N'P', N'PC'))
1622BEGIN
1623EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetPasswordWithFormat]
1624 @ApplicationName nvarchar(256),
1625 @UserName nvarchar(256),
1626 @UpdateLastLoginActivityDate bit,
1627 @CurrentTimeUtc datetime
1628AS
1629BEGIN
1630 DECLARE @IsLockedOut bit
1631 DECLARE @UserId uniqueidentifier
1632 DECLARE @Password nvarchar(128)
1633 DECLARE @PasswordSalt nvarchar(128)
1634 DECLARE @PasswordFormat int
1635 DECLARE @FailedPasswordAttemptCount int
1636 DECLARE @FailedPasswordAnswerAttemptCount int
1637 DECLARE @IsApproved bit
1638 DECLARE @LastActivityDate datetime
1639 DECLARE @LastLoginDate datetime
1640
1641 SELECT @UserId = NULL
1642
1643 SELECT @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
1644 @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
1645 @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
1646 @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
1647 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1648 WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
1649 u.ApplicationId = a.ApplicationId AND
1650 u.UserId = m.UserId AND
1651 LOWER(@UserName) = u.LoweredUserName
1652
1653 IF (@UserId IS NULL)
1654 RETURN 1
1655
1656 IF (@IsLockedOut = 1)
1657 RETURN 99
1658
1659 SELECT @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
1660 @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate
1661
1662 IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
1663 BEGIN
1664 UPDATE dbo.aspnet_Membership
1665 SET LastLoginDate = @CurrentTimeUtc
1666 WHERE UserId = @UserId
1667
1668 UPDATE dbo.aspnet_Users
1669 SET LastActivityDate = @CurrentTimeUtc
1670 WHERE @UserId = UserId
1671 END
1672
1673
1674 RETURN 0
1675END'
1676END
1677GO
1678/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetPassword] Script Date: 04/09/2012 20:01:31 ******/
1679SET ANSI_NULLS ON
1680GO
1681SET QUOTED_IDENTIFIER OFF
1682GO
1683IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetPassword]') AND type in (N'P', N'PC'))
1684BEGIN
1685EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetPassword]
1686 @ApplicationName nvarchar(256),
1687 @UserName nvarchar(256),
1688 @MaxInvalidPasswordAttempts int,
1689 @PasswordAttemptWindow int,
1690 @CurrentTimeUtc datetime,
1691 @PasswordAnswer nvarchar(128) = NULL
1692AS
1693BEGIN
1694 DECLARE @UserId uniqueidentifier
1695 DECLARE @PasswordFormat int
1696 DECLARE @Password nvarchar(128)
1697 DECLARE @passAns nvarchar(128)
1698 DECLARE @IsLockedOut bit
1699 DECLARE @LastLockoutDate datetime
1700 DECLARE @FailedPasswordAttemptCount int
1701 DECLARE @FailedPasswordAttemptWindowStart datetime
1702 DECLARE @FailedPasswordAnswerAttemptCount int
1703 DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1704
1705 DECLARE @ErrorCode int
1706 SET @ErrorCode = 0
1707
1708 DECLARE @TranStarted bit
1709 SET @TranStarted = 0
1710
1711 IF( @@TRANCOUNT = 0 )
1712 BEGIN
1713 BEGIN TRANSACTION
1714 SET @TranStarted = 1
1715 END
1716 ELSE
1717 SET @TranStarted = 0
1718
1719 SELECT @UserId = u.UserId,
1720 @Password = m.Password,
1721 @passAns = m.PasswordAnswer,
1722 @PasswordFormat = m.PasswordFormat,
1723 @IsLockedOut = m.IsLockedOut,
1724 @LastLockoutDate = m.LastLockoutDate,
1725 @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
1726 @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
1727 @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
1728 @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
1729 FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
1730 WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
1731 u.ApplicationId = a.ApplicationId AND
1732 u.UserId = m.UserId AND
1733 LOWER(@UserName) = u.LoweredUserName
1734
1735 IF ( @@rowcount = 0 )
1736 BEGIN
1737 SET @ErrorCode = 1
1738 GOTO Cleanup
1739 END
1740
1741 IF( @IsLockedOut = 1 )
1742 BEGIN
1743 SET @ErrorCode = 99
1744 GOTO Cleanup
1745 END
1746
1747 IF ( NOT( @PasswordAnswer IS NULL ) )
1748 BEGIN
1749 IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
1750 BEGIN
1751 IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
1752 BEGIN
1753 SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1754 SET @FailedPasswordAnswerAttemptCount = 1
1755 END
1756 ELSE
1757 BEGIN
1758 SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
1759 SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1760 END
1761
1762 BEGIN
1763 IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
1764 BEGIN
1765 SET @IsLockedOut = 1
1766 SET @LastLockoutDate = @CurrentTimeUtc
1767 END
1768 END
1769
1770 SET @ErrorCode = 3
1771 END
1772 ELSE
1773 BEGIN
1774 IF( @FailedPasswordAnswerAttemptCount > 0 )
1775 BEGIN
1776 SET @FailedPasswordAnswerAttemptCount = 0
1777 SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
1778 END
1779 END
1780
1781 UPDATE dbo.aspnet_Membership
1782 SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1783 FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1784 FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1785 FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1786 FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1787 WHERE @UserId = UserId
1788
1789 IF( @@ERROR <> 0 )
1790 BEGIN
1791 SET @ErrorCode = -1
1792 GOTO Cleanup
1793 END
1794 END
1795
1796 IF( @TranStarted = 1 )
1797 BEGIN
1798 SET @TranStarted = 0
1799 COMMIT TRANSACTION
1800 END
1801
1802 IF( @ErrorCode = 0 )
1803 SELECT @Password, @PasswordFormat
1804
1805 RETURN @ErrorCode
1806
1807Cleanup:
1808
1809 IF( @TranStarted = 1 )
1810 BEGIN
1811 SET @TranStarted = 0
1812 ROLLBACK TRANSACTION
1813 END
1814
1815 RETURN @ErrorCode
1816
1817END'
1818END
1819GO
1820/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline] Script Date: 04/09/2012 20:01:31 ******/
1821SET ANSI_NULLS ON
1822GO
1823SET QUOTED_IDENTIFIER OFF
1824GO
1825IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetNumberOfUsersOnline]') AND type in (N'P', N'PC'))
1826BEGIN
1827EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
1828 @ApplicationName nvarchar(256),
1829 @MinutesSinceLastInActive int,
1830 @CurrentTimeUtc datetime
1831AS
1832BEGIN
1833 DECLARE @DateActive datetime
1834 SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc)
1835
1836 DECLARE @NumOnline int
1837 SELECT @NumOnline = COUNT(*)
1838 FROM dbo.aspnet_Users u(NOLOCK),
1839 dbo.aspnet_Applications a(NOLOCK),
1840 dbo.aspnet_Membership m(NOLOCK)
1841 WHERE u.ApplicationId = a.ApplicationId AND
1842 LastActivityDate > @DateActive AND
1843 a.LoweredApplicationName = LOWER(@ApplicationName) AND
1844 u.UserId = m.UserId
1845 RETURN(@NumOnline)
1846END'
1847END
1848GO
1849/****** Object: StoredProcedure [dbo].[aspnet_Membership_GetAllUsers] Script Date: 04/09/2012 20:01:31 ******/
1850SET ANSI_NULLS ON
1851GO
1852SET QUOTED_IDENTIFIER OFF
1853GO
1854IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetAllUsers]') AND type in (N'P', N'PC'))
1855BEGIN
1856EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_GetAllUsers]
1857 @ApplicationName nvarchar(256),
1858 @PageIndex int,
1859 @PageSize int
1860AS
1861BEGIN
1862 DECLARE @ApplicationId uniqueidentifier
1863 SELECT @ApplicationId = NULL
1864 SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1865 IF (@ApplicationId IS NULL)
1866 RETURN 0
1867
1868
1869 -- Set the page bounds
1870 DECLARE @PageLowerBound int
1871 DECLARE @PageUpperBound int
1872 DECLARE @TotalRecords int
1873 SET @PageLowerBound = @PageSize * @PageIndex
1874 SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
1875
1876 -- Create a temp table TO store the select results
1877 CREATE TABLE #PageIndexForUsers
1878 (
1879 IndexId int IDENTITY (0, 1) NOT NULL,
1880 UserId uniqueidentifier
1881 )
1882
1883 -- Insert into our temp table
1884 INSERT INTO #PageIndexForUsers (UserId)
1885 SELECT u.UserId
1886 FROM dbo.aspnet_Membership m, dbo.aspnet_Users u
1887 WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
1888 ORDER BY u.UserName
1889
1890 SELECT @TotalRecords = @@ROWCOUNT
1891
1892 SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1893 m.CreateDate,
1894 m.LastLoginDate,
1895 u.LastActivityDate,
1896 m.LastPasswordChangedDate,
1897 u.UserId, m.IsLockedOut,
1898 m.LastLockoutDate
1899 FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
1900 WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
1901 p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
1902 ORDER BY u.UserName
1903 RETURN @TotalRecords
1904END'
1905END
1906GO
1907/****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByName] Script Date: 04/09/2012 20:01:31 ******/
1908SET ANSI_NULLS ON
1909GO
1910SET QUOTED_IDENTIFIER OFF
1911GO
1912IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_FindUsersByName]') AND type in (N'P', N'PC'))
1913BEGIN
1914EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
1915 @ApplicationName nvarchar(256),
1916 @UserNameToMatch nvarchar(256),
1917 @PageIndex int,
1918 @PageSize int
1919AS
1920BEGIN
1921 DECLARE @ApplicationId uniqueidentifier
1922 SELECT @ApplicationId = NULL
1923 SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1924 IF (@ApplicationId IS NULL)
1925 RETURN 0
1926
1927 -- Set the page bounds
1928 DECLARE @PageLowerBound int
1929 DECLARE @PageUpperBound int
1930 DECLARE @TotalRecords int
1931 SET @PageLowerBound = @PageSize * @PageIndex
1932 SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
1933
1934 -- Create a temp table TO store the select results
1935 CREATE TABLE #PageIndexForUsers
1936 (
1937 IndexId int IDENTITY (0, 1) NOT NULL,
1938 UserId uniqueidentifier
1939 )
1940
1941 -- Insert into our temp table
1942 INSERT INTO #PageIndexForUsers (UserId)
1943 SELECT u.UserId
1944 FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
1945 WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
1946 ORDER BY u.UserName
1947
1948
1949 SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1950 m.CreateDate,
1951 m.LastLoginDate,
1952 u.LastActivityDate,
1953 m.LastPasswordChangedDate,
1954 u.UserId, m.IsLockedOut,
1955 m.LastLockoutDate
1956 FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
1957 WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
1958 p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
1959 ORDER BY u.UserName
1960
1961 SELECT @TotalRecords = COUNT(*)
1962 FROM #PageIndexForUsers
1963 RETURN @TotalRecords
1964END'
1965END
1966GO
1967/****** Object: StoredProcedure [dbo].[aspnet_Membership_FindUsersByEmail] Script Date: 04/09/2012 20:01:31 ******/
1968SET ANSI_NULLS ON
1969GO
1970SET QUOTED_IDENTIFIER OFF
1971GO
1972IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_FindUsersByEmail]') AND type in (N'P', N'PC'))
1973BEGIN
1974EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByEmail]
1975 @ApplicationName nvarchar(256),
1976 @EmailToMatch nvarchar(256),
1977 @PageIndex int,
1978 @PageSize int
1979AS
1980BEGIN
1981 DECLARE @ApplicationId uniqueidentifier
1982 SELECT @ApplicationId = NULL
1983 SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1984 IF (@ApplicationId IS NULL)
1985 RETURN 0
1986
1987 -- Set the page bounds
1988 DECLARE @PageLowerBound int
1989 DECLARE @PageUpperBound int
1990 DECLARE @TotalRecords int
1991 SET @PageLowerBound = @PageSize * @PageIndex
1992 SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
1993
1994 -- Create a temp table TO store the select results
1995 CREATE TABLE #PageIndexForUsers
1996 (
1997 IndexId int IDENTITY (0, 1) NOT NULL,
1998 UserId uniqueidentifier
1999 )
2000
2001 -- Insert into our temp table
2002 IF( @EmailToMatch IS NULL )
2003 INSERT INTO #PageIndexForUsers (UserId)
2004 SELECT u.UserId
2005 FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
2006 WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
2007 ORDER BY m.LoweredEmail
2008 ELSE
2009 INSERT INTO #PageIndexForUsers (UserId)
2010 SELECT u.UserId
2011 FROM dbo.aspnet_Users u, dbo.aspnet_Membership m
2012 WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
2013 ORDER BY m.LoweredEmail
2014
2015 SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
2016 m.CreateDate,
2017 m.LastLoginDate,
2018 u.LastActivityDate,
2019 m.LastPasswordChangedDate,
2020 u.UserId, m.IsLockedOut,
2021 m.LastLockoutDate
2022 FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
2023 WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
2024 p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
2025 ORDER BY m.LoweredEmail
2026
2027 SELECT @TotalRecords = COUNT(*)
2028 FROM #PageIndexForUsers
2029 RETURN @TotalRecords
2030END'
2031END
2032GO
2033/****** Object: StoredProcedure [dbo].[aspnet_Membership_CreateUser] Script Date: 04/09/2012 20:01:31 ******/
2034SET ANSI_NULLS ON
2035GO
2036SET QUOTED_IDENTIFIER OFF
2037GO
2038IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_CreateUser]') AND type in (N'P', N'PC'))
2039BEGIN
2040EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_CreateUser]
2041 @ApplicationName nvarchar(256),
2042 @UserName nvarchar(256),
2043 @Password nvarchar(128),
2044 @PasswordSalt nvarchar(128),
2045 @Email nvarchar(256),
2046 @PasswordQuestion nvarchar(256),
2047 @PasswordAnswer nvarchar(128),
2048 @IsApproved bit,
2049 @CurrentTimeUtc datetime,
2050 @CreateDate datetime = NULL,
2051 @UniqueEmail int = 0,
2052 @PasswordFormat int = 0,
2053 @UserId uniqueidentifier OUTPUT
2054AS
2055BEGIN
2056 DECLARE @ApplicationId uniqueidentifier
2057 SELECT @ApplicationId = NULL
2058
2059 DECLARE @NewUserId uniqueidentifier
2060 SELECT @NewUserId = NULL
2061
2062 DECLARE @IsLockedOut bit
2063 SET @IsLockedOut = 0
2064
2065 DECLARE @LastLockoutDate datetime
2066 SET @LastLockoutDate = CONVERT( datetime, ''17540101'', 112 )
2067
2068 DECLARE @FailedPasswordAttemptCount int
2069 SET @FailedPasswordAttemptCount = 0
2070
2071 DECLARE @FailedPasswordAttemptWindowStart datetime
2072 SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
2073
2074 DECLARE @FailedPasswordAnswerAttemptCount int
2075 SET @FailedPasswordAnswerAttemptCount = 0
2076
2077 DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
2078 SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, ''17540101'', 112 )
2079
2080 DECLARE @NewUserCreated bit
2081 DECLARE @ReturnValue int
2082 SET @ReturnValue = 0
2083
2084 DECLARE @ErrorCode int
2085 SET @ErrorCode = 0
2086
2087 DECLARE @TranStarted bit
2088 SET @TranStarted = 0
2089
2090 IF( @@TRANCOUNT = 0 )
2091 BEGIN
2092 BEGIN TRANSACTION
2093 SET @TranStarted = 1
2094 END
2095 ELSE
2096 SET @TranStarted = 0
2097
2098 EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
2099
2100 IF( @@ERROR <> 0 )
2101 BEGIN
2102 SET @ErrorCode = -1
2103 GOTO Cleanup
2104 END
2105
2106 SET @CreateDate = @CurrentTimeUtc
2107
2108 SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
2109 IF ( @NewUserId IS NULL )
2110 BEGIN
2111 SET @NewUserId = @UserId
2112 EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
2113 SET @NewUserCreated = 1
2114 END
2115 ELSE
2116 BEGIN
2117 SET @NewUserCreated = 0
2118 IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
2119 BEGIN
2120 SET @ErrorCode = 6
2121 GOTO Cleanup
2122 END
2123 END
2124
2125 IF( @@ERROR <> 0 )
2126 BEGIN
2127 SET @ErrorCode = -1
2128 GOTO Cleanup
2129 END
2130
2131 IF( @ReturnValue = -1 )
2132 BEGIN
2133 SET @ErrorCode = 10
2134 GOTO Cleanup
2135 END
2136
2137 IF ( EXISTS ( SELECT UserId
2138 FROM dbo.aspnet_Membership
2139 WHERE @NewUserId = UserId ) )
2140 BEGIN
2141 SET @ErrorCode = 6
2142 GOTO Cleanup
2143 END
2144
2145 SET @UserId = @NewUserId
2146
2147 IF (@UniqueEmail = 1)
2148 BEGIN
2149 IF (EXISTS (SELECT *
2150 FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
2151 WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
2152 BEGIN
2153 SET @ErrorCode = 7
2154 GOTO Cleanup
2155 END
2156 END
2157
2158 IF (@NewUserCreated = 0)
2159 BEGIN
2160 UPDATE dbo.aspnet_Users
2161 SET LastActivityDate = @CreateDate
2162 WHERE @UserId = UserId
2163 IF( @@ERROR <> 0 )
2164 BEGIN
2165 SET @ErrorCode = -1
2166 GOTO Cleanup
2167 END
2168 END
2169
2170 INSERT INTO dbo.aspnet_Membership
2171 ( ApplicationId,
2172 UserId,
2173 Password,
2174 PasswordSalt,
2175 Email,
2176 LoweredEmail,
2177 PasswordQuestion,
2178 PasswordAnswer,
2179 PasswordFormat,
2180 IsApproved,
2181 IsLockedOut,
2182 CreateDate,
2183 LastLoginDate,
2184 LastPasswordChangedDate,
2185 LastLockoutDate,
2186 FailedPasswordAttemptCount,
2187 FailedPasswordAttemptWindowStart,
2188 FailedPasswordAnswerAttemptCount,
2189 FailedPasswordAnswerAttemptWindowStart )
2190 VALUES ( @ApplicationId,
2191 @UserId,
2192 @Password,
2193 @PasswordSalt,
2194 @Email,
2195 LOWER(@Email),
2196 @PasswordQuestion,
2197 @PasswordAnswer,
2198 @PasswordFormat,
2199 @IsApproved,
2200 @IsLockedOut,
2201 @CreateDate,
2202 @CreateDate,
2203 @CreateDate,
2204 @LastLockoutDate,
2205 @FailedPasswordAttemptCount,
2206 @FailedPasswordAttemptWindowStart,
2207 @FailedPasswordAnswerAttemptCount,
2208 @FailedPasswordAnswerAttemptWindowStart )
2209
2210 IF( @@ERROR <> 0 )
2211 BEGIN
2212 SET @ErrorCode = -1
2213 GOTO Cleanup
2214 END
2215
2216 IF( @TranStarted = 1 )
2217 BEGIN
2218 SET @TranStarted = 0
2219 COMMIT TRANSACTION
2220 END
2221
2222 RETURN 0
2223
2224Cleanup:
2225
2226 IF( @TranStarted = 1 )
2227 BEGIN
2228 SET @TranStarted = 0
2229 ROLLBACK TRANSACTION
2230 END
2231
2232 RETURN @ErrorCode
2233
2234END'
2235END
2236GO
2237/****** Object: StoredProcedure [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer] Script Date: 04/09/2012 20:01:31 ******/
2238SET ANSI_NULLS ON
2239GO
2240SET QUOTED_IDENTIFIER OFF
2241GO
2242IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]') AND type in (N'P', N'PC'))
2243BEGIN
2244EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]
2245 @ApplicationName nvarchar(256),
2246 @UserName nvarchar(256),
2247 @NewPasswordQuestion nvarchar(256),
2248 @NewPasswordAnswer nvarchar(128)
2249AS
2250BEGIN
2251 DECLARE @UserId uniqueidentifier
2252 SELECT @UserId = NULL
2253 SELECT @UserId = u.UserId
2254 FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
2255 WHERE LoweredUserName = LOWER(@UserName) AND
2256 u.ApplicationId = a.ApplicationId AND
2257 LOWER(@ApplicationName) = a.LoweredApplicationName AND
2258 u.UserId = m.UserId
2259 IF (@UserId IS NULL)
2260 BEGIN
2261 RETURN(1)
2262 END
2263
2264 UPDATE dbo.aspnet_Membership
2265 SET PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
2266 WHERE UserId=@UserId
2267 RETURN(0)
2268END'
2269END
2270GO
2271/****** Object: StoredProcedure [dbo].[aspnet_AnyDataInTables] Script Date: 04/09/2012 20:01:31 ******/
2272SET ANSI_NULLS ON
2273GO
2274SET QUOTED_IDENTIFIER OFF
2275GO
2276IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_AnyDataInTables]') AND type in (N'P', N'PC'))
2277BEGIN
2278EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables]
2279 @TablesToCheck int
2280AS
2281BEGIN
2282 -- Check Membership table if (@TablesToCheck & 1) is set
2283 IF ((@TablesToCheck & 1) <> 0 AND
2284 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
2285 BEGIN
2286 IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
2287 BEGIN
2288 SELECT N''aspnet_Membership''
2289 RETURN
2290 END
2291 END
2292
2293 -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
2294 IF ((@TablesToCheck & 2) <> 0 AND
2295 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Roles'') AND (type = ''V''))) )
2296 BEGIN
2297 IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
2298 BEGIN
2299 SELECT N''aspnet_Roles''
2300 RETURN
2301 END
2302 END
2303
2304 -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
2305 IF ((@TablesToCheck & 4) <> 0 AND
2306 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
2307 BEGIN
2308 IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
2309 BEGIN
2310 SELECT N''aspnet_Profile''
2311 RETURN
2312 END
2313 END
2314
2315 -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
2316 IF ((@TablesToCheck & 8) <> 0 AND
2317 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
2318 BEGIN
2319 IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
2320 BEGIN
2321 SELECT N''aspnet_PersonalizationPerUser''
2322 RETURN
2323 END
2324 END
2325
2326 -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
2327 IF ((@TablesToCheck & 16) <> 0 AND
2328 (EXISTS (SELECT name FROM sysobjects WHERE (name = N''aspnet_WebEvent_LogEvent'') AND (type = ''P''))) )
2329 BEGIN
2330 IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
2331 BEGIN
2332 SELECT N''aspnet_WebEvent_Events''
2333 RETURN
2334 END
2335 END
2336
2337 -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
2338 IF ((@TablesToCheck & 1) <> 0 AND
2339 (@TablesToCheck & 2) <> 0 AND
2340 (@TablesToCheck & 4) <> 0 AND
2341 (@TablesToCheck & 8) <> 0 AND
2342 (@TablesToCheck & 32) <> 0 AND
2343 (@TablesToCheck & 128) <> 0 AND
2344 (@TablesToCheck & 256) <> 0 AND
2345 (@TablesToCheck & 512) <> 0 AND
2346 (@TablesToCheck & 1024) <> 0)
2347 BEGIN
2348 IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
2349 BEGIN
2350 SELECT N''aspnet_Users''
2351 RETURN
2352 END
2353 IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
2354 BEGIN
2355 SELECT N''aspnet_Applications''
2356 RETURN
2357 END
2358 END
2359END'
2360END
2361GO