3UZwgkZs

1/**********************************************************************/
2/* InstallCommon.SQL                                                  */
3/*                                                                    */
4/* Installs the tables, triggers and stored procedures necessary for  */
5/* supporting some features of ASP.Net                                */
6/*
7** Copyright Microsoft, Inc. 2003
8** All Rights Reserved.
9*/
10/**********************************************************************/
11
12PRINT '---------------------------------------'
13PRINT 'Starting execution of InstallCommon.SQL'
14PRINT '---------------------------------------'
15GO
16
17SET QUOTED_IDENTIFIER OFF
18SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
19GO
20SET ANSI_PADDING ON
21GO
22SET ANSI_NULL_DFLT_ON ON
23GO
24
25DECLARE @dbname nvarchar(128)
26DECLARE @dboptions nvarchar(1024)
27
28SET @dboptions = N'/**/'
29SET @dbname = N'aspnetdb'
30
31IF (NOT EXISTS (SELECT name
32                FROM master.dbo.sysdatabases
33                WHERE name = @dbname))
34BEGIN
35  PRINT 'Creating the ' + @dbname + ' database...'
36  DECLARE @cmd nvarchar(500)
37  SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions
38  EXEC(@cmd)
39END
40GO
41
42USE [aspnetdb]
43GO
44
45/*************************************************************/
46/*************************************************************/
47/*************************************************************/
48-- Create the temporary permission tables and stored procedures
49-- TO preserve the permissions of an object.
50--
51-- We use this method instead of using CREATE (if the object
52-- doesn't exist) and ALTER (if the object exists) because the
53-- latter one either requires the use of dynamic SQL (which we want to
54-- avoid) or writing the body of the object (e.g. an SP or view) twice,
55-- once use CREATE and again using ALTER.
56
57
58IF (OBJECT_ID('tempdb.#aspnet_Permissions') IS NOT NULL)
59BEGIN
60    DROP TABLE #aspnet_Permissions
61END
62GO
63
64CREATE TABLE #aspnet_Permissions
65(
66    Owner     sysname,
67    Object    sysname,
68    Grantee   sysname,
69    Grantor   sysname,
70    ProtectType char(10),
71    [Action]    varchar(60),
72    [Column]    sysname
73)
74
75INSERT INTO #aspnet_Permissions
76EXEC sp_helprotect
77
78IF (EXISTS (SELECT name
79              FROM sysobjects
80             WHERE (name = N'aspnet_Setup_RestorePermissions')
81               AND (type = 'P')))
82DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions
83GO
84
85CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions
86    @name   sysname
87AS
88BEGIN
89    DECLARE @object sysname
90    DECLARE @protectType char(10)
91    DECLARE @action varchar(60)
92    DECLARE @grantee sysname
93    DECLARE @cmd nvarchar(500)
94    DECLARE c1 cursor FORWARD_ONLY FOR
95        SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
96
97    OPEN c1
98
99    FETCH c1 INTO @object, @protectType, @action, @grantee
100    WHILE (@@fetch_status = 0)
101    BEGIN
102        SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
103        EXEC (@cmd)
104        FETCH c1 INTO @object, @protectType, @action, @grantee
105    END
106
107    CLOSE c1
108    DEALLOCATE c1
109END
110GO
111
112/*************************************************************/
113/*************************************************************/
114/*************************************************************/
115
116IF (EXISTS (SELECT name
117              FROM sysobjects
118             WHERE (name = N'aspnet_Setup_RemoveAllRoleMembers')
119               AND (type = 'P')))
120DROP PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
121GO
122
123CREATE PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
124    @name   sysname
125AS
126BEGIN
127    CREATE TABLE #aspnet_RoleMembers
128    (
129        Group_name      sysname,
130        Group_id        smallint,
131        Users_in_group  sysname,
132        User_id         smallint
133    )
134
135    INSERT INTO #aspnet_RoleMembers
136    EXEC sp_helpuser @name
137
138    DECLARE @user_id smallint
139    DECLARE @cmd nvarchar(500)
140    DECLARE c1 cursor FORWARD_ONLY FOR
141        SELECT User_id FROM #aspnet_RoleMembers
142
143    OPEN c1
144
145    FETCH c1 INTO @user_id
146    WHILE (@@fetch_status = 0)
147    BEGIN
148        SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
149        EXEC (@cmd)
150        FETCH c1 INTO @user_id
151    END
152
153    CLOSE c1
154    DEALLOCATE c1
155END
156GO
157
158/*************************************************************/
159/*************************************************************/
160/*************************************************************/
161-- Create the aspnet_Applications table.
162
163IF (NOT EXISTS (SELECT name
164                FROM sysobjects
165                WHERE (name = N'aspnet_Applications')
166                  AND (type = 'U')))
167BEGIN
168  PRINT 'Creating the aspnet_Applications table...'
169  CREATE TABLE [dbo].aspnet_Applications (
170    ApplicationName         nvarchar(256)               NOT NULL UNIQUE,
171    LoweredApplicationName  nvarchar(256)               NOT NULL UNIQUE,
172    ApplicationId           uniqueidentifier            PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
173    Description             nvarchar(256)       )
174  CREATE CLUSTERED INDEX aspnet_Applications_Index ON [dbo].aspnet_Applications(LoweredApplicationName)
175END
176GO
177
178/*************************************************************/
179/*************************************************************/
180/*************************************************************/
181-- Create the aspnet_Users table
182IF (NOT EXISTS (SELECT name
183                FROM sysobjects
184                WHERE (name = N'aspnet_Users')
185                  AND (type = 'U')))
186BEGIN
187  PRINT 'Creating the aspnet_Users table...'
188  CREATE TABLE [dbo].aspnet_Users (
189    ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES [dbo].aspnet_Applications(ApplicationId),
190    UserId           uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
191    UserName         nvarchar(256)       NOT NULL,
192    LoweredUserName  nvarchar(256)	     NOT NULL,
193    MobileAlias      nvarchar(16)        DEFAULT NULL,
194    IsAnonymous      bit                 NOT NULL DEFAULT 0,
195    LastActivityDate DATETIME            NOT NULL)
196
197   CREATE UNIQUE CLUSTERED INDEX aspnet_Users_Index ON [dbo].aspnet_Users(ApplicationId, LoweredUserName)
198   CREATE NONCLUSTERED INDEX aspnet_Users_Index2 ON [dbo].aspnet_Users(ApplicationId, LastActivityDate)
199END
200GO
201
202/*************************************************************/
203/*************************************************************/
204/*************************************************************/
205-- Create the aspnet_SchemaVersions table
206IF (NOT EXISTS (SELECT name
207                FROM sysobjects
208                WHERE (name = N'aspnet_SchemaVersions')
209                  AND (type = 'U')))
210BEGIN
211  PRINT 'Creating the aspnet_SchemaVersions table...'
212  CREATE TABLE [dbo].aspnet_SchemaVersions (
213    Feature                  nvarchar(128)  NOT NULL PRIMARY KEY CLUSTERED( Feature, CompatibleSchemaVersion ),
214    CompatibleSchemaVersion  nvarchar(128)	NOT NULL,
215    IsCurrentVersion         bit            NOT NULL )
216END
217GO
218
219/*************************************************************/
220/*************************************************************/
221------------- Create Stored Procedures
222/*************************************************************/
223/*************************************************************/
224-- RegisterSchemaVersion SP
225
226IF (EXISTS (SELECT name
227              FROM sysobjects
228             WHERE (name = N'aspnet_RegisterSchemaVersion')
229               AND (type = 'P')))
230DROP PROCEDURE [dbo].aspnet_RegisterSchemaVersion
231GO
232
233CREATE PROCEDURE [dbo].aspnet_RegisterSchemaVersion
234    @Feature                   nvarchar(128),
235    @CompatibleSchemaVersion   nvarchar(128),
236    @IsCurrentVersion          bit,
237    @RemoveIncompatibleSchema  bit
238AS
239BEGIN
240    IF( @RemoveIncompatibleSchema = 1 )
241    BEGIN
242        DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
243    END
244    ELSE
245    BEGIN
246        IF( @IsCurrentVersion = 1 )
247        BEGIN
248            UPDATE dbo.aspnet_SchemaVersions
249            SET IsCurrentVersion = 0
250            WHERE Feature = LOWER( @Feature )
251        END
252    END
253
254    INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
255    VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
256END
257GO
258
259DECLARE @command nvarchar(4000)
260
261SET @command = 'GRANT EXECUTE ON [dbo].aspnet_Setup_RestorePermissions TO ' + QUOTENAME(user)
262EXEC (@command)
263SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
264EXEC (@command)
265GO
266
267-- Restore the permissions
268EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_RegisterSchemaVersion'
269GO
270
271-- Create common schema version
272EXEC [dbo].aspnet_RegisterSchemaVersion N'Common', N'1', 1, 1
273GO
274
275/*************************************************************/
276/*************************************************************/
277-- CheckSchemaVersion SP
278
279IF (EXISTS (SELECT name
280              FROM sysobjects
281             WHERE (name = N'aspnet_CheckSchemaVersion')
282               AND (type = 'P')))
283DROP PROCEDURE [dbo].aspnet_CheckSchemaVersion
284GO
285
286CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion
287    @Feature                   nvarchar(128),
288    @CompatibleSchemaVersion   nvarchar(128)
289AS
290BEGIN
291    IF (EXISTS( SELECT  *
292                FROM    dbo.aspnet_SchemaVersions
293                WHERE   Feature = LOWER( @Feature ) AND
294                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
295        RETURN 0
296
297    RETURN 1
298END
299GO
300
301-- Restore the permissions
302EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_CheckSchemaVersion'
303GO
304
305/*************************************************************/
306/*************************************************************/
307-- CreateApplication SP
308
309IF (EXISTS (SELECT name
310              FROM sysobjects
311             WHERE (name = N'aspnet_Applications_CreateApplication')
312               AND (type = 'P')))
313DROP PROCEDURE [dbo].aspnet_Applications_CreateApplication
314GO
315
316CREATE PROCEDURE [dbo].aspnet_Applications_CreateApplication
317    @ApplicationName      nvarchar(256),
318    @ApplicationId        uniqueidentifier OUTPUT
319AS
320BEGIN
321    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
322
323    IF(@ApplicationId IS NULL)
324    BEGIN
325        DECLARE @TranStarted   bit
326        SET @TranStarted = 0
327
328        IF( @@TRANCOUNT = 0 )
329        BEGIN
330	        BEGIN TRANSACTION
331	        SET @TranStarted = 1
332        END
333        ELSE
334    	    SET @TranStarted = 0
335
336        SELECT  @ApplicationId = ApplicationId
337        FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
338        WHERE LOWER(@ApplicationName) = LoweredApplicationName
339
340        IF(@ApplicationId IS NULL)
341        BEGIN
342            SELECT  @ApplicationId = NEWID()
343            INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
344            VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
345        END
346
347
348        IF( @TranStarted = 1 )
349        BEGIN
350            IF(@@ERROR = 0)
351            BEGIN
352	        SET @TranStarted = 0
353	        COMMIT TRANSACTION
354            END
355            ELSE
356            BEGIN
357                SET @TranStarted = 0
358                ROLLBACK TRANSACTION
359            END
360        END
361    END
362END
363GO
364
365-- Restore the permissions
366EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Applications_CreateApplication'
367GO
368
369/*************************************************************/
370/*************************************************************/
371-- UnRegisterSchemaVersion SP
372
373IF (EXISTS (SELECT name
374              FROM sysobjects
375             WHERE (name = N'aspnet_UnRegisterSchemaVersion')
376               AND (type = 'P')))
377DROP PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
378GO
379
380CREATE PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
381    @Feature                   nvarchar(128),
382    @CompatibleSchemaVersion   nvarchar(128)
383AS
384BEGIN
385    DELETE FROM dbo.aspnet_SchemaVersions
386        WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
387END
388GO
389
390-- Restore the permissions
391EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_UnRegisterSchemaVersion'
392GO
393
394/*************************************************************/
395/*************************************************************/
396-- CreateUser SP
397
398IF (EXISTS (SELECT name
399              FROM sysobjects
400             WHERE (name = N'aspnet_Users_CreateUser')
401               AND (type = 'P')))
402DROP PROCEDURE [dbo].aspnet_Users_CreateUser
403GO
404
405CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
406    @ApplicationId    uniqueidentifier,
407    @UserName         nvarchar(256),
408    @IsUserAnonymous  bit,
409    @LastActivityDate DATETIME,
410    @UserId           uniqueidentifier OUTPUT
411AS
412BEGIN
413    IF( @UserId IS NULL )
414        SELECT @UserId = NEWID()
415    ELSE
416    BEGIN
417        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
418                    WHERE @UserId = UserId ) )
419            RETURN -1
420    END
421
422    INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
423    VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
424
425    RETURN 0
426END
427GO
428
429-- Restore the permissions
430EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_CreateUser'
431GO
432
433/*************************************************************/
434/*************************************************************/
435--- DeleteUser SP
436
437IF (EXISTS (SELECT name
438              FROM sysobjects
439             WHERE (name = N'aspnet_Users_DeleteUser')
440               AND (type = 'P')))
441DROP PROCEDURE [dbo].aspnet_Users_DeleteUser
442GO
443CREATE PROCEDURE [dbo].aspnet_Users_DeleteUser
444    @ApplicationName  nvarchar(256),
445    @UserName         nvarchar(256),
446    @TablesToDeleteFrom int,
447    @NumTablesDeletedFrom int OUTPUT
448AS
449BEGIN
450    DECLARE @UserId               uniqueidentifier
451    SELECT  @UserId               = NULL
452    SELECT  @NumTablesDeletedFrom = 0
453
454    DECLARE @TranStarted   bit
455    SET @TranStarted = 0
456
457    IF( @@TRANCOUNT = 0 )
458    BEGIN
459	    BEGIN TRANSACTION
460	    SET @TranStarted = 1
461    END
462    ELSE
463	SET @TranStarted = 0
464
465    DECLARE @ErrorCode   int
466    DECLARE @RowCount    int
467
468    SET @ErrorCode = 0
469    SET @RowCount  = 0
470
471    SELECT  @UserId = u.UserId
472    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a
473    WHERE   u.LoweredUserName       = LOWER(@UserName)
474        AND u.ApplicationId         = a.ApplicationId
475        AND LOWER(@ApplicationName) = a.LoweredApplicationName
476
477    IF (@UserId IS NULL)
478    BEGIN
479        GOTO Cleanup
480    END
481
482    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
483    IF ((@TablesToDeleteFrom & 1) <> 0 AND
484        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
485    BEGIN
486        DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
487
488        SELECT @ErrorCode = @@ERROR,
489               @RowCount = @@ROWCOUNT
490
491        IF( @ErrorCode <> 0 )
492            GOTO Cleanup
493
494        IF (@RowCount <> 0)
495            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
496    END
497
498    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
499    IF ((@TablesToDeleteFrom & 2) <> 0  AND
500        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
501    BEGIN
502        DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
503
504        SELECT @ErrorCode = @@ERROR,
505                @RowCount = @@ROWCOUNT
506
507        IF( @ErrorCode <> 0 )
508            GOTO Cleanup
509
510        IF (@RowCount <> 0)
511            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
512    END
513
514    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
515    IF ((@TablesToDeleteFrom & 4) <> 0  AND
516        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
517    BEGIN
518        DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
519
520        SELECT @ErrorCode = @@ERROR,
521                @RowCount = @@ROWCOUNT
522
523        IF( @ErrorCode <> 0 )
524            GOTO Cleanup
525
526        IF (@RowCount <> 0)
527            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
528    END
529
530    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
531    IF ((@TablesToDeleteFrom & 8) <> 0  AND
532        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
533    BEGIN
534        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
535
536        SELECT @ErrorCode = @@ERROR,
537                @RowCount = @@ROWCOUNT
538
539        IF( @ErrorCode <> 0 )
540            GOTO Cleanup
541
542        IF (@RowCount <> 0)
543            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
544    END
545
546    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
547    IF ((@TablesToDeleteFrom & 1) <> 0 AND
548        (@TablesToDeleteFrom & 2) <> 0 AND
549        (@TablesToDeleteFrom & 4) <> 0 AND
550        (@TablesToDeleteFrom & 8) <> 0 AND
551        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
552    BEGIN
553        DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
554
555        SELECT @ErrorCode = @@ERROR,
556                @RowCount = @@ROWCOUNT
557
558        IF( @ErrorCode <> 0 )
559            GOTO Cleanup
560
561        IF (@RowCount <> 0)
562            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
563    END
564
565    IF( @TranStarted = 1 )
566    BEGIN
567	    SET @TranStarted = 0
568	    COMMIT TRANSACTION
569    END
570
571    RETURN 0
572
573Cleanup:
574    SET @NumTablesDeletedFrom = 0
575
576    IF( @TranStarted = 1 )
577    BEGIN
578        SET @TranStarted = 0
579	    ROLLBACK TRANSACTION
580    END
581
582    RETURN @ErrorCode
583
584END
585GO
586
587/*************************************************************/
588/*************************************************************/
589/*************************************************************/
590/*************************************************************/
591
592-- Restore the permissions
593EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_DeleteUser'
594GO
595
596/*************************************************************/
597/*************************************************************/
598--- aspnet_AnyDataInTables SP
599
600IF (EXISTS (SELECT name
601              FROM sysobjects
602             WHERE (name = N'aspnet_AnyDataInTables')
603               AND (type = 'P')))
604DROP PROCEDURE [dbo].aspnet_AnyDataInTables
605GO
606CREATE PROCEDURE [dbo].aspnet_AnyDataInTables
607    @TablesToCheck int
608AS
609BEGIN
610    -- Check Membership table if (@TablesToCheck & 1) is set
611    IF ((@TablesToCheck & 1) <> 0 AND
612        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
613    BEGIN
614        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
615        BEGIN
616            SELECT N'aspnet_Membership'
617            RETURN
618        END
619    END
620
621    -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
622    IF ((@TablesToCheck & 2) <> 0  AND
623        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
624    BEGIN
625        IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
626        BEGIN
627            SELECT N'aspnet_Roles'
628            RETURN
629        END
630    END
631
632    -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
633    IF ((@TablesToCheck & 4) <> 0  AND
634        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
635    BEGIN
636        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
637        BEGIN
638            SELECT N'aspnet_Profile'
639            RETURN
640        END
641    END
642
643    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
644    IF ((@TablesToCheck & 8) <> 0  AND
645        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
646    BEGIN
647        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
648        BEGIN
649            SELECT N'aspnet_PersonalizationPerUser'
650            RETURN
651        END
652    END
653
654    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
655    IF ((@TablesToCheck & 16) <> 0  AND
656        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
657    BEGIN
658        IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
659        BEGIN
660            SELECT N'aspnet_WebEvent_Events'
661            RETURN
662        END
663    END
664
665    -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
666    IF ((@TablesToCheck & 1) <> 0 AND
667        (@TablesToCheck & 2) <> 0 AND
668        (@TablesToCheck & 4) <> 0 AND
669        (@TablesToCheck & 8) <> 0 AND
670        (@TablesToCheck & 32) <> 0 AND
671        (@TablesToCheck & 128) <> 0 AND
672        (@TablesToCheck & 256) <> 0 AND
673        (@TablesToCheck & 512) <> 0 AND
674        (@TablesToCheck & 1024) <> 0)
675    BEGIN
676        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
677        BEGIN
678            SELECT N'aspnet_Users'
679            RETURN
680        END
681        IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
682        BEGIN
683            SELECT N'aspnet_Applications'
684            RETURN
685        END
686    END
687END
688GO
689
690/*************************************************************/
691/*************************************************************/
692/*************************************************************/
693/*************************************************************/
694DECLARE @command nvarchar(400)
695SET @command = 'GRANT EXECUTE ON [dbo].aspnet_AnyDataInTables TO ' + QUOTENAME(user)
696EXEC (@command)
697GO
698
699-- Restore the permissions
700EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_AnyDataInTables'
701GO
702
703/*************************************************************/
704/*************************************************************/
705
706IF (NOT EXISTS (SELECT name
707                FROM sysobjects
708                WHERE (name = N'vw_aspnet_Applications')
709                  AND (type = 'V')))
710BEGIN
711  PRINT 'Creating the vw_aspnet_Applications view...'
712  EXEC('
713  CREATE VIEW [dbo].[vw_aspnet_Applications]
714  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
715  FROM [dbo].[aspnet_Applications]
716  ')
717END
718
719-- Restore the permissions
720EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Applications'
721GO
722
723/*************************************************************/
724/*************************************************************/
725
726IF (NOT EXISTS (SELECT name
727                FROM sysobjects
728                WHERE (name = N'vw_aspnet_Users')
729                  AND (type = 'V')))
730BEGIN
731  PRINT 'Creating the vw_aspnet_Users view...'
732  EXEC('
733  CREATE VIEW [dbo].[vw_aspnet_Users]
734  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]
735  FROM [dbo].[aspnet_Users]
736  ')
737END
738
739-- Restore the permissions
740EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Users'
741GO
742
743/*************************************************************/
744/*************************************************************/
745DECLARE @command nvarchar(4000)
746
747SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_Setup_RestorePermissions from ' + QUOTENAME(user)
748EXEC (@command)
749SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion from ' + QUOTENAME(user)
750EXEC (@command)
751GO
752
753DROP TABLE #aspnet_Permissions
754GO
755
756PRINT '----------------------------------------'
757PRINT 'Completed execution of InstallCommon.SQL'
758PRINT '----------------------------------------'
759/**********************************************************************/
760/* InstallMembership.SQL                                              */
761/*                                                                    */
762/* Installs the tables, triggers and stored procedures necessary for  */
763/* supporting the aspnet feature of ASP.Net                           */
764/*                                                                    */
765/* InstallCommon.sql must be run before running this file.            */
766/*
767** Copyright Microsoft, Inc. 2002
768** All Rights Reserved.
769*/
770/**********************************************************************/
771
772PRINT '-------------------------------------------'
773PRINT 'Starting execution of InstallMembership.SQL'
774PRINT '-------------------------------------------'
775GO
776
777SET QUOTED_IDENTIFIER OFF
778SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
779GO
780SET ANSI_PADDING ON
781GO
782SET ANSI_NULL_DFLT_ON ON
783GO
784
785/*************************************************************/
786/*************************************************************/
787/*************************************************************/
788/*************************************************************/
789/*************************************************************/
790
791DECLARE @dbname nvarchar(128)
792
793SET @dbname = N'aspnetdb'
794
795IF (NOT EXISTS (SELECT name
796                FROM master.dbo.sysdatabases
797                WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
798BEGIN
799  RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
800END
801GO
802
803USE [aspnetdb]
804GO
805
806IF (NOT EXISTS (SELECT name
807                FROM sysobjects
808                WHERE (name = N'aspnet_Applications')
809                  AND (type = 'U')))
810BEGIN
811  RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
812END
813
814IF (NOT EXISTS (SELECT name
815                FROM sysobjects
816                WHERE (name = N'aspnet_Users')
817                  AND (type = 'U')))
818BEGIN
819  RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
820END
821
822IF (NOT EXISTS (SELECT name
823              FROM sysobjects
824             WHERE (name = N'aspnet_Applications_CreateApplication')
825               AND (type = 'P')))
826BEGIN
827  RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
828END
829
830IF (NOT EXISTS (SELECT name
831              FROM sysobjects
832             WHERE (name = N'aspnet_Users_CreateUser')
833               AND (type = 'P')))
834BEGIN
835  RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
836END
837
838IF (NOT EXISTS (SELECT name
839              FROM sysobjects
840             WHERE (name = N'aspnet_Users_DeleteUser')
841               AND (type = 'P')))
842BEGIN
843  RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
844END
845
846/*************************************************************/
847/*************************************************************/
848IF (NOT EXISTS (SELECT name
849                FROM sysobjects
850                WHERE (name = N'aspnet_Membership')
851                  AND (type = 'U')))
852BEGIN
853  PRINT 'Creating the aspnet_Membership table...'
854  CREATE TABLE dbo.aspnet_Membership (
855        ApplicationId                           uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
856        UserId                                  uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),
857        Password                                nvarchar(128)       NOT NULL,
858        PasswordFormat                          int                 NOT NULL DEFAULT 0,
859        PasswordSalt                            nvarchar(128)       NOT NULL,
860        MobilePIN                               nvarchar(16),
861        Email                                   nvarchar(256),
862        LoweredEmail                            nvarchar(256),
863        PasswordQuestion                        nvarchar(256),
864        PasswordAnswer                          nvarchar(128),
865        IsApproved                              bit                 NOT NULL,
866        IsLockedOut                             bit                 NOT NULL,
867        CreateDate                              datetime            NOT NULL,
868        LastLoginDate                           datetime            NOT NULL,
869        LastPasswordChangedDate                 datetime            NOT NULL,
870        LastLockoutDate                         datetime            NOT NULL,
871        FailedPasswordAttemptCount              int                 NOT NULL,
872        FailedPasswordAttemptWindowStart        datetime            NOT NULL,
873        FailedPasswordAnswerAttemptCount        int                 NOT NULL,
874        FailedPasswordAnswerAttemptWindowStart  datetime            NOT NULL,
875        Comment                                 ntext )
876  CREATE CLUSTERED INDEX aspnet_Membership_index ON aspnet_Membership(ApplicationId, LoweredEmail)
877END
878GO
879
880/*************************************************************/
881/*************************************************************/
882/*************************************************************/
883
884DECLARE @ver int
885DECLARE @version nchar(100)
886DECLARE @dot int
887DECLARE @hyphen int
888DECLARE @SqlToExec nchar(400)
889
890SELECT @ver = 8
891SELECT @version = @@Version
892SELECT @hyphen  = CHARINDEX(N' - ', @version)
893IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
894BEGIN
895    SELECT @hyphen = @hyphen + 3
896    SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
897    IF (NOT(@dot IS NULL) AND @dot > @hyphen)
898    BEGIN
899        SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
900        SELECT @ver     = CONVERT(int, @version)
901    END
902END
903
904/*************************************************************/
905
906IF (@ver >= 8)
907    EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000
908
909/*************************************************************/
910/*************************************************************/
911
912IF (EXISTS (SELECT name
913              FROM sysobjects
914             WHERE (name = N'aspnet_Membership_CreateUser')
915               AND (type = 'P')))
916DROP PROCEDURE dbo.aspnet_Membership_CreateUser
917GO
918CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
919    @ApplicationName                        nvarchar(256),
920    @UserName                               nvarchar(256),
921    @Password                               nvarchar(128),
922    @PasswordSalt                           nvarchar(128),
923    @Email                                  nvarchar(256),
924    @PasswordQuestion                       nvarchar(256),
925    @PasswordAnswer                         nvarchar(128),
926    @IsApproved                             bit,
927    @CurrentTimeUtc                         datetime,
928    @CreateDate                             datetime = NULL,
929    @UniqueEmail                            int      = 0,
930    @PasswordFormat                         int      = 0,
931    @UserId                                 uniqueidentifier OUTPUT
932AS
933BEGIN
934    DECLARE @ApplicationId uniqueidentifier
935    SELECT  @ApplicationId = NULL
936
937    DECLARE @NewUserId uniqueidentifier
938    SELECT @NewUserId = NULL
939
940    DECLARE @IsLockedOut bit
941    SET @IsLockedOut = 0
942
943    DECLARE @LastLockoutDate  datetime
944    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
945
946    DECLARE @FailedPasswordAttemptCount int
947    SET @FailedPasswordAttemptCount = 0
948
949    DECLARE @FailedPasswordAttemptWindowStart  datetime
950    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
951
952    DECLARE @FailedPasswordAnswerAttemptCount int
953    SET @FailedPasswordAnswerAttemptCount = 0
954
955    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
956    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
957
958    DECLARE @NewUserCreated bit
959    DECLARE @ReturnValue   int
960    SET @ReturnValue = 0
961
962    DECLARE @ErrorCode     int
963    SET @ErrorCode = 0
964
965    DECLARE @TranStarted   bit
966    SET @TranStarted = 0
967
968    IF( @@TRANCOUNT = 0 )
969    BEGIN
970	    BEGIN TRANSACTION
971	    SET @TranStarted = 1
972    END
973    ELSE
974    	SET @TranStarted = 0
975
976    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
977
978    IF( @@ERROR <> 0 )
979    BEGIN
980        SET @ErrorCode = -1
981        GOTO Cleanup
982    END
983
984    SET @CreateDate = @CurrentTimeUtc
985
986    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
987    IF ( @NewUserId IS NULL )
988    BEGIN
989        SET @NewUserId = @UserId
990        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
991        SET @NewUserCreated = 1
992    END
993    ELSE
994    BEGIN
995        SET @NewUserCreated = 0
996        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
997        BEGIN
998            SET @ErrorCode = 6
999            GOTO Cleanup
1000        END
1001    END
1002
1003    IF( @@ERROR <> 0 )
1004    BEGIN
1005        SET @ErrorCode = -1
1006        GOTO Cleanup
1007    END
1008
1009    IF( @ReturnValue = -1 )
1010    BEGIN
1011        SET @ErrorCode = 10
1012        GOTO Cleanup
1013    END
1014
1015    IF ( EXISTS ( SELECT UserId
1016                  FROM   dbo.aspnet_Membership
1017                  WHERE  @NewUserId = UserId ) )
1018    BEGIN
1019        SET @ErrorCode = 6
1020        GOTO Cleanup
1021    END
1022
1023    SET @UserId = @NewUserId
1024
1025    IF (@UniqueEmail = 1)
1026    BEGIN
1027        IF (EXISTS (SELECT *
1028                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
1029                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
1030        BEGIN
1031            SET @ErrorCode = 7
1032            GOTO Cleanup
1033        END
1034    END
1035
1036    IF (@NewUserCreated = 0)
1037    BEGIN
1038        UPDATE dbo.aspnet_Users
1039        SET    LastActivityDate = @CreateDate
1040        WHERE  @UserId = UserId
1041        IF( @@ERROR <> 0 )
1042        BEGIN
1043            SET @ErrorCode = -1
1044            GOTO Cleanup
1045        END
1046    END
1047
1048    INSERT INTO dbo.aspnet_Membership
1049                ( ApplicationId,
1050                  UserId,
1051                  Password,
1052                  PasswordSalt,
1053                  Email,
1054                  LoweredEmail,
1055                  PasswordQuestion,
1056                  PasswordAnswer,
1057                  PasswordFormat,
1058                  IsApproved,
1059                  IsLockedOut,
1060                  CreateDate,
1061                  LastLoginDate,
1062                  LastPasswordChangedDate,
1063                  LastLockoutDate,
1064                  FailedPasswordAttemptCount,
1065                  FailedPasswordAttemptWindowStart,
1066                  FailedPasswordAnswerAttemptCount,
1067                  FailedPasswordAnswerAttemptWindowStart )
1068         VALUES ( @ApplicationId,
1069                  @UserId,
1070                  @Password,
1071                  @PasswordSalt,
1072                  @Email,
1073                  LOWER(@Email),
1074                  @PasswordQuestion,
1075                  @PasswordAnswer,
1076                  @PasswordFormat,
1077                  @IsApproved,
1078                  @IsLockedOut,
1079                  @CreateDate,
1080                  @CreateDate,
1081                  @CreateDate,
1082                  @LastLockoutDate,
1083                  @FailedPasswordAttemptCount,
1084                  @FailedPasswordAttemptWindowStart,
1085                  @FailedPasswordAnswerAttemptCount,
1086                  @FailedPasswordAnswerAttemptWindowStart )
1087
1088    IF( @@ERROR <> 0 )
1089    BEGIN
1090        SET @ErrorCode = -1
1091        GOTO Cleanup
1092    END
1093
1094    IF( @TranStarted = 1 )
1095    BEGIN
1096	    SET @TranStarted = 0
1097	    COMMIT TRANSACTION
1098    END
1099
1100    RETURN 0
1101
1102Cleanup:
1103
1104    IF( @TranStarted = 1 )
1105    BEGIN
1106        SET @TranStarted = 0
1107    	ROLLBACK TRANSACTION
1108    END
1109
1110    RETURN @ErrorCode
1111
1112END
1113GO
1114
1115/*************************************************************/
1116/*************************************************************/
1117
1118IF (EXISTS (SELECT name
1119              FROM sysobjects
1120             WHERE (name = N'aspnet_Membership_GetUserByName')
1121               AND (type = 'P')))
1122DROP PROCEDURE dbo.aspnet_Membership_GetUserByName
1123GO
1124CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName
1125    @ApplicationName      nvarchar(256),
1126    @UserName             nvarchar(256),
1127    @CurrentTimeUtc       datetime,
1128    @UpdateLastActivity   bit = 0
1129AS
1130BEGIN
1131    DECLARE @UserId uniqueidentifier
1132
1133    IF (@UpdateLastActivity = 1)
1134    BEGIN
1135        -- select user ID from aspnet_users table
1136        SELECT TOP 1 @UserId = u.UserId
1137        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1138        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
1139                u.ApplicationId = a.ApplicationId    AND
1140                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
1141
1142        IF (@@ROWCOUNT = 0) -- Username not found
1143            RETURN -1
1144
1145        UPDATE   dbo.aspnet_Users
1146        SET      LastActivityDate = @CurrentTimeUtc
1147        WHERE    @UserId = UserId
1148
1149        SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1150                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
1151                u.UserId, m.IsLockedOut, m.LastLockoutDate
1152        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1153        WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
1154    END
1155    ELSE
1156    BEGIN
1157        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1158                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
1159                u.UserId, m.IsLockedOut,m.LastLockoutDate
1160        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1161        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
1162                u.ApplicationId = a.ApplicationId    AND
1163                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
1164
1165        IF (@@ROWCOUNT = 0) -- Username not found
1166            RETURN -1
1167    END
1168
1169    RETURN 0
1170END
1171GO
1172
1173/*************************************************************/
1174/*************************************************************/
1175
1176IF (EXISTS (SELECT name
1177              FROM sysobjects
1178             WHERE (name = N'aspnet_Membership_GetUserByUserId')
1179               AND (type = 'P')))
1180DROP PROCEDURE dbo.aspnet_Membership_GetUserByUserId
1181GO
1182CREATE PROCEDURE dbo.aspnet_Membership_GetUserByUserId
1183    @UserId               uniqueidentifier,
1184    @CurrentTimeUtc       datetime,
1185    @UpdateLastActivity   bit = 0
1186AS
1187BEGIN
1188    IF ( @UpdateLastActivity = 1 )
1189    BEGIN
1190        UPDATE   dbo.aspnet_Users
1191        SET      LastActivityDate = @CurrentTimeUtc
1192        FROM     dbo.aspnet_Users
1193        WHERE    @UserId = UserId
1194
1195        IF ( @@ROWCOUNT = 0 ) -- User ID not found
1196            RETURN -1
1197    END
1198
1199    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1200            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
1201            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
1202            m.LastLockoutDate
1203    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
1204    WHERE   @UserId = u.UserId AND u.UserId = m.UserId
1205
1206    IF ( @@ROWCOUNT = 0 ) -- User ID not found
1207       RETURN -1
1208
1209    RETURN 0
1210END
1211GO
1212
1213/*************************************************************/
1214/*************************************************************/
1215
1216IF (EXISTS (SELECT name
1217              FROM sysobjects
1218             WHERE (name = N'aspnet_Membership_GetUserByEmail')
1219               AND (type = 'P')))
1220DROP PROCEDURE dbo.aspnet_Membership_GetUserByEmail
1221GO
1222CREATE PROCEDURE dbo.aspnet_Membership_GetUserByEmail
1223    @ApplicationName  nvarchar(256),
1224    @Email            nvarchar(256)
1225AS
1226BEGIN
1227    IF( @Email IS NULL )
1228        SELECT  u.UserName
1229        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1230        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
1231                u.ApplicationId = a.ApplicationId    AND
1232                u.UserId = m.UserId AND
1233                m.LoweredEmail IS NULL
1234    ELSE
1235        SELECT  u.UserName
1236        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1237        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
1238                u.ApplicationId = a.ApplicationId    AND
1239                u.UserId = m.UserId AND
1240                LOWER(@Email) = m.LoweredEmail
1241
1242    IF (@@rowcount = 0)
1243        RETURN(1)
1244    RETURN(0)
1245END
1246GO
1247
1248/*************************************************************/
1249/*************************************************************/
1250
1251IF ( EXISTS( SELECT name
1252             FROM sysobjects
1253             WHERE ( name = N'aspnet_Membership_GetPasswordWithFormat' )
1254                   AND ( type = 'P' ) ) )
1255DROP PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
1256GO
1257CREATE PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
1258    @ApplicationName                nvarchar(256),
1259    @UserName                       nvarchar(256),
1260    @UpdateLastLoginActivityDate    bit,
1261    @CurrentTimeUtc                 datetime
1262AS
1263BEGIN
1264    DECLARE @IsLockedOut                        bit
1265    DECLARE @UserId                             uniqueidentifier
1266    DECLARE @Password                           nvarchar(128)
1267    DECLARE @PasswordSalt                       nvarchar(128)
1268    DECLARE @PasswordFormat                     int
1269    DECLARE @FailedPasswordAttemptCount         int
1270    DECLARE @FailedPasswordAnswerAttemptCount   int
1271    DECLARE @IsApproved                         bit
1272    DECLARE @LastActivityDate                   datetime
1273    DECLARE @LastLoginDate                      datetime
1274
1275    SELECT  @UserId          = NULL
1276
1277    SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
1278            @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
1279		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
1280            @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
1281    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
1282    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
1283            u.ApplicationId = a.ApplicationId    AND
1284            u.UserId = m.UserId AND
1285            LOWER(@UserName) = u.LoweredUserName
1286
1287    IF (@UserId IS NULL)
1288        RETURN 1
1289
1290    IF (@IsLockedOut = 1)
1291        RETURN 99
1292
1293    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
1294             @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate
1295
1296    IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
1297    BEGIN
1298        UPDATE  dbo.aspnet_Membership
1299        SET     LastLoginDate = @CurrentTimeUtc
1300        WHERE   UserId = @UserId
1301
1302        UPDATE  dbo.aspnet_Users
1303        SET     LastActivityDate = @CurrentTimeUtc
1304        WHERE   @UserId = UserId
1305    END
1306
1307
1308    RETURN 0
1309END
1310GO
1311/*************************************************************/
1312/*************************************************************/
1313
1314IF ( EXISTS( SELECT name
1315             FROM sysobjects
1316             WHERE ( name = N'aspnet_Membership_UpdateUserInfo' )
1317                   AND ( type = 'P' ) ) )
1318DROP PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
1319GO
1320CREATE PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
1321    @ApplicationName                nvarchar(256),
1322    @UserName                       nvarchar(256),
1323    @IsPasswordCorrect              bit,
1324    @UpdateLastLoginActivityDate    bit,
1325    @MaxInvalidPasswordAttempts     int,
1326    @PasswordAttemptWindow          int,
1327    @CurrentTimeUtc                 datetime,
1328    @LastLoginDate                  datetime,
1329    @LastActivityDate               datetime
1330AS
1331BEGIN
1332    DECLARE @UserId                                 uniqueidentifier
1333    DECLARE @IsApproved                             bit
1334    DECLARE @IsLockedOut                            bit
1335    DECLARE @LastLockoutDate                        datetime
1336    DECLARE @FailedPasswordAttemptCount             int
1337    DECLARE @FailedPasswordAttemptWindowStart       datetime
1338    DECLARE @FailedPasswordAnswerAttemptCount       int
1339    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1340
1341    DECLARE @ErrorCode     int
1342    SET @ErrorCode = 0
1343
1344    DECLARE @TranStarted   bit
1345    SET @TranStarted = 0
1346
1347    IF( @@TRANCOUNT = 0 )
1348    BEGIN
1349	    BEGIN TRANSACTION
1350	    SET @TranStarted = 1
1351    END
1352    ELSE
1353    	SET @TranStarted = 0
1354
1355    SELECT  @UserId = u.UserId,
1356            @IsApproved = m.IsApproved,
1357            @IsLockedOut = m.IsLockedOut,
1358            @LastLockoutDate = m.LastLockoutDate,
1359            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
1360            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
1361            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
1362            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
1363    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
1364    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
1365            u.ApplicationId = a.ApplicationId    AND
1366            u.UserId = m.UserId AND
1367            LOWER(@UserName) = u.LoweredUserName
1368
1369    IF ( @@rowcount = 0 )
1370    BEGIN
1371        SET @ErrorCode = 1
1372        GOTO Cleanup
1373    END
1374
1375    IF( @IsLockedOut = 1 )
1376    BEGIN
1377        GOTO Cleanup
1378    END
1379
1380    IF( @IsPasswordCorrect = 0 )
1381    BEGIN
1382        IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
1383        BEGIN
1384            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
1385            SET @FailedPasswordAttemptCount = 1
1386        END
1387        ELSE
1388        BEGIN
1389            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
1390            SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
1391        END
1392
1393        BEGIN
1394            IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
1395            BEGIN
1396                SET @IsLockedOut = 1
1397                SET @LastLockoutDate = @CurrentTimeUtc
1398            END
1399        END
1400    END
1401    ELSE
1402    BEGIN
1403        IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
1404        BEGIN
1405            SET @FailedPasswordAttemptCount = 0
1406            SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
1407            SET @FailedPasswordAnswerAttemptCount = 0
1408            SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
1409            SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
1410        END
1411    END
1412
1413    IF( @UpdateLastLoginActivityDate = 1 )
1414    BEGIN
1415        UPDATE  dbo.aspnet_Users
1416        SET     LastActivityDate = @LastActivityDate
1417        WHERE   @UserId = UserId
1418
1419        IF( @@ERROR <> 0 )
1420        BEGIN
1421            SET @ErrorCode = -1
1422            GOTO Cleanup
1423        END
1424
1425        UPDATE  dbo.aspnet_Membership
1426        SET     LastLoginDate = @LastLoginDate
1427        WHERE   UserId = @UserId
1428
1429        IF( @@ERROR <> 0 )
1430        BEGIN
1431            SET @ErrorCode = -1
1432            GOTO Cleanup
1433        END
1434    END
1435
1436
1437    UPDATE dbo.aspnet_Membership
1438    SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1439        FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1440        FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1441        FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1442        FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1443    WHERE @UserId = UserId
1444
1445    IF( @@ERROR <> 0 )
1446    BEGIN
1447        SET @ErrorCode = -1
1448        GOTO Cleanup
1449    END
1450
1451    IF( @TranStarted = 1 )
1452    BEGIN
1453	SET @TranStarted = 0
1454	COMMIT TRANSACTION
1455    END
1456
1457    RETURN @ErrorCode
1458
1459Cleanup:
1460
1461    IF( @TranStarted = 1 )
1462    BEGIN
1463        SET @TranStarted = 0
1464    	ROLLBACK TRANSACTION
1465    END
1466
1467    RETURN @ErrorCode
1468
1469END
1470GO
1471
1472/*************************************************************/
1473/*************************************************************/
1474
1475IF (EXISTS (SELECT name
1476              FROM sysobjects
1477             WHERE (name = N'aspnet_Membership_GetPassword')
1478               AND (type = 'P')))
1479DROP PROCEDURE dbo.aspnet_Membership_GetPassword
1480GO
1481CREATE PROCEDURE dbo.aspnet_Membership_GetPassword
1482    @ApplicationName                nvarchar(256),
1483    @UserName                       nvarchar(256),
1484    @MaxInvalidPasswordAttempts     int,
1485    @PasswordAttemptWindow          int,
1486    @CurrentTimeUtc                 datetime,
1487    @PasswordAnswer                 nvarchar(128) = NULL
1488AS
1489BEGIN
1490    DECLARE @UserId                                 uniqueidentifier
1491    DECLARE @PasswordFormat                         int
1492    DECLARE @Password                               nvarchar(128)
1493    DECLARE @passAns                                nvarchar(128)
1494    DECLARE @IsLockedOut                            bit
1495    DECLARE @LastLockoutDate                        datetime
1496    DECLARE @FailedPasswordAttemptCount             int
1497    DECLARE @FailedPasswordAttemptWindowStart       datetime
1498    DECLARE @FailedPasswordAnswerAttemptCount       int
1499    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1500
1501    DECLARE @ErrorCode     int
1502    SET @ErrorCode = 0
1503
1504    DECLARE @TranStarted   bit
1505    SET @TranStarted = 0
1506
1507    IF( @@TRANCOUNT = 0 )
1508    BEGIN
1509	    BEGIN TRANSACTION
1510	    SET @TranStarted = 1
1511    END
1512    ELSE
1513    	SET @TranStarted = 0
1514
1515    SELECT  @UserId = u.UserId,
1516            @Password = m.Password,
1517            @passAns = m.PasswordAnswer,
1518            @PasswordFormat = m.PasswordFormat,
1519            @IsLockedOut = m.IsLockedOut,
1520            @LastLockoutDate = m.LastLockoutDate,
1521            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
1522            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
1523            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
1524            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
1525    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
1526    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
1527            u.ApplicationId = a.ApplicationId    AND
1528            u.UserId = m.UserId AND
1529            LOWER(@UserName) = u.LoweredUserName
1530
1531    IF ( @@rowcount = 0 )
1532    BEGIN
1533        SET @ErrorCode = 1
1534        GOTO Cleanup
1535    END
1536
1537    IF( @IsLockedOut = 1 )
1538    BEGIN
1539        SET @ErrorCode = 99
1540        GOTO Cleanup
1541    END
1542
1543    IF ( NOT( @PasswordAnswer IS NULL ) )
1544    BEGIN
1545        IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
1546        BEGIN
1547            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
1548            BEGIN
1549                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1550                SET @FailedPasswordAnswerAttemptCount = 1
1551            END
1552            ELSE
1553            BEGIN
1554                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
1555                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1556            END
1557
1558            BEGIN
1559                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
1560                BEGIN
1561                    SET @IsLockedOut = 1
1562                    SET @LastLockoutDate = @CurrentTimeUtc
1563                END
1564            END
1565
1566            SET @ErrorCode = 3
1567        END
1568        ELSE
1569        BEGIN
1570            IF( @FailedPasswordAnswerAttemptCount > 0 )
1571            BEGIN
1572                SET @FailedPasswordAnswerAttemptCount = 0
1573                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
1574            END
1575        END
1576
1577        UPDATE dbo.aspnet_Membership
1578        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1579            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1580            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1581            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1582            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1583        WHERE @UserId = UserId
1584
1585        IF( @@ERROR <> 0 )
1586        BEGIN
1587            SET @ErrorCode = -1
1588            GOTO Cleanup
1589        END
1590    END
1591
1592    IF( @TranStarted = 1 )
1593    BEGIN
1594	SET @TranStarted = 0
1595	COMMIT TRANSACTION
1596    END
1597
1598    IF( @ErrorCode = 0 )
1599        SELECT @Password, @PasswordFormat
1600
1601    RETURN @ErrorCode
1602
1603Cleanup:
1604
1605    IF( @TranStarted = 1 )
1606    BEGIN
1607        SET @TranStarted = 0
1608    	ROLLBACK TRANSACTION
1609    END
1610
1611    RETURN @ErrorCode
1612
1613END
1614GO
1615
1616/*************************************************************/
1617/*************************************************************/
1618
1619IF (EXISTS (SELECT name
1620              FROM sysobjects
1621             WHERE (name = N'aspnet_Membership_SetPassword')
1622               AND (type = 'P')))
1623DROP PROCEDURE dbo.aspnet_Membership_SetPassword
1624GO
1625CREATE PROCEDURE dbo.aspnet_Membership_SetPassword
1626    @ApplicationName  nvarchar(256),
1627    @UserName         nvarchar(256),
1628    @NewPassword      nvarchar(128),
1629    @PasswordSalt     nvarchar(128),
1630    @CurrentTimeUtc   datetime,
1631    @PasswordFormat   int = 0
1632AS
1633BEGIN
1634    DECLARE @UserId uniqueidentifier
1635    SELECT  @UserId = NULL
1636    SELECT  @UserId = u.UserId
1637    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1638    WHERE   LoweredUserName = LOWER(@UserName) AND
1639            u.ApplicationId = a.ApplicationId  AND
1640            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1641            u.UserId = m.UserId
1642
1643    IF (@UserId IS NULL)
1644        RETURN(1)
1645
1646    UPDATE dbo.aspnet_Membership
1647    SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
1648        LastPasswordChangedDate = @CurrentTimeUtc
1649    WHERE @UserId = UserId
1650    RETURN(0)
1651END
1652GO
1653
1654/*************************************************************/
1655/*************************************************************/
1656
1657IF (EXISTS (SELECT name
1658              FROM sysobjects
1659             WHERE (name = N'aspnet_Membership_ResetPassword')
1660               AND (type = 'P')))
1661DROP PROCEDURE dbo.aspnet_Membership_ResetPassword
1662GO
1663CREATE PROCEDURE dbo.aspnet_Membership_ResetPassword
1664    @ApplicationName             nvarchar(256),
1665    @UserName                    nvarchar(256),
1666    @NewPassword                 nvarchar(128),
1667    @MaxInvalidPasswordAttempts  int,
1668    @PasswordAttemptWindow       int,
1669    @PasswordSalt                nvarchar(128),
1670    @CurrentTimeUtc              datetime,
1671    @PasswordFormat              int = 0,
1672    @PasswordAnswer              nvarchar(128) = NULL
1673AS
1674BEGIN
1675    DECLARE @IsLockedOut                            bit
1676    DECLARE @LastLockoutDate                        datetime
1677    DECLARE @FailedPasswordAttemptCount             int
1678    DECLARE @FailedPasswordAttemptWindowStart       datetime
1679    DECLARE @FailedPasswordAnswerAttemptCount       int
1680    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
1681
1682    DECLARE @UserId                                 uniqueidentifier
1683    SET     @UserId = NULL
1684
1685    DECLARE @ErrorCode     int
1686    SET @ErrorCode = 0
1687
1688    DECLARE @TranStarted   bit
1689    SET @TranStarted = 0
1690
1691    IF( @@TRANCOUNT = 0 )
1692    BEGIN
1693	    BEGIN TRANSACTION
1694	    SET @TranStarted = 1
1695    END
1696    ELSE
1697    	SET @TranStarted = 0
1698
1699    SELECT  @UserId = u.UserId
1700    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1701    WHERE   LoweredUserName = LOWER(@UserName) AND
1702            u.ApplicationId = a.ApplicationId  AND
1703            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1704            u.UserId = m.UserId
1705
1706    IF ( @UserId IS NULL )
1707    BEGIN
1708        SET @ErrorCode = 1
1709        GOTO Cleanup
1710    END
1711
1712    SELECT @IsLockedOut = IsLockedOut,
1713           @LastLockoutDate = LastLockoutDate,
1714           @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
1715           @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
1716           @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
1717           @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
1718    FROM dbo.aspnet_Membership WITH ( UPDLOCK )
1719    WHERE @UserId = UserId
1720
1721    IF( @IsLockedOut = 1 )
1722    BEGIN
1723        SET @ErrorCode = 99
1724        GOTO Cleanup
1725    END
1726
1727    UPDATE dbo.aspnet_Membership
1728    SET    Password = @NewPassword,
1729           LastPasswordChangedDate = @CurrentTimeUtc,
1730           PasswordFormat = @PasswordFormat,
1731           PasswordSalt = @PasswordSalt
1732    WHERE  @UserId = UserId AND
1733           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
1734
1735    IF ( @@ROWCOUNT = 0 )
1736        BEGIN
1737            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
1738            BEGIN
1739                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1740                SET @FailedPasswordAnswerAttemptCount = 1
1741            END
1742            ELSE
1743            BEGIN
1744                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
1745                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
1746            END
1747
1748            BEGIN
1749                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
1750                BEGIN
1751                    SET @IsLockedOut = 1
1752                    SET @LastLockoutDate = @CurrentTimeUtc
1753                END
1754            END
1755
1756            SET @ErrorCode = 3
1757        END
1758    ELSE
1759        BEGIN
1760            IF( @FailedPasswordAnswerAttemptCount > 0 )
1761            BEGIN
1762                SET @FailedPasswordAnswerAttemptCount = 0
1763                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
1764            END
1765        END
1766
1767    IF( NOT ( @PasswordAnswer IS NULL ) )
1768    BEGIN
1769        UPDATE dbo.aspnet_Membership
1770        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
1771            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
1772            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
1773            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
1774            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
1775        WHERE @UserId = UserId
1776
1777        IF( @@ERROR <> 0 )
1778        BEGIN
1779            SET @ErrorCode = -1
1780            GOTO Cleanup
1781        END
1782    END
1783
1784    IF( @TranStarted = 1 )
1785    BEGIN
1786	SET @TranStarted = 0
1787	COMMIT TRANSACTION
1788    END
1789
1790    RETURN @ErrorCode
1791
1792Cleanup:
1793
1794    IF( @TranStarted = 1 )
1795    BEGIN
1796        SET @TranStarted = 0
1797    	ROLLBACK TRANSACTION
1798    END
1799
1800    RETURN @ErrorCode
1801
1802END
1803GO
1804
1805/*************************************************************/
1806/*************************************************************/
1807
1808IF (EXISTS (SELECT name
1809              FROM sysobjects
1810             WHERE (name = N'aspnet_Membership_UnlockUser')
1811               AND (type = 'P')))
1812DROP PROCEDURE dbo.aspnet_Membership_UnlockUser
1813GO
1814CREATE PROCEDURE dbo.aspnet_Membership_UnlockUser
1815    @ApplicationName                         nvarchar(256),
1816    @UserName                                nvarchar(256)
1817AS
1818BEGIN
1819    DECLARE @UserId uniqueidentifier
1820    SELECT  @UserId = NULL
1821    SELECT  @UserId = u.UserId
1822    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1823    WHERE   LoweredUserName = LOWER(@UserName) AND
1824            u.ApplicationId = a.ApplicationId  AND
1825            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1826            u.UserId = m.UserId
1827
1828    IF ( @UserId IS NULL )
1829        RETURN 1
1830
1831    UPDATE dbo.aspnet_Membership
1832    SET IsLockedOut = 0,
1833        FailedPasswordAttemptCount = 0,
1834        FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
1835        FailedPasswordAnswerAttemptCount = 0,
1836        FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
1837        LastLockoutDate = CONVERT( datetime, '17540101', 112 )
1838    WHERE @UserId = UserId
1839
1840    RETURN 0
1841END
1842GO
1843
1844/*************************************************************/
1845/*************************************************************/
1846
1847IF (EXISTS (SELECT name
1848              FROM sysobjects
1849             WHERE (name = N'aspnet_Membership_UpdateUser')
1850               AND (type = 'P')))
1851DROP PROCEDURE dbo.aspnet_Membership_UpdateUser
1852GO
1853CREATE PROCEDURE dbo.aspnet_Membership_UpdateUser
1854    @ApplicationName      nvarchar(256),
1855    @UserName             nvarchar(256),
1856    @Email                nvarchar(256),
1857    @Comment              ntext,
1858    @IsApproved           bit,
1859    @LastLoginDate        datetime,
1860    @LastActivityDate     datetime,
1861    @UniqueEmail          int,
1862    @CurrentTimeUtc       datetime
1863AS
1864BEGIN
1865    DECLARE @UserId uniqueidentifier
1866    DECLARE @ApplicationId uniqueidentifier
1867    SELECT  @UserId = NULL
1868    SELECT  @UserId = u.UserId, @ApplicationId = a.ApplicationId
1869    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
1870    WHERE   LoweredUserName = LOWER(@UserName) AND
1871            u.ApplicationId = a.ApplicationId  AND
1872            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1873            u.UserId = m.UserId
1874
1875    IF (@UserId IS NULL)
1876        RETURN(1)
1877
1878    IF (@UniqueEmail = 1)
1879    BEGIN
1880        IF (EXISTS (SELECT *
1881                    FROM  dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
1882                    WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
1883        BEGIN
1884            RETURN(7)
1885        END
1886    END
1887
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    ELSE
1897	SET @TranStarted = 0
1898
1899    UPDATE dbo.aspnet_Users WITH (ROWLOCK)
1900    SET
1901         LastActivityDate = @LastActivityDate
1902    WHERE
1903       @UserId = UserId
1904
1905    IF( @@ERROR <> 0 )
1906        GOTO Cleanup
1907
1908    UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
1909    SET
1910         Email            = @Email,
1911         LoweredEmail     = LOWER(@Email),
1912         Comment          = @Comment,
1913         IsApproved       = @IsApproved,
1914         LastLoginDate    = @LastLoginDate
1915    WHERE
1916       @UserId = UserId
1917
1918    IF( @@ERROR <> 0 )
1919        GOTO Cleanup
1920
1921    IF( @TranStarted = 1 )
1922    BEGIN
1923	SET @TranStarted = 0
1924	COMMIT TRANSACTION
1925    END
1926
1927    RETURN 0
1928
1929Cleanup:
1930
1931    IF( @TranStarted = 1 )
1932    BEGIN
1933        SET @TranStarted = 0
1934    	ROLLBACK TRANSACTION
1935    END
1936
1937    RETURN -1
1938END
1939GO
1940
1941/*************************************************************/
1942/*************************************************************/
1943
1944IF (EXISTS (SELECT name
1945              FROM sysobjects
1946             WHERE (name = N'aspnet_Membership_ChangePasswordQuestionAndAnswer')
1947               AND (type = 'P')))
1948DROP PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
1949GO
1950CREATE PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
1951    @ApplicationName       nvarchar(256),
1952    @UserName              nvarchar(256),
1953    @NewPasswordQuestion   nvarchar(256),
1954    @NewPasswordAnswer     nvarchar(128)
1955AS
1956BEGIN
1957    DECLARE @UserId uniqueidentifier
1958    SELECT  @UserId = NULL
1959    SELECT  @UserId = u.UserId
1960    FROM    dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
1961    WHERE   LoweredUserName = LOWER(@UserName) AND
1962            u.ApplicationId = a.ApplicationId  AND
1963            LOWER(@ApplicationName) = a.LoweredApplicationName AND
1964            u.UserId = m.UserId
1965    IF (@UserId IS NULL)
1966    BEGIN
1967        RETURN(1)
1968    END
1969
1970    UPDATE dbo.aspnet_Membership
1971    SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
1972    WHERE  UserId=@UserId
1973    RETURN(0)
1974END
1975GO
1976/*************************************************************/
1977/*************************************************************/
1978
1979IF (EXISTS (SELECT name
1980              FROM sysobjects
1981             WHERE (name = N'aspnet_Membership_GetAllUsers')
1982               AND (type = 'P')))
1983DROP PROCEDURE dbo.aspnet_Membership_GetAllUsers
1984GO
1985CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers
1986    @ApplicationName       nvarchar(256),
1987    @PageIndex             int,
1988    @PageSize              int
1989AS
1990BEGIN
1991    DECLARE @ApplicationId uniqueidentifier
1992    SELECT  @ApplicationId = NULL
1993    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
1994    IF (@ApplicationId IS NULL)
1995        RETURN 0
1996
1997
1998    -- Set the page bounds
1999    DECLARE @PageLowerBound int
2000    DECLARE @PageUpperBound int
2001    DECLARE @TotalRecords   int
2002    SET @PageLowerBound = @PageSize * @PageIndex
2003    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2004
2005    -- Create a temp table TO store the select results
2006    CREATE TABLE #PageIndexForUsers
2007    (
2008        IndexId int IDENTITY (0, 1) NOT NULL,
2009        UserId uniqueidentifier
2010    )
2011
2012    -- Insert into our temp table
2013    INSERT INTO #PageIndexForUsers (UserId)
2014    SELECT u.UserId
2015    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
2016    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
2017    ORDER BY u.UserName
2018
2019    SELECT @TotalRecords = @@ROWCOUNT
2020
2021    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
2022            m.CreateDate,
2023            m.LastLoginDate,
2024            u.LastActivityDate,
2025            m.LastPasswordChangedDate,
2026            u.UserId, m.IsLockedOut,
2027            m.LastLockoutDate
2028    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
2029    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
2030           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
2031    ORDER BY u.UserName
2032    RETURN @TotalRecords
2033END
2034GO
2035/*************************************************************/
2036/*************************************************************/
2037
2038IF (EXISTS (SELECT name
2039              FROM sysobjects
2040             WHERE (name = N'aspnet_Membership_GetNumberOfUsersOnline')
2041               AND (type = 'P')))
2042DROP PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
2043GO
2044CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
2045    @ApplicationName            nvarchar(256),
2046    @MinutesSinceLastInActive   int,
2047    @CurrentTimeUtc             datetime
2048AS
2049BEGIN
2050    DECLARE @DateActive datetime
2051    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)
2052
2053    DECLARE @NumOnline int
2054    SELECT  @NumOnline = COUNT(*)
2055    FROM    dbo.aspnet_Users u(NOLOCK),
2056            dbo.aspnet_Applications a(NOLOCK),
2057            dbo.aspnet_Membership m(NOLOCK)
2058    WHERE   u.ApplicationId = a.ApplicationId                  AND
2059            LastActivityDate > @DateActive                     AND
2060            a.LoweredApplicationName = LOWER(@ApplicationName) AND
2061            u.UserId = m.UserId
2062    RETURN(@NumOnline)
2063END
2064GO
2065
2066
2067/*************************************************************/
2068/*************************************************************/
2069IF (EXISTS (SELECT name
2070              FROM sysobjects
2071             WHERE (name = N'aspnet_Membership_FindUsersByName')
2072               AND (type = 'P')))
2073DROP PROCEDURE dbo.aspnet_Membership_FindUsersByName
2074GO
2075CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByName
2076    @ApplicationName       nvarchar(256),
2077    @UserNameToMatch       nvarchar(256),
2078    @PageIndex             int,
2079    @PageSize              int
2080AS
2081BEGIN
2082    DECLARE @ApplicationId uniqueidentifier
2083    SELECT  @ApplicationId = NULL
2084    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2085    IF (@ApplicationId IS NULL)
2086        RETURN 0
2087
2088    -- Set the page bounds
2089    DECLARE @PageLowerBound int
2090    DECLARE @PageUpperBound int
2091    DECLARE @TotalRecords   int
2092    SET @PageLowerBound = @PageSize * @PageIndex
2093    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2094
2095    -- Create a temp table TO store the select results
2096    CREATE TABLE #PageIndexForUsers
2097    (
2098        IndexId int IDENTITY (0, 1) NOT NULL,
2099        UserId uniqueidentifier
2100    )
2101
2102    -- Insert into our temp table
2103    INSERT INTO #PageIndexForUsers (UserId)
2104        SELECT u.UserId
2105        FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
2106        WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
2107        ORDER BY u.UserName
2108
2109
2110    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
2111            m.CreateDate,
2112            m.LastLoginDate,
2113            u.LastActivityDate,
2114            m.LastPasswordChangedDate,
2115            u.UserId, m.IsLockedOut,
2116            m.LastLockoutDate
2117    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
2118    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
2119           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
2120    ORDER BY u.UserName
2121
2122    SELECT  @TotalRecords = COUNT(*)
2123    FROM    #PageIndexForUsers
2124    RETURN @TotalRecords
2125END
2126GO
2127/*************************************************************/
2128/*************************************************************/
2129IF (EXISTS (SELECT name
2130              FROM sysobjects
2131             WHERE (name = N'aspnet_Membership_FindUsersByEmail')
2132               AND (type = 'P')))
2133DROP PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
2134GO
2135CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
2136    @ApplicationName       nvarchar(256),
2137    @EmailToMatch          nvarchar(256),
2138    @PageIndex             int,
2139    @PageSize              int
2140AS
2141BEGIN
2142    DECLARE @ApplicationId uniqueidentifier
2143    SELECT  @ApplicationId = NULL
2144    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2145    IF (@ApplicationId IS NULL)
2146        RETURN 0
2147
2148    -- Set the page bounds
2149    DECLARE @PageLowerBound int
2150    DECLARE @PageUpperBound int
2151    DECLARE @TotalRecords   int
2152    SET @PageLowerBound = @PageSize * @PageIndex
2153    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2154
2155    -- Create a temp table TO store the select results
2156    CREATE TABLE #PageIndexForUsers
2157    (
2158        IndexId int IDENTITY (0, 1) NOT NULL,
2159        UserId uniqueidentifier
2160    )
2161
2162    -- Insert into our temp table
2163    IF( @EmailToMatch IS NULL )
2164        INSERT INTO #PageIndexForUsers (UserId)
2165            SELECT u.UserId
2166            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
2167            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
2168            ORDER BY m.LoweredEmail
2169    ELSE
2170        INSERT INTO #PageIndexForUsers (UserId)
2171            SELECT u.UserId
2172            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
2173            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
2174            ORDER BY m.LoweredEmail
2175
2176    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
2177            m.CreateDate,
2178            m.LastLoginDate,
2179            u.LastActivityDate,
2180            m.LastPasswordChangedDate,
2181            u.UserId, m.IsLockedOut,
2182            m.LastLockoutDate
2183    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
2184    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
2185           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
2186    ORDER BY m.LoweredEmail
2187
2188    SELECT  @TotalRecords = COUNT(*)
2189    FROM    #PageIndexForUsers
2190    RETURN @TotalRecords
2191END
2192GO
2193
2194/*************************************************************/
2195/*************************************************************/
2196
2197IF (NOT EXISTS (SELECT name
2198                FROM sysobjects
2199                WHERE (name = N'vw_aspnet_MembershipUsers')
2200                  AND (type = 'V')))
2201BEGIN
2202  PRINT 'Creating the vw_aspnet_MembershipUsers view...'
2203  EXEC('
2204  CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
2205  AS SELECT [dbo].[aspnet_Membership].[UserId],
2206            [dbo].[aspnet_Membership].[PasswordFormat],
2207            [dbo].[aspnet_Membership].[MobilePIN],
2208            [dbo].[aspnet_Membership].[Email],
2209            [dbo].[aspnet_Membership].[LoweredEmail],
2210            [dbo].[aspnet_Membership].[PasswordQuestion],
2211            [dbo].[aspnet_Membership].[PasswordAnswer],
2212            [dbo].[aspnet_Membership].[IsApproved],
2213            [dbo].[aspnet_Membership].[IsLockedOut],
2214            [dbo].[aspnet_Membership].[CreateDate],
2215            [dbo].[aspnet_Membership].[LastLoginDate],
2216            [dbo].[aspnet_Membership].[LastPasswordChangedDate],
2217            [dbo].[aspnet_Membership].[LastLockoutDate],
2218            [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
2219            [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
2220            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
2221            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
2222            [dbo].[aspnet_Membership].[Comment],
2223            [dbo].[aspnet_Users].[ApplicationId],
2224            [dbo].[aspnet_Users].[UserName],
2225            [dbo].[aspnet_Users].[MobileAlias],
2226            [dbo].[aspnet_Users].[IsAnonymous],
2227            [dbo].[aspnet_Users].[LastActivityDate]
2228  FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
2229      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]
2230  ')
2231END
2232GO
2233
2234/*************************************************************/
2235/*************************************************************/
2236
2237--
2238--Create Membership schema version
2239--
2240DECLARE @command nvarchar(4000)
2241SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
2242EXECUTE (@command)
2243GO
2244
2245EXEC [dbo].aspnet_RegisterSchemaVersion N'Membership', N'1', 1, 1
2246GO
2247
2248/*************************************************************/
2249/*************************************************************/
2250
2251--
2252--Create Membership roles
2253--
2254
2255IF ( NOT EXISTS ( SELECT name
2256                  FROM sysusers
2257                  WHERE issqlrole = 1
2258                  AND name = N'aspnet_Membership_FullAccess'  ) )
2259EXEC sp_addrole N'aspnet_Membership_FullAccess'
2260
2261IF ( NOT EXISTS ( SELECT name
2262                  FROM sysusers
2263                  WHERE issqlrole = 1
2264                  AND name = N'aspnet_Membership_BasicAccess'  ) )
2265EXEC sp_addrole N'aspnet_Membership_BasicAccess'
2266
2267IF ( NOT EXISTS ( SELECT name
2268                  FROM sysusers
2269                  WHERE issqlrole = 1
2270                  AND name = N'aspnet_Membership_ReportingAccess'  ) )
2271EXEC sp_addrole N'aspnet_Membership_ReportingAccess'
2272GO
2273
2274EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'aspnet_Membership_FullAccess'
2275EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'aspnet_Membership_FullAccess'
2276GO
2277
2278--
2279--Stored Procedure rights for BasicAcess
2280--
2281GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_BasicAccess
2282GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_BasicAccess
2283GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_BasicAccess
2284GRANT EXECUTE ON dbo.aspnet_Membership_GetPassword TO aspnet_Membership_BasicAccess
2285GRANT EXECUTE ON dbo.aspnet_Membership_GetPasswordWithFormat TO aspnet_Membership_BasicAccess
2286GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUserInfo TO aspnet_Membership_BasicAccess
2287GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_BasicAccess
2288GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_BasicAccess
2289GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_BasicAccess
2290GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_BasicAccess
2291
2292--
2293--Stored Procedure rights for ReportingAccess
2294--
2295GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_ReportingAccess
2296GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_ReportingAccess
2297GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_ReportingAccess
2298GRANT EXECUTE ON dbo.aspnet_Membership_GetAllUsers TO aspnet_Membership_ReportingAccess
2299GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_ReportingAccess
2300GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByName TO aspnet_Membership_ReportingAccess
2301GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByEmail TO aspnet_Membership_ReportingAccess
2302GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_ReportingAccess
2303GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_ReportingAccess
2304GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_ReportingAccess
2305
2306--
2307--Additional stored procedure rights for FullAccess
2308--
2309GRANT EXECUTE ON dbo.aspnet_Users_DeleteUser TO aspnet_Membership_FullAccess
2310
2311GRANT EXECUTE ON dbo.aspnet_Membership_CreateUser TO aspnet_Membership_FullAccess
2312GRANT EXECUTE ON dbo.aspnet_Membership_SetPassword TO aspnet_Membership_FullAccess
2313GRANT EXECUTE ON dbo.aspnet_Membership_ResetPassword TO aspnet_Membership_FullAccess
2314GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUser TO aspnet_Membership_FullAccess
2315GRANT EXECUTE ON dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer TO aspnet_Membership_FullAccess
2316GRANT EXECUTE ON dbo.aspnet_Membership_UnlockUser TO aspnet_Membership_FullAccess
2317
2318--
2319--View rights
2320--
2321GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Membership_ReportingAccess
2322GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Membership_ReportingAccess
2323
2324GRANT SELECT ON dbo.vw_aspnet_MembershipUsers TO aspnet_Membership_ReportingAccess
2325
2326/*************************************************************/
2327/*************************************************************/
2328/*************************************************************/
2329/*************************************************************/
2330
2331DECLARE @command nvarchar(4000)
2332SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
2333EXECUTE (@command)
2334GO
2335
2336PRINT '--------------------------------------------'
2337PRINT 'Completed execution of InstallMembership.SQL'
2338PRINT '--------------------------------------------'
2339/**********************************************************************/
2340/* InstallProfile.SQL                                         */
2341/*                                                                    */
2342/* Installs the tables, triggers and stored procedures necessary for  */
2343/* supporting the aspnet feature of ASP.Net                           */
2344/*                                                                    */
2345/* InstallCommon.sql must be run before running this file.            */
2346/*
2347** Copyright Microsoft, Inc. 2002
2348** All Rights Reserved.
2349*/
2350/**********************************************************************/
2351
2352PRINT '------------------------------------------------'
2353PRINT 'Starting execution of InstallProfile.SQL'
2354PRINT '------------------------------------------------'
2355GO
2356
2357SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
2358SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
2359GO
2360SET ANSI_PADDING ON
2361GO
2362SET ANSI_NULL_DFLT_ON ON
2363GO
2364
2365/*************************************************************/
2366/*************************************************************/
2367/*************************************************************/
2368/*************************************************************/
2369/*************************************************************/
2370
2371DECLARE @dbname nvarchar(128)
2372
2373SET @dbname = N'aspnetdb'
2374
2375IF (NOT EXISTS (SELECT name
2376                FROM master.dbo.sysdatabases
2377                WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
2378BEGIN
2379  RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
2380END
2381GO
2382
2383USE [aspnetdb]
2384GO
2385
2386IF (NOT EXISTS (SELECT name
2387                FROM sysobjects
2388                WHERE (name = N'aspnet_Applications')
2389                  AND (type = 'U')))
2390BEGIN
2391  RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
2392END
2393
2394IF (NOT EXISTS (SELECT name
2395                FROM sysobjects
2396                WHERE (name = N'aspnet_Users')
2397                  AND (type = 'U')))
2398BEGIN
2399  RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
2400END
2401
2402IF (NOT EXISTS (SELECT name
2403              FROM sysobjects
2404             WHERE (name = N'aspnet_Applications_CreateApplication')
2405               AND (type = 'P')))
2406BEGIN
2407  RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
2408END
2409
2410IF (NOT EXISTS (SELECT name
2411              FROM sysobjects
2412             WHERE (name = N'aspnet_Users_CreateUser')
2413               AND (type = 'P')))
2414BEGIN
2415  RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
2416END
2417
2418IF (NOT EXISTS (SELECT name
2419              FROM sysobjects
2420             WHERE (name = N'aspnet_Users_DeleteUser')
2421               AND (type = 'P')))
2422BEGIN
2423  RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
2424END
2425
2426/*************************************************************/
2427/*************************************************************/
2428/*************************************************************/
2429/*************************************************************/
2430/*************************************************************/
2431
2432IF (NOT EXISTS (SELECT name
2433                FROM sysobjects
2434                WHERE (name = N'aspnet_Profile')
2435                  AND (type = 'U')))
2436BEGIN
2437  PRINT 'Creating the aspnet_Profile table...'
2438  CREATE TABLE dbo.aspnet_Profile (
2439        UserId                   uniqueidentifier   PRIMARY KEY FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),
2440        PropertyNames            ntext NOT NULL,
2441        PropertyValuesString     ntext NOT NULL,
2442        PropertyValuesBinary     image NOT NULL,
2443        LastUpdatedDate          datetime NOT NULL)
2444END
2445
2446/*************************************************************/
2447/*************************************************************/
2448/*************************************************************/
2449/*************************************************************/
2450
2451IF (EXISTS (SELECT name
2452              FROM sysobjects
2453             WHERE (name = N'aspnet_Profile_GetProperties')
2454               AND (type = 'P')))
2455DROP PROCEDURE dbo.aspnet_Profile_GetProperties
2456GO
2457
2458CREATE PROCEDURE dbo.aspnet_Profile_GetProperties
2459    @ApplicationName      nvarchar(256),
2460    @UserName             nvarchar(256),
2461    @CurrentTimeUtc       datetime
2462AS
2463BEGIN
2464    DECLARE @ApplicationId uniqueidentifier
2465    SELECT  @ApplicationId = NULL
2466    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2467    IF (@ApplicationId IS NULL)
2468        RETURN
2469
2470    DECLARE @UserId uniqueidentifier
2471    SELECT  @UserId = NULL
2472
2473    SELECT @UserId = UserId
2474    FROM   dbo.aspnet_Users
2475    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
2476
2477    IF (@UserId IS NULL)
2478        RETURN
2479    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
2480    FROM         dbo.aspnet_Profile
2481    WHERE        UserId = @UserId
2482
2483    IF (@@ROWCOUNT > 0)
2484    BEGIN
2485        UPDATE dbo.aspnet_Users
2486        SET    LastActivityDate=@CurrentTimeUtc
2487        WHERE  UserId = @UserId
2488    END
2489END
2490GO
2491
2492/*************************************************************/
2493/*************************************************************/
2494
2495IF (EXISTS (SELECT name
2496              FROM sysobjects
2497             WHERE (name = N'aspnet_Profile_SetProperties')
2498               AND (type = 'P')))
2499DROP PROCEDURE dbo.aspnet_Profile_SetProperties
2500GO
2501
2502CREATE PROCEDURE dbo.aspnet_Profile_SetProperties
2503    @ApplicationName        nvarchar(256),
2504    @PropertyNames          ntext,
2505    @PropertyValuesString   ntext,
2506    @PropertyValuesBinary   image,
2507    @UserName               nvarchar(256),
2508    @IsUserAnonymous        bit,
2509    @CurrentTimeUtc         datetime
2510AS
2511BEGIN
2512    DECLARE @ApplicationId uniqueidentifier
2513    SELECT  @ApplicationId = NULL
2514
2515    DECLARE @ErrorCode     int
2516    SET @ErrorCode = 0
2517
2518    DECLARE @TranStarted   bit
2519    SET @TranStarted = 0
2520
2521    IF( @@TRANCOUNT = 0 )
2522    BEGIN
2523       BEGIN TRANSACTION
2524       SET @TranStarted = 1
2525    END
2526    ELSE
2527    	SET @TranStarted = 0
2528
2529    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
2530
2531    IF( @@ERROR <> 0 )
2532    BEGIN
2533        SET @ErrorCode = -1
2534        GOTO Cleanup
2535    END
2536
2537    DECLARE @UserId uniqueidentifier
2538    DECLARE @LastActivityDate datetime
2539    SELECT  @UserId = NULL
2540    SELECT  @LastActivityDate = @CurrentTimeUtc
2541
2542    SELECT @UserId = UserId
2543    FROM   dbo.aspnet_Users
2544    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
2545    IF (@UserId IS NULL)
2546        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT
2547
2548    IF( @@ERROR <> 0 )
2549    BEGIN
2550        SET @ErrorCode = -1
2551        GOTO Cleanup
2552    END
2553
2554    UPDATE dbo.aspnet_Users
2555    SET    LastActivityDate=@CurrentTimeUtc
2556    WHERE  UserId = @UserId
2557
2558    IF( @@ERROR <> 0 )
2559    BEGIN
2560        SET @ErrorCode = -1
2561        GOTO Cleanup
2562    END
2563
2564    IF (EXISTS( SELECT *
2565               FROM   dbo.aspnet_Profile
2566               WHERE  UserId = @UserId))
2567        UPDATE dbo.aspnet_Profile
2568        SET    PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
2569               PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
2570        WHERE  UserId = @UserId
2571    ELSE
2572        INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
2573             VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)
2574
2575    IF( @@ERROR <> 0 )
2576    BEGIN
2577        SET @ErrorCode = -1
2578        GOTO Cleanup
2579    END
2580
2581    IF( @TranStarted = 1 )
2582    BEGIN
2583    	SET @TranStarted = 0
2584    	COMMIT TRANSACTION
2585    END
2586
2587    RETURN 0
2588
2589Cleanup:
2590
2591    IF( @TranStarted = 1 )
2592    BEGIN
2593        SET @TranStarted = 0
2594    	ROLLBACK TRANSACTION
2595    END
2596
2597    RETURN @ErrorCode
2598
2599END
2600GO
2601/*************************************************************/
2602/*************************************************************/
2603IF (EXISTS (SELECT name
2604              FROM sysobjects
2605             WHERE (name = N'aspnet_Profile_DeleteProfiles')
2606               AND (type = 'P')))
2607DROP PROCEDURE dbo.aspnet_Profile_DeleteProfiles
2608GO
2609
2610CREATE PROCEDURE dbo.aspnet_Profile_DeleteProfiles
2611    @ApplicationName        nvarchar(256),
2612    @UserNames              nvarchar(4000)
2613AS
2614BEGIN
2615    DECLARE @UserName     nvarchar(256)
2616    DECLARE @CurrentPos   int
2617    DECLARE @NextPos      int
2618    DECLARE @NumDeleted   int
2619    DECLARE @DeletedUser  int
2620    DECLARE @TranStarted  bit
2621    DECLARE @ErrorCode    int
2622
2623    SET @ErrorCode = 0
2624    SET @CurrentPos = 1
2625    SET @NumDeleted = 0
2626    SET @TranStarted = 0
2627
2628    IF( @@TRANCOUNT = 0 )
2629    BEGIN
2630        BEGIN TRANSACTION
2631        SET @TranStarted = 1
2632    END
2633    ELSE
2634    	SET @TranStarted = 0
2635
2636    WHILE (@CurrentPos <= LEN(@UserNames))
2637    BEGIN
2638        SELECT @NextPos = CHARINDEX(N',', @UserNames,  @CurrentPos)
2639        IF (@NextPos = 0 OR @NextPos IS NULL)
2640            SELECT @NextPos = LEN(@UserNames) + 1
2641
2642        SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
2643        SELECT @CurrentPos = @NextPos+1
2644
2645        IF (LEN(@UserName) > 0)
2646        BEGIN
2647            SELECT @DeletedUser = 0
2648            EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
2649            IF( @@ERROR <> 0 )
2650            BEGIN
2651                SET @ErrorCode = -1
2652                GOTO Cleanup
2653            END
2654            IF (@DeletedUser <> 0)
2655                SELECT @NumDeleted = @NumDeleted + 1
2656        END
2657    END
2658    SELECT @NumDeleted
2659    IF (@TranStarted = 1)
2660    BEGIN
2661    	SET @TranStarted = 0
2662    	COMMIT TRANSACTION
2663    END
2664    SET @TranStarted = 0
2665
2666    RETURN 0
2667
2668Cleanup:
2669    IF (@TranStarted = 1 )
2670    BEGIN
2671        SET @TranStarted = 0
2672    	ROLLBACK TRANSACTION
2673    END
2674    RETURN @ErrorCode
2675END
2676GO
2677
2678/*************************************************************/
2679/*************************************************************/
2680IF (EXISTS (SELECT name
2681              FROM sysobjects
2682             WHERE (name = N'aspnet_Profile_DeleteInactiveProfiles')
2683               AND (type = 'P')))
2684DROP PROCEDURE dbo.aspnet_Profile_DeleteInactiveProfiles
2685GO
2686
2687CREATE PROCEDURE dbo.aspnet_Profile_DeleteInactiveProfiles
2688    @ApplicationName        nvarchar(256),
2689    @ProfileAuthOptions     int,
2690    @InactiveSinceDate      datetime
2691AS
2692BEGIN
2693    DECLARE @ApplicationId uniqueidentifier
2694    SELECT  @ApplicationId = NULL
2695    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2696    IF (@ApplicationId IS NULL)
2697    BEGIN
2698        SELECT  0
2699        RETURN
2700    END
2701
2702    DELETE
2703    FROM    dbo.aspnet_Profile
2704    WHERE   UserId IN
2705            (   SELECT  UserId
2706                FROM    dbo.aspnet_Users u
2707                WHERE   ApplicationId = @ApplicationId
2708                        AND (LastActivityDate <= @InactiveSinceDate)
2709                        AND (
2710                                (@ProfileAuthOptions = 2)
2711                             OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
2712                             OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
2713                            )
2714            )
2715
2716    SELECT  @@ROWCOUNT
2717END
2718GO
2719
2720/*************************************************************/
2721/*************************************************************/
2722 IF (EXISTS (SELECT name
2723              FROM sysobjects
2724             WHERE (name = N'aspnet_Profile_GetNumberOfInactiveProfiles')
2725               AND (type = 'P')))
2726DROP PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
2727GO
2728
2729CREATE PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
2730    @ApplicationName        nvarchar(256),
2731    @ProfileAuthOptions     int,
2732    @InactiveSinceDate      datetime
2733AS
2734BEGIN
2735    DECLARE @ApplicationId uniqueidentifier
2736    SELECT  @ApplicationId = NULL
2737    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2738    IF (@ApplicationId IS NULL)
2739    BEGIN
2740        SELECT 0
2741        RETURN
2742    END
2743
2744    SELECT  COUNT(*)
2745    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
2746    WHERE   ApplicationId = @ApplicationId
2747        AND u.UserId = p.UserId
2748        AND (LastActivityDate <= @InactiveSinceDate)
2749        AND (
2750                (@ProfileAuthOptions = 2)
2751                OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
2752                OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
2753            )
2754END
2755GO
2756
2757
2758/*************************************************************/
2759/*************************************************************/
2760IF (EXISTS (SELECT name
2761              FROM sysobjects
2762             WHERE (name = N'aspnet_Profile_GetProfiles')
2763               AND (type = 'P')))
2764DROP PROCEDURE dbo.aspnet_Profile_GetProfiles
2765GO
2766
2767CREATE PROCEDURE dbo.aspnet_Profile_GetProfiles
2768    @ApplicationName        nvarchar(256),
2769    @ProfileAuthOptions     int,
2770    @PageIndex              int,
2771    @PageSize               int,
2772    @UserNameToMatch        nvarchar(256) = NULL,
2773    @InactiveSinceDate      datetime      = NULL
2774AS
2775BEGIN
2776    DECLARE @ApplicationId uniqueidentifier
2777    SELECT  @ApplicationId = NULL
2778    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
2779    IF (@ApplicationId IS NULL)
2780        RETURN
2781
2782    -- Set the page bounds
2783    DECLARE @PageLowerBound int
2784    DECLARE @PageUpperBound int
2785    DECLARE @TotalRecords   int
2786    SET @PageLowerBound = @PageSize * @PageIndex
2787    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
2788
2789    -- Create a temp table TO store the select results
2790    CREATE TABLE #PageIndexForUsers
2791    (
2792        IndexId int IDENTITY (0, 1) NOT NULL,
2793        UserId uniqueidentifier
2794    )
2795
2796    -- Insert into our temp table
2797    INSERT INTO #PageIndexForUsers (UserId)
2798        SELECT  u.UserId
2799        FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
2800        WHERE   ApplicationId = @ApplicationId
2801            AND u.UserId = p.UserId
2802            AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
2803            AND (     (@ProfileAuthOptions = 2)
2804                   OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
2805                   OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
2806                 )
2807            AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
2808        ORDER BY UserName
2809
2810    SELECT  u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
2811            DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
2812    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
2813    WHERE   u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
2814
2815    SELECT COUNT(*)
2816    FROM   #PageIndexForUsers
2817
2818    DROP TABLE #PageIndexForUsers
2819END
2820GO
2821
2822/*************************************************************/
2823/*************************************************************/
2824IF (NOT EXISTS (SELECT name
2825                FROM sysobjects
2826                WHERE (name = N'vw_aspnet_Profiles')
2827                  AND (type = 'V')))
2828BEGIN
2829  PRINT 'Creating the vw_aspnet_Profiles view...'
2830  EXEC(N'
2831  CREATE VIEW [dbo].[vw_aspnet_Profiles]
2832  AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
2833      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
2834                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
2835                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
2836  FROM [dbo].[aspnet_Profile]
2837  ')
2838END
2839GO
2840
2841/*************************************************************/
2842/*************************************************************/
2843
2844--
2845--Create Profile schema version
2846--
2847
2848DECLARE @command nvarchar(4000)
2849SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
2850EXECUTE (@command)
2851GO
2852
2853EXEC [dbo].aspnet_RegisterSchemaVersion N'Profile', N'1', 1, 1
2854GO
2855
2856/*************************************************************/
2857/*************************************************************/
2858
2859--
2860--Create Profile roles
2861--
2862
2863IF ( NOT EXISTS ( SELECT name
2864                  FROM sysusers
2865                  WHERE issqlrole = 1
2866                  AND name = N'aspnet_Profile_FullAccess' ) )
2867EXEC sp_addrole N'aspnet_Profile_FullAccess'
2868
2869IF ( NOT EXISTS ( SELECT name
2870                  FROM sysusers
2871                  WHERE issqlrole = 1
2872                  AND name = N'aspnet_Profile_BasicAccess' ) )
2873EXEC sp_addrole N'aspnet_Profile_BasicAccess'
2874
2875IF ( NOT EXISTS ( SELECT name
2876                  FROM sysusers
2877                  WHERE issqlrole = 1
2878                  AND name = N'aspnet_Profile_ReportingAccess' ) )
2879EXEC sp_addrole N'aspnet_Profile_ReportingAccess'
2880GO
2881
2882EXEC sp_addrolemember N'aspnet_Profile_BasicAccess', N'aspnet_Profile_FullAccess'
2883EXEC sp_addrolemember N'aspnet_Profile_ReportingAccess', N'aspnet_Profile_FullAccess'
2884GO
2885
2886--
2887--Stored Procedure rights for BasicAccess
2888--
2889GRANT EXECUTE ON dbo.aspnet_Profile_GetProperties TO aspnet_Profile_BasicAccess
2890GRANT EXECUTE ON dbo.aspnet_Profile_SetProperties TO aspnet_Profile_BasicAccess
2891GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Profile_BasicAccess
2892GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Profile_BasicAccess
2893GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Profile_BasicAccess
2894
2895--
2896--Stored Procedure rights for ReportingAccess
2897--
2898GRANT EXECUTE ON dbo.aspnet_Profile_GetNumberOfInactiveProfiles TO aspnet_Profile_ReportingAccess
2899GRANT EXECUTE ON dbo.aspnet_Profile_GetProfiles TO aspnet_Profile_ReportingAccess
2900GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Profile_ReportingAccess
2901GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Profile_ReportingAccess
2902GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Profile_ReportingAccess
2903
2904--
2905--Additional stored procedure rights for FullAccess
2906--
2907GRANT EXECUTE ON dbo.aspnet_Profile_DeleteProfiles TO aspnet_Profile_FullAccess
2908GRANT EXECUTE ON dbo.aspnet_Profile_DeleteInactiveProfiles TO aspnet_Profile_FullAccess
2909
2910--
2911--View rights
2912--
2913GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Profile_ReportingAccess
2914GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Profile_ReportingAccess
2915
2916GRANT SELECT ON dbo.vw_aspnet_Profiles TO aspnet_Profile_ReportingAccess
2917GO
2918
2919-------------------------------------------------------------------------
2920--- Version specific install
2921-------------------------------------------------------------------------
2922
2923DECLARE @ver int
2924DECLARE @version nchar(100)
2925DECLARE @dot int
2926DECLARE @hyphen int
2927DECLARE @SqlToExec nchar(400)
2928
2929SELECT @ver = 8
2930SELECT @version = @@Version
2931SELECT @hyphen  = CHARINDEX(N' - ', @version)
2932IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
2933BEGIN
2934    SELECT @hyphen = @hyphen + 3
2935    SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
2936    IF (NOT(@dot IS NULL) AND @dot > @hyphen)
2937    BEGIN
2938        SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
2939        SELECT @ver     = CONVERT(int, @version)
2940    END
2941END
2942
2943IF (@ver >= 8)
2944BEGIN
2945    EXEC sp_tableoption N'aspnet_Profile', 'text in row', 6000
2946END
2947GO
2948/*************************************************************/
2949/*************************************************************/
2950/*************************************************************/
2951/*************************************************************/
2952
2953
2954DECLARE @command nvarchar(4000)
2955SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
2956EXECUTE (@command)
2957GO
2958
2959PRINT '-------------------------------------------------'
2960PRINT 'Completed execution of InstallProfile.SQL'
2961PRINT '-------------------------------------------------'
2962/**********************************************************************/
2963/* InstallRoles.SQL                                                   */
2964/*                                                                    */
2965/* Installs the tables, triggers and stored procedures necessary for  */
2966/* supporting the aspnet feature of ASP.Net                           */
2967/*                                                                    */
2968/* InstallCommon.sql must be run before running this file.            */
2969/*
2970** Copyright Microsoft, Inc. 2002
2971** All Rights Reserved.
2972*/
2973/**********************************************************************/
2974
2975PRINT '--------------------------------------'
2976PRINT 'Starting execution of InstallRoles.SQL'
2977PRINT '--------------------------------------'
2978GO
2979
2980SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
2981SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
2982GO
2983SET ANSI_PADDING ON
2984GO
2985SET ANSI_NULL_DFLT_ON ON
2986GO
2987
2988/*************************************************************/
2989/*************************************************************/
2990/*************************************************************/
2991/*************************************************************/
2992/*************************************************************/
2993
2994DECLARE @dbname nvarchar(128)
2995
2996SET @dbname = N'aspnetdb'
2997
2998IF (NOT EXISTS (SELECT name
2999                FROM master.dbo.sysdatabases
3000                WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
3001BEGIN
3002  RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
3003END
3004GO
3005
3006USE [aspnetdb]
3007GO
3008
3009IF (NOT EXISTS (SELECT name
3010                FROM sysobjects
3011                WHERE (name = N'aspnet_Applications')
3012                  AND (type = 'U')))
3013BEGIN
3014  RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
3015END
3016
3017IF (NOT EXISTS (SELECT name
3018                FROM sysobjects
3019                WHERE (name = N'aspnet_Users')
3020                  AND (type = 'U')))
3021BEGIN
3022  RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
3023END
3024
3025IF (NOT EXISTS (SELECT name
3026              FROM sysobjects
3027             WHERE (name = N'aspnet_Applications_CreateApplication')
3028               AND (type = 'P')))
3029BEGIN
3030  RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
3031END
3032
3033IF (NOT EXISTS (SELECT name
3034              FROM sysobjects
3035             WHERE (name = N'aspnet_Users_CreateUser')
3036               AND (type = 'P')))
3037BEGIN
3038  RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
3039END
3040
3041IF (NOT EXISTS (SELECT name
3042              FROM sysobjects
3043             WHERE (name = N'aspnet_Users_DeleteUser')
3044               AND (type = 'P')))
3045BEGIN
3046  RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
3047END
3048
3049/*************************************************************/
3050/*************************************************************/
3051
3052IF (NOT EXISTS (SELECT name
3053                FROM sysobjects
3054                WHERE (name = N'aspnet_Roles')
3055                  AND (type = 'U')))
3056BEGIN
3057  PRINT 'Creating the aspnet_Roles table...'
3058  CREATE TABLE dbo.aspnet_Roles (
3059        ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
3060        RoleId           uniqueidentifier    PRIMARY KEY  NONCLUSTERED DEFAULT NEWID(),
3061        RoleName         nvarchar(256)       NOT NULL,
3062        LoweredRoleName  nvarchar(256)       NOT NULL,
3063        Description      nvarchar(256)       )
3064 CREATE UNIQUE  CLUSTERED  INDEX aspnet_Roles_index1 ON  dbo.aspnet_Roles(ApplicationId, LoweredRoleName)
3065END
3066GO
3067
3068/*************************************************************/
3069/*************************************************************/
3070
3071IF (NOT EXISTS (SELECT name
3072                FROM sysobjects
3073                WHERE (name = N'aspnet_UsersInRoles')
3074                  AND (type = 'U')))
3075BEGIN
3076  PRINT 'Creating the aspnet_UsersInRoles table...'
3077  CREATE TABLE dbo.aspnet_UsersInRoles (
3078        UserId     uniqueidentifier NOT NULL PRIMARY KEY(UserId, RoleId) FOREIGN KEY REFERENCES dbo.aspnet_Users (UserId),
3079        RoleId     uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Roles (RoleId))
3080
3081  CREATE INDEX aspnet_UsersInRoles_index ON  dbo.aspnet_UsersInRoles(RoleId)
3082END
3083
3084
3085/*************************************************************/
3086/*************************************************************/
3087/*************************************************************/
3088/*************************************************************/
3089
3090IF (EXISTS (SELECT name
3091              FROM sysobjects
3092             WHERE (name = N'aspnet_UsersInRoles_IsUserInRole')
3093               AND (type = 'P')))
3094DROP PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
3095GO
3096
3097CREATE PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
3098    @ApplicationName  nvarchar(256),
3099    @UserName         nvarchar(256),
3100    @RoleName         nvarchar(256)
3101AS
3102BEGIN
3103    DECLARE @ApplicationId uniqueidentifier
3104    SELECT  @ApplicationId = NULL
3105    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3106    IF (@ApplicationId IS NULL)
3107        RETURN(2)
3108    DECLARE @UserId uniqueidentifier
3109    SELECT  @UserId = NULL
3110    DECLARE @RoleId uniqueidentifier
3111    SELECT  @RoleId = NULL
3112
3113    SELECT  @UserId = UserId
3114    FROM    dbo.aspnet_Users
3115    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
3116
3117    IF (@UserId IS NULL)
3118        RETURN(2)
3119
3120    SELECT  @RoleId = RoleId
3121    FROM    dbo.aspnet_Roles
3122    WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
3123
3124    IF (@RoleId IS NULL)
3125        RETURN(3)
3126
3127    IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
3128        RETURN(1)
3129    ELSE
3130        RETURN(0)
3131END
3132GO
3133
3134/*************************************************************/
3135/*************************************************************/
3136
3137IF (EXISTS (SELECT name
3138              FROM sysobjects
3139             WHERE (name = N'aspnet_UsersInRoles_GetRolesForUser')
3140               AND (type = 'P')))
3141DROP PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
3142GO
3143
3144CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
3145    @ApplicationName  nvarchar(256),
3146    @UserName         nvarchar(256)
3147AS
3148BEGIN
3149    DECLARE @ApplicationId uniqueidentifier
3150    SELECT  @ApplicationId = NULL
3151    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3152    IF (@ApplicationId IS NULL)
3153        RETURN(1)
3154    DECLARE @UserId uniqueidentifier
3155    SELECT  @UserId = NULL
3156
3157    SELECT  @UserId = UserId
3158    FROM    dbo.aspnet_Users
3159    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
3160
3161    IF (@UserId IS NULL)
3162        RETURN(1)
3163
3164    SELECT r.RoleName
3165    FROM   dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
3166    WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
3167    ORDER BY r.RoleName
3168    RETURN (0)
3169END
3170GO
3171
3172/*************************************************************/
3173/*************************************************************/
3174IF (EXISTS (SELECT name
3175              FROM sysobjects
3176             WHERE (name = N'aspnet_Roles_CreateRole')
3177               AND (type = 'P')))
3178DROP PROCEDURE dbo.aspnet_Roles_CreateRole
3179GO
3180CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
3181    @ApplicationName  nvarchar(256),
3182    @RoleName         nvarchar(256)
3183AS
3184BEGIN
3185    DECLARE @ApplicationId uniqueidentifier
3186    SELECT  @ApplicationId = NULL
3187
3188    DECLARE @ErrorCode     int
3189    SET @ErrorCode = 0
3190
3191    DECLARE @TranStarted   bit
3192    SET @TranStarted = 0
3193
3194    IF( @@TRANCOUNT = 0 )
3195    BEGIN
3196        BEGIN TRANSACTION
3197        SET @TranStarted = 1
3198    END
3199    ELSE
3200        SET @TranStarted = 0
3201
3202    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
3203
3204    IF( @@ERROR <> 0 )
3205    BEGIN
3206        SET @ErrorCode = -1
3207        GOTO Cleanup
3208    END
3209
3210    IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
3211    BEGIN
3212        SET @ErrorCode = 1
3213        GOTO Cleanup
3214    END
3215
3216    INSERT INTO dbo.aspnet_Roles
3217                (ApplicationId, RoleName, LoweredRoleName)
3218         VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
3219
3220    IF( @@ERROR <> 0 )
3221    BEGIN
3222        SET @ErrorCode = -1
3223        GOTO Cleanup
3224    END
3225
3226    IF( @TranStarted = 1 )
3227    BEGIN
3228        SET @TranStarted = 0
3229        COMMIT TRANSACTION
3230    END
3231
3232    RETURN(0)
3233
3234Cleanup:
3235
3236    IF( @TranStarted = 1 )
3237    BEGIN
3238        SET @TranStarted = 0
3239        ROLLBACK TRANSACTION
3240    END
3241
3242    RETURN @ErrorCode
3243
3244END
3245GO
3246
3247/*************************************************************/
3248/*************************************************************/
3249
3250IF (EXISTS (SELECT name
3251              FROM sysobjects
3252             WHERE (name = N'aspnet_Roles_DeleteRole')
3253               AND (type = 'P')))
3254DROP PROCEDURE dbo.aspnet_Roles_DeleteRole
3255GO
3256
3257CREATE PROCEDURE dbo.aspnet_Roles_DeleteRole
3258    @ApplicationName            nvarchar(256),
3259    @RoleName                   nvarchar(256),
3260    @DeleteOnlyIfRoleIsEmpty    bit
3261AS
3262BEGIN
3263    DECLARE @ApplicationId uniqueidentifier
3264    SELECT  @ApplicationId = NULL
3265    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3266    IF (@ApplicationId IS NULL)
3267        RETURN(1)
3268
3269    DECLARE @ErrorCode     int
3270    SET @ErrorCode = 0
3271
3272    DECLARE @TranStarted   bit
3273    SET @TranStarted = 0
3274
3275    IF( @@TRANCOUNT = 0 )
3276    BEGIN
3277        BEGIN TRANSACTION
3278        SET @TranStarted = 1
3279    END
3280    ELSE
3281        SET @TranStarted = 0
3282
3283    DECLARE @RoleId   uniqueidentifier
3284    SELECT  @RoleId = NULL
3285    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
3286
3287    IF (@RoleId IS NULL)
3288    BEGIN
3289        SELECT @ErrorCode = 1
3290        GOTO Cleanup
3291    END
3292    IF (@DeleteOnlyIfRoleIsEmpty <> 0)
3293    BEGIN
3294        IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId))
3295        BEGIN
3296            SELECT @ErrorCode = 2
3297            GOTO Cleanup
3298        END
3299    END
3300
3301
3302    DELETE FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId
3303
3304    IF( @@ERROR <> 0 )
3305    BEGIN
3306        SET @ErrorCode = -1
3307        GOTO Cleanup
3308    END
3309
3310    DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId  AND ApplicationId = @ApplicationId
3311
3312    IF( @@ERROR <> 0 )
3313    BEGIN
3314        SET @ErrorCode = -1
3315        GOTO Cleanup
3316    END
3317
3318    IF( @TranStarted = 1 )
3319    BEGIN
3320        SET @TranStarted = 0
3321        COMMIT TRANSACTION
3322    END
3323
3324    RETURN(0)
3325
3326Cleanup:
3327
3328    IF( @TranStarted = 1 )
3329    BEGIN
3330        SET @TranStarted = 0
3331        ROLLBACK TRANSACTION
3332    END
3333
3334    RETURN @ErrorCode
3335END
3336GO
3337
3338/*************************************************************/
3339/*************************************************************/
3340
3341IF (EXISTS (SELECT name
3342              FROM sysobjects
3343             WHERE (name = N'aspnet_Roles_RoleExists')
3344               AND (type = 'P')))
3345DROP PROCEDURE dbo.aspnet_Roles_RoleExists
3346GO
3347
3348CREATE PROCEDURE dbo.aspnet_Roles_RoleExists
3349    @ApplicationName  nvarchar(256),
3350    @RoleName         nvarchar(256)
3351AS
3352BEGIN
3353    DECLARE @ApplicationId uniqueidentifier
3354    SELECT  @ApplicationId = NULL
3355    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3356    IF (@ApplicationId IS NULL)
3357        RETURN(0)
3358    IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
3359        RETURN(1)
3360    ELSE
3361        RETURN(0)
3362END
3363GO
3364
3365/*************************************************************/
3366/*************************************************************/
3367
3368IF (EXISTS (SELECT name
3369              FROM sysobjects
3370             WHERE (name = N'aspnet_UsersInRoles_AddUsersToRoles')
3371               AND (type = 'P')))
3372DROP PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
3373GO
3374IF (EXISTS (SELECT name
3375              FROM sysobjects
3376             WHERE (name = N'aspnet_UsersInRoles_RemoveUsersFromRoles')
3377               AND (type = 'P')))
3378DROP PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
3379GO
3380
3381DECLARE @ver            int
3382DECLARE @version        nchar(100)
3383DECLARE @dot            int
3384DECLARE @hyphen         int
3385DECLARE @SqlToExec      nchar(4000)
3386
3387SELECT @ver = 7
3388SELECT @version = @@Version
3389SELECT @hyphen  = CHARINDEX(N' - ', @version)
3390IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
3391BEGIN
3392    SELECT @hyphen = @hyphen + 3
3393    SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
3394    IF (NOT(@dot IS NULL) AND @dot > @hyphen)
3395    BEGIN
3396        SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
3397        SELECT @ver     = CONVERT(int, @version)
3398    END
3399END
3400
3401IF (@ver > 7)
3402SELECT @SqlToExec = N'
3403CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
3404	@ApplicationName  nvarchar(256),
3405	@UserNames		  nvarchar(4000),
3406	@RoleNames		  nvarchar(4000),
3407	@CurrentTimeUtc   datetime
3408AS
3409BEGIN
3410	DECLARE @AppId uniqueidentifier
3411	SELECT  @AppId = NULL
3412	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3413	IF (@AppId IS NULL)
3414		RETURN(2)
3415	DECLARE @TranStarted   bit
3416	SET @TranStarted = 0
3417
3418	IF( @@TRANCOUNT = 0 )
3419	BEGIN
3420		BEGIN TRANSACTION
3421		SET @TranStarted = 1
3422	END
3423
3424	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
3425	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
3426	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
3427	DECLARE @Num		int
3428	DECLARE @Pos		int
3429	DECLARE @NextPos	int
3430	DECLARE @Name		nvarchar(256)
3431
3432	SET @Num = 0
3433	SET @Pos = 1
3434	WHILE(@Pos <= LEN(@RoleNames))
3435	BEGIN
3436		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
3437		IF (@NextPos = 0 OR @NextPos IS NULL)
3438			SELECT @NextPos = LEN(@RoleNames) + 1
3439		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
3440		SELECT @Pos = @NextPos+1
3441
3442		INSERT INTO @tbNames VALUES (@Name)
3443		SET @Num = @Num + 1
3444	END
3445
3446	INSERT INTO @tbRoles
3447	  SELECT RoleId
3448	  FROM   dbo.aspnet_Roles ar, @tbNames t
3449	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
3450
3451	IF (@@ROWCOUNT <> @Num)
3452	BEGIN
3453		SELECT TOP 1 Name
3454		FROM   @tbNames
3455		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
3456		IF( @TranStarted = 1 )
3457			ROLLBACK TRANSACTION
3458		RETURN(2)
3459	END
3460
3461	DELETE FROM @tbNames WHERE 1=1
3462	SET @Num = 0
3463	SET @Pos = 1
3464
3465	WHILE(@Pos <= LEN(@UserNames))
3466	BEGIN
3467		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
3468		IF (@NextPos = 0 OR @NextPos IS NULL)
3469			SELECT @NextPos = LEN(@UserNames) + 1
3470		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
3471		SELECT @Pos = @NextPos+1
3472
3473		INSERT INTO @tbNames VALUES (@Name)
3474		SET @Num = @Num + 1
3475	END
3476
3477	INSERT INTO @tbUsers
3478	  SELECT UserId
3479	  FROM   dbo.aspnet_Users ar, @tbNames t
3480	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
3481
3482	IF (@@ROWCOUNT <> @Num)
3483	BEGIN
3484		DELETE FROM @tbNames
3485		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)
3486
3487		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
3488		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
3489		  FROM   @tbNames
3490
3491		INSERT INTO @tbUsers
3492		  SELECT  UserId
3493		  FROM	dbo.aspnet_Users au, @tbNames t
3494		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
3495	END
3496
3497	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
3498	BEGIN
3499		SELECT TOP 1 UserName, RoleName
3500		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
3501		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId
3502
3503		IF( @TranStarted = 1 )
3504			ROLLBACK TRANSACTION
3505		RETURN(3)
3506	END
3507
3508	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
3509	SELECT UserId, RoleId
3510	FROM @tbUsers, @tbRoles
3511
3512	IF( @TranStarted = 1 )
3513		COMMIT TRANSACTION
3514	RETURN(0)
3515END'
3516ELSE
3517SELECT @SqlToExec = N'
3518CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
3519	@ApplicationName	nvarchar(256),
3520	@UserNames			nvarchar(4000),
3521	@RoleNames			nvarchar(4000),
3522	@CurrentTimeUtc		datetime
3523AS
3524BEGIN
3525	DECLARE @AppId uniqueidentifier
3526	SELECT  @AppId = NULL
3527	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3528	IF (@AppId IS NULL)
3529		RETURN(2)
3530
3531	DECLARE @TranStarted   bit
3532	SET @TranStarted = 0
3533	IF( @@TRANCOUNT = 0 )
3534	BEGIN
3535		BEGIN TRANSACTION
3536		SET @TranStarted = 1
3537	END
3538
3539	DECLARE @RoleId		uniqueidentifier
3540	DECLARE @UserId		uniqueidentifier
3541	DECLARE @UserName	nvarchar(256)
3542	DECLARE @RoleName	nvarchar(256)
3543
3544	DECLARE @CurrentPosU	int
3545	DECLARE @NextPosU		int
3546	DECLARE @CurrentPosR	int
3547	DECLARE @NextPosR		int
3548
3549	SELECT  @CurrentPosU = 1
3550
3551	WHILE(@CurrentPosU <= LEN(@UserNames))
3552	BEGIN
3553		SELECT @NextPosU = CHARINDEX(N'','', @UserNames,  @CurrentPosU)
3554		IF (@NextPosU = 0 OR @NextPosU IS NULL)
3555			SELECT @NextPosU = LEN(@UserNames) + 1
3556
3557		SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
3558		SELECT @CurrentPosU = @NextPosU+1
3559
3560		SELECT @CurrentPosR = 1
3561		WHILE(@CurrentPosR <= LEN(@RoleNames))
3562		BEGIN
3563			SELECT @NextPosR = CHARINDEX(N'','', @RoleNames,  @CurrentPosR)
3564			IF (@NextPosR = 0 OR @NextPosR IS NULL)
3565				SELECT @NextPosR = LEN(@RoleNames) + 1
3566			SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
3567			SELECT @CurrentPosR = @NextPosR+1
3568			SELECT @RoleId = NULL
3569			SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
3570			IF (@RoleId IS NULL)
3571			BEGIN
3572				SELECT @RoleName
3573				IF( @TranStarted = 1 )
3574					ROLLBACK TRANSACTION
3575				RETURN(2)
3576			END
3577
3578			SELECT @UserId = NULL
3579			SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
3580			IF (@UserId IS NULL)
3581			BEGIN
3582				EXEC dbo.aspnet_Users_CreateUser @AppId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
3583			END
3584
3585			IF (EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId))
3586			BEGIN
3587				SELECT @UserName, @RoleName
3588				IF( @TranStarted = 1 )
3589					ROLLBACK TRANSACTION
3590				RETURN(3)
3591			END
3592			INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId) VALUES(@UserId, @RoleId)
3593		END
3594	END
3595	IF( @TranStarted = 1 )
3596		COMMIT TRANSACTION
3597	RETURN(0)
3598END'
3599
3600EXEC sp_executesql @SqlToExec
3601
3602IF (@ver > 7)
3603SELECT @SqlToExec = N'
3604CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
3605	@ApplicationName  nvarchar(256),
3606	@UserNames		  nvarchar(4000),
3607	@RoleNames		  nvarchar(4000)
3608AS
3609BEGIN
3610	DECLARE @AppId uniqueidentifier
3611	SELECT  @AppId = NULL
3612	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3613	IF (@AppId IS NULL)
3614		RETURN(2)
3615
3616
3617	DECLARE @TranStarted   bit
3618	SET @TranStarted = 0
3619
3620	IF( @@TRANCOUNT = 0 )
3621	BEGIN
3622		BEGIN TRANSACTION
3623		SET @TranStarted = 1
3624	END
3625
3626	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
3627	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
3628	DECLARE @tbUsers  table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
3629	DECLARE @Num	  int
3630	DECLARE @Pos	  int
3631	DECLARE @NextPos  int
3632	DECLARE @Name	  nvarchar(256)
3633	DECLARE @CountAll int
3634	DECLARE @CountU	  int
3635	DECLARE @CountR	  int
3636
3637
3638	SET @Num = 0
3639	SET @Pos = 1
3640	WHILE(@Pos <= LEN(@RoleNames))
3641	BEGIN
3642		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
3643		IF (@NextPos = 0 OR @NextPos IS NULL)
3644			SELECT @NextPos = LEN(@RoleNames) + 1
3645		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
3646		SELECT @Pos = @NextPos+1
3647
3648		INSERT INTO @tbNames VALUES (@Name)
3649		SET @Num = @Num + 1
3650	END
3651
3652	INSERT INTO @tbRoles
3653	  SELECT RoleId
3654	  FROM   dbo.aspnet_Roles ar, @tbNames t
3655	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
3656	SELECT @CountR = @@ROWCOUNT
3657
3658	IF (@CountR <> @Num)
3659	BEGIN
3660		SELECT TOP 1 N'''', Name
3661		FROM   @tbNames
3662		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
3663		IF( @TranStarted = 1 )
3664			ROLLBACK TRANSACTION
3665		RETURN(2)
3666	END
3667
3668
3669	DELETE FROM @tbNames WHERE 1=1
3670	SET @Num = 0
3671	SET @Pos = 1
3672
3673
3674	WHILE(@Pos <= LEN(@UserNames))
3675	BEGIN
3676		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
3677		IF (@NextPos = 0 OR @NextPos IS NULL)
3678			SELECT @NextPos = LEN(@UserNames) + 1
3679		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
3680		SELECT @Pos = @NextPos+1
3681
3682		INSERT INTO @tbNames VALUES (@Name)
3683		SET @Num = @Num + 1
3684	END
3685
3686	INSERT INTO @tbUsers
3687	  SELECT UserId
3688	  FROM   dbo.aspnet_Users ar, @tbNames t
3689	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
3690
3691	SELECT @CountU = @@ROWCOUNT
3692	IF (@CountU <> @Num)
3693	BEGIN
3694		SELECT TOP 1 Name, N''''
3695		FROM   @tbNames
3696		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)
3697
3698		IF( @TranStarted = 1 )
3699			ROLLBACK TRANSACTION
3700		RETURN(1)
3701	END
3702
3703	SELECT  @CountAll = COUNT(*)
3704	FROM	dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
3705	WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId
3706
3707	IF (@CountAll <> @CountU * @CountR)
3708	BEGIN
3709		SELECT TOP 1 UserName, RoleName
3710		FROM		 @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
3711		WHERE		 u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
3712					 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
3713					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
3714		IF( @TranStarted = 1 )
3715			ROLLBACK TRANSACTION
3716		RETURN(3)
3717	END
3718
3719	DELETE FROM dbo.aspnet_UsersInRoles
3720	WHERE UserId IN (SELECT UserId FROM @tbUsers)
3721	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
3722	IF( @TranStarted = 1 )
3723		COMMIT TRANSACTION
3724	RETURN(0)
3725END
3726'
3727ELSE
3728SELECT @SqlToExec = N'
3729CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
3730	@ApplicationName  nvarchar(256),
3731	@UserNames		  nvarchar(4000),
3732	@RoleNames		  nvarchar(4000)
3733AS
3734BEGIN
3735	DECLARE @AppId uniqueidentifier
3736	SELECT  @AppId = NULL
3737	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3738	IF (@AppId IS NULL)
3739		RETURN(2)
3740
3741
3742	DECLARE @TranStarted   bit
3743	SET @TranStarted = 0
3744
3745	IF( @@TRANCOUNT = 0 )
3746	BEGIN
3747		BEGIN TRANSACTION
3748		SET @TranStarted = 1
3749	END
3750
3751	DECLARE @RoleId		uniqueidentifier
3752	DECLARE @UserId		uniqueidentifier
3753	DECLARE @UserName	nvarchar(256)
3754	DECLARE @RoleName	nvarchar(256)
3755
3756	DECLARE @CurrentPosU	int
3757	DECLARE @NextPosU		int
3758	DECLARE @CurrentPosR	int
3759	DECLARE @NextPosR		int
3760
3761	SELECT  @CurrentPosU = 1
3762
3763	WHILE(@CurrentPosU <= LEN(@UserNames))
3764	BEGIN
3765		SELECT @NextPosU = CHARINDEX(N'','', @UserNames,  @CurrentPosU)
3766		IF (@NextPosU = 0  OR @NextPosU IS NULL)
3767			SELECT @NextPosU = LEN(@UserNames)+1
3768		SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
3769		SELECT @CurrentPosU = @NextPosU+1
3770
3771		SELECT @CurrentPosR = 1
3772		WHILE(@CurrentPosR <= LEN(@RoleNames))
3773		BEGIN
3774			SELECT @NextPosR = CHARINDEX(N'','', @RoleNames,  @CurrentPosR)
3775			IF (@NextPosR = 0 OR @NextPosR IS NULL)
3776				SELECT @NextPosR = LEN(@RoleNames)+1
3777			SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
3778			SELECT @CurrentPosR = @NextPosR+1
3779
3780			SELECT @RoleId = NULL
3781			SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
3782			IF (@RoleId IS NULL)
3783			BEGIN
3784				SELECT N'''', @RoleName
3785				IF( @TranStarted = 1 )
3786					ROLLBACK TRANSACTION
3787				RETURN(2)
3788			END
3789
3790			SELECT @UserId = NULL
3791			SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
3792			IF (@UserId IS NULL)
3793			BEGIN
3794				SELECT @UserName, N''''
3795				IF( @TranStarted = 1 )
3796					ROLLBACK TRANSACTION
3797				RETURN(1)
3798			END
3799
3800			IF (NOT(EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId)))
3801			BEGIN
3802				SELECT @UserName, @RoleName
3803				IF( @TranStarted = 1 )
3804					ROLLBACK TRANSACTION
3805				RETURN(3)
3806			END
3807			DELETE FROM dbo.aspnet_UsersInRoles WHERE (UserId = @UserId AND RoleId = @RoleId)
3808		END
3809	END
3810	IF( @TranStarted = 1 )
3811		COMMIT TRANSACTION
3812	RETURN(0)
3813END
3814'
3815EXEC sp_executesql @SqlToExec
3816GO
3817/*************************************************************/
3818/*************************************************************/
3819
3820IF (EXISTS (SELECT name
3821              FROM sysobjects
3822             WHERE (name = N'aspnet_UsersInRoles_GetUsersInRoles')
3823               AND (type = 'P')))
3824DROP PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
3825GO
3826
3827CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
3828    @ApplicationName  nvarchar(256),
3829    @RoleName         nvarchar(256)
3830AS
3831BEGIN
3832    DECLARE @ApplicationId uniqueidentifier
3833    SELECT  @ApplicationId = NULL
3834    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3835    IF (@ApplicationId IS NULL)
3836        RETURN(1)
3837     DECLARE @RoleId uniqueidentifier
3838     SELECT  @RoleId = NULL
3839
3840     SELECT  @RoleId = RoleId
3841     FROM    dbo.aspnet_Roles
3842     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
3843
3844     IF (@RoleId IS NULL)
3845         RETURN(1)
3846
3847    SELECT u.UserName
3848    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
3849    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
3850    ORDER BY u.UserName
3851    RETURN(0)
3852END
3853GO
3854
3855/*************************************************************/
3856/*************************************************************/
3857
3858IF (EXISTS (SELECT name
3859              FROM sysobjects
3860             WHERE (name = N'aspnet_UsersInRoles_FindUsersInRole')
3861               AND (type = 'P')))
3862DROP PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
3863GO
3864
3865CREATE PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
3866    @ApplicationName  nvarchar(256),
3867    @RoleName         nvarchar(256),
3868    @UserNameToMatch  nvarchar(256)
3869AS
3870BEGIN
3871    DECLARE @ApplicationId uniqueidentifier
3872    SELECT  @ApplicationId = NULL
3873    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3874    IF (@ApplicationId IS NULL)
3875        RETURN(1)
3876     DECLARE @RoleId uniqueidentifier
3877     SELECT  @RoleId = NULL
3878
3879     SELECT  @RoleId = RoleId
3880     FROM    dbo.aspnet_Roles
3881     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
3882
3883     IF (@RoleId IS NULL)
3884         RETURN(1)
3885
3886    SELECT u.UserName
3887    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
3888    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
3889    ORDER BY u.UserName
3890    RETURN(0)
3891END
3892GO
3893
3894/*************************************************************/
3895/*************************************************************/
3896
3897IF (EXISTS (SELECT name
3898              FROM sysobjects
3899             WHERE (name = N'aspnet_Roles_GetAllRoles')
3900               AND (type = 'P')))
3901DROP PROCEDURE dbo.aspnet_Roles_GetAllRoles
3902GO
3903
3904CREATE PROCEDURE dbo.aspnet_Roles_GetAllRoles (
3905    @ApplicationName           nvarchar(256))
3906AS
3907BEGIN
3908    DECLARE @ApplicationId uniqueidentifier
3909    SELECT  @ApplicationId = NULL
3910    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
3911    IF (@ApplicationId IS NULL)
3912        RETURN
3913    SELECT RoleName
3914    FROM   dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
3915    ORDER BY RoleName
3916END
3917GO
3918
3919/*************************************************************/
3920/*************************************************************/
3921
3922IF (NOT EXISTS (SELECT name
3923                FROM sysobjects
3924                WHERE (name = N'vw_aspnet_Roles')
3925                  AND (type = 'V')))
3926BEGIN
3927  PRINT 'Creating the vw_aspnet_Roles view...'
3928  EXEC(N'
3929  CREATE VIEW [dbo].[vw_aspnet_Roles]
3930  AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
3931  FROM [dbo].[aspnet_Roles]
3932  ')
3933END
3934GO
3935
3936/*************************************************************/
3937/*************************************************************/
3938
3939IF (NOT EXISTS (SELECT name
3940                FROM sysobjects
3941                WHERE (name = N'vw_aspnet_UsersInRoles')
3942                  AND (type = 'V')))
3943BEGIN
3944  PRINT 'Creating the vw_aspnet_UsersInRoles view...'
3945  EXEC(N'
3946  CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
3947  AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
3948  FROM [dbo].[aspnet_UsersInRoles]
3949  ')
3950END
3951GO
3952
3953/*************************************************************/
3954/*************************************************************/
3955
3956--
3957--Create Role Manager schema version
3958--
3959
3960DECLARE @command nvarchar(4000)
3961SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
3962EXECUTE (@command)
3963GO
3964
3965EXEC [dbo].aspnet_RegisterSchemaVersion N'Role Manager', N'1', 1, 1
3966GO
3967
3968/*************************************************************/
3969/*************************************************************/
3970
3971--
3972--Create Role Manager roles
3973--
3974
3975IF ( NOT EXISTS ( SELECT name
3976                  FROM sysusers
3977                  WHERE issqlrole = 1
3978                  AND name = N'aspnet_Roles_FullAccess'  ) )
3979EXEC sp_addrole N'aspnet_Roles_FullAccess'
3980
3981IF ( NOT EXISTS ( SELECT name
3982                  FROM sysusers
3983                  WHERE issqlrole = 1
3984                  AND name = N'aspnet_Roles_BasicAccess'  ) )
3985EXEC sp_addrole N'aspnet_Roles_BasicAccess'
3986
3987IF ( NOT EXISTS ( SELECT name
3988                  FROM sysusers
3989                  WHERE issqlrole = 1
3990                  AND name = N'aspnet_Roles_ReportingAccess'  ) )
3991EXEC sp_addrole N'aspnet_Roles_ReportingAccess'
3992GO
3993
3994EXEC sp_addrolemember N'aspnet_Roles_BasicAccess', N'aspnet_Roles_FullAccess'
3995EXEC sp_addrolemember N'aspnet_Roles_ReportingAccess', N'aspnet_Roles_FullAccess'
3996GO
3997
3998--
3999--Stored Procedure rights for BasicAccess
4000--
4001GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_BasicAccess
4002GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_BasicAccess
4003GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_BasicAccess
4004GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_BasicAccess
4005GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_BasicAccess
4006
4007--
4008--Stored Procedure rights for ReportingAccess
4009--
4010GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_ReportingAccess
4011GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_ReportingAccess
4012GRANT EXECUTE ON dbo.aspnet_Roles_RoleExists TO aspnet_Roles_ReportingAccess
4013GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetUsersInRoles TO aspnet_Roles_ReportingAccess
4014GRANT EXECUTE ON dbo.aspnet_UsersInRoles_FindUsersInRole TO aspnet_Roles_ReportingAccess
4015GRANT EXECUTE ON dbo.aspnet_Roles_GetAllRoles TO aspnet_Roles_ReportingAccess
4016GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_ReportingAccess
4017GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_ReportingAccess
4018GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_ReportingAccess
4019
4020--
4021--Additional stored procedure rights for FullAccess
4022--
4023
4024GRANT EXECUTE ON dbo.aspnet_Roles_CreateRole TO aspnet_Roles_FullAccess
4025GRANT EXECUTE ON dbo.aspnet_Roles_DeleteRole TO aspnet_Roles_FullAccess
4026GRANT EXECUTE ON dbo.aspnet_UsersInRoles_AddUsersToRoles TO aspnet_Roles_FullAccess
4027GRANT EXECUTE ON dbo.aspnet_UsersInRoles_RemoveUsersFromRoles TO aspnet_Roles_FullAccess
4028
4029--
4030--View rights
4031--
4032GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Roles_ReportingAccess
4033GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Roles_ReportingAccess
4034
4035GRANT SELECT ON dbo.vw_aspnet_Roles TO aspnet_Roles_ReportingAccess
4036GRANT SELECT ON dbo.vw_aspnet_UsersInRoles TO aspnet_Roles_ReportingAccess
4037
4038GO
4039
4040/*************************************************************/
4041/*************************************************************/
4042/*************************************************************/
4043/*************************************************************/
4044
4045DECLARE @command nvarchar(4000)
4046SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
4047EXECUTE (@command)
4048GO
4049
4050PRINT '---------------------------------------'
4051PRINT 'Completed execution of InstallRoles.SQL'
4052PRINT '---------------------------------------'
4053/**********************************************************************/
4054/* InstallPersonalization.SQL                                         */
4055/*                                                                    */
4056/* Installs the tables, triggers and stored procedures necessary for  */
4057/* supporting the personalization feature of ASP.NET                  */
4058/*                                                                    */
4059/* InstallCommon.sql must be run before running this file.            */
4060/*
4061** Copyright Microsoft, Inc. 2002
4062** All Rights Reserved.
4063*/
4064/**********************************************************************/
4065
4066PRINT '------------------------------------------------'
4067PRINT 'Starting execution of InstallPersonalization.SQL'
4068PRINT '------------------------------------------------'
4069GO
4070
4071SET QUOTED_IDENTIFIER OFF
4072SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
4073GO
4074SET ANSI_PADDING ON
4075GO
4076SET ANSI_NULL_DFLT_ON ON
4077GO
4078
4079/*************************************************************/
4080/*************************************************************/
4081/*************************************************************/
4082/*************************************************************/
4083/*************************************************************/
4084
4085DECLARE @dbname NVARCHAR(128)
4086
4087SET @dbname = N'aspnetdb'
4088
4089IF (NOT EXISTS (SELECT name
4090                FROM master.dbo.sysdatabases
4091                WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
4092BEGIN
4093  RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
4094END
4095GO
4096
4097USE [aspnetdb]
4098GO
4099
4100IF (NOT EXISTS (SELECT name
4101                FROM sysobjects
4102                WHERE (name = N'aspnet_Applications')
4103                  AND (type = 'U')))
4104BEGIN
4105  RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
4106END
4107
4108IF (NOT EXISTS (SELECT name
4109                FROM sysobjects
4110                WHERE (name = N'aspnet_Users')
4111                  AND (type = 'U')))
4112BEGIN
4113  RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
4114END
4115
4116IF (NOT EXISTS (SELECT name
4117              FROM sysobjects
4118             WHERE (name = N'aspnet_Applications_CreateApplication')
4119               AND (type = 'P')))
4120BEGIN
4121  RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
4122END
4123
4124IF (NOT EXISTS (SELECT name
4125              FROM sysobjects
4126             WHERE (name = N'aspnet_Users_CreateUser')
4127               AND (type = 'P')))
4128BEGIN
4129  RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
4130END
4131
4132IF (NOT EXISTS (SELECT name
4133              FROM sysobjects
4134             WHERE (name = N'aspnet_Users_DeleteUser')
4135               AND (type = 'P')))
4136BEGIN
4137  RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
4138END
4139
4140/***************************************************************************************************************************/
4141/***************************************************************************************************************************/
4142
4143IF (NOT EXISTS (SELECT name
4144                FROM sysobjects
4145                WHERE (name = N'aspnet_Paths')
4146                  AND (type = 'U')))
4147BEGIN
4148  PRINT 'Creating the aspnet_Paths table...'
4149  CREATE TABLE dbo.aspnet_Paths (
4150        ApplicationId    UNIQUEIDENTIFIER    NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
4151        PathId            UNIQUEIDENTIFIER   PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
4152        Path              NVARCHAR(256)      NOT NULL,
4153        LoweredPath       NVARCHAR(256)      NOT NULL)
4154  CREATE UNIQUE CLUSTERED INDEX aspnet_Paths_index ON dbo.aspnet_Paths(ApplicationId, LoweredPath)
4155END
4156
4157
4158/***************************************************************************************************************************/
4159/***************************************************************************************************************************/
4160
4161IF (EXISTS (SELECT name
4162              FROM sysobjects
4163             WHERE (name = N'aspnet_Personalization_GetApplicationId')
4164               AND (type = 'P')))
4165DROP PROCEDURE dbo.aspnet_Personalization_GetApplicationId
4166GO
4167CREATE PROCEDURE dbo.aspnet_Personalization_GetApplicationId (
4168    @ApplicationName NVARCHAR(256),
4169    @ApplicationId UNIQUEIDENTIFIER OUT)
4170AS
4171BEGIN
4172    SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
4173END
4174GO
4175
4176/***************************************************************************************************************************/
4177/***************************************************************************************************************************/
4178
4179IF (EXISTS (SELECT name
4180              FROM sysobjects
4181             WHERE (name = N'aspnet_Paths_CreatePath')
4182               AND (type = 'P')))
4183DROP PROCEDURE dbo.aspnet_Paths_CreatePath
4184GO
4185CREATE PROCEDURE dbo.aspnet_Paths_CreatePath
4186    @ApplicationId UNIQUEIDENTIFIER,
4187    @Path           NVARCHAR(256),
4188    @PathId         UNIQUEIDENTIFIER OUTPUT
4189AS
4190BEGIN
4191    BEGIN TRANSACTION
4192    IF (NOT EXISTS(SELECT * FROM dbo.aspnet_Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
4193    BEGIN
4194        INSERT dbo.aspnet_Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
4195    END
4196    COMMIT TRANSACTION
4197    SELECT @PathId = PathId FROM dbo.aspnet_Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
4198END
4199GO
4200
4201/***************************************************************************************************************************/
4202
4203IF (NOT EXISTS (SELECT name
4204                FROM sysobjects
4205                WHERE (name = N'aspnet_PersonalizationAllUsers')
4206                  AND (type = 'U')))
4207BEGIN
4208  PRINT 'Creating the aspnet_PersonalizationAllUsers table...'
4209  CREATE TABLE dbo.aspnet_PersonalizationAllUsers (
4210        PathId            UNIQUEIDENTIFIER PRIMARY KEY FOREIGN KEY REFERENCES dbo.aspnet_Paths (PathId),
4211        PageSettings     IMAGE NOT NULL,
4212        LastUpdatedDate  DATETIME NOT NULL)
4213END
4214
4215
4216IF (NOT EXISTS (SELECT name
4217                FROM sysobjects
4218                WHERE (name = N'aspnet_PersonalizationPerUser')
4219                  AND (type = 'U')))
4220BEGIN
4221  PRINT 'Creating the aspnet_PersonalizationPerUser table...'
4222  CREATE TABLE dbo.aspnet_PersonalizationPerUser (
4223        Id               UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
4224        PathId           UNIQUEIDENTIFIER FOREIGN KEY REFERENCES dbo.aspnet_Paths (PathId),
4225        UserId           UNIQUEIDENTIFIER FOREIGN KEY REFERENCES dbo.aspnet_Users (UserId),
4226        PageSettings     IMAGE NOT NULL,
4227        LastUpdatedDate  DATETIME NOT NULL)
4228  CREATE UNIQUE CLUSTERED INDEX aspnet_PersonalizationPerUser_index1 ON [dbo].[aspnet_PersonalizationPerUser](PathId,UserId)
4229  CREATE UNIQUE INDEX aspnet_PersonalizationPerUser_ncindex2 ON [dbo].[aspnet_PersonalizationPerUser](UserId,PathId)
4230END
4231
4232
4233IF (EXISTS (SELECT name
4234              FROM sysobjects
4235             WHERE (name = N'aspnet_PersonalizationAllUsers_GetPageSettings')
4236               AND (type = 'P')))
4237DROP PROCEDURE dbo.aspnet_PersonalizationAllUsers_GetPageSettings
4238GO
4239CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_GetPageSettings (
4240    @ApplicationName  NVARCHAR(256),
4241    @Path              NVARCHAR(256))
4242AS
4243BEGIN
4244    DECLARE @ApplicationId UNIQUEIDENTIFIER
4245    DECLARE @PathId UNIQUEIDENTIFIER
4246
4247    SELECT @ApplicationId = NULL
4248    SELECT @PathId = NULL
4249
4250    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4251    IF (@ApplicationId IS NULL)
4252    BEGIN
4253        RETURN
4254    END
4255
4256    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
4257    IF (@PathId IS NULL)
4258    BEGIN
4259        RETURN
4260    END
4261
4262    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationAllUsers p WHERE p.PathId = @PathId
4263END
4264GO
4265
4266
4267IF (EXISTS (SELECT name
4268              FROM sysobjects
4269             WHERE (name = N'aspnet_PersonalizationAllUsers_ResetPageSettings')
4270               AND (type = 'P')))
4271DROP PROCEDURE dbo.aspnet_PersonalizationAllUsers_ResetPageSettings
4272GO
4273CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_ResetPageSettings (
4274    @ApplicationName  NVARCHAR(256),
4275    @Path              NVARCHAR(256))
4276AS
4277BEGIN
4278    DECLARE @ApplicationId UNIQUEIDENTIFIER
4279    DECLARE @PathId UNIQUEIDENTIFIER
4280
4281    SELECT @ApplicationId = NULL
4282    SELECT @PathId = NULL
4283
4284    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4285    IF (@ApplicationId IS NULL)
4286    BEGIN
4287        RETURN
4288    END
4289
4290    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
4291    IF (@PathId IS NULL)
4292    BEGIN
4293        RETURN
4294    END
4295
4296    DELETE FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId
4297    RETURN 0
4298END
4299GO
4300
4301
4302IF (EXISTS (SELECT name
4303              FROM sysobjects
4304             WHERE (name = N'aspnet_PersonalizationAllUsers_SetPageSettings')
4305               AND (type = 'P')))
4306DROP PROCEDURE dbo.aspnet_PersonalizationAllUsers_SetPageSettings
4307GO
4308CREATE PROCEDURE dbo.aspnet_PersonalizationAllUsers_SetPageSettings (
4309    @ApplicationName  NVARCHAR(256),
4310    @Path             NVARCHAR(256),
4311    @PageSettings     IMAGE,
4312    @CurrentTimeUtc   DATETIME)
4313AS
4314BEGIN
4315    DECLARE @ApplicationId UNIQUEIDENTIFIER
4316    DECLARE @PathId UNIQUEIDENTIFIER
4317
4318    SELECT @ApplicationId = NULL
4319    SELECT @PathId = NULL
4320
4321    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
4322
4323    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
4324    IF (@PathId IS NULL)
4325    BEGIN
4326        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
4327    END
4328
4329    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId))
4330        UPDATE dbo.aspnet_PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
4331    ELSE
4332        INSERT INTO dbo.aspnet_PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
4333    RETURN 0
4334END
4335GO
4336
4337
4338IF (EXISTS (SELECT name
4339              FROM sysobjects
4340             WHERE (name = N'aspnet_PersonalizationPerUser_GetPageSettings')
4341               AND (type = 'P')))
4342DROP PROCEDURE dbo.aspnet_PersonalizationPerUser_GetPageSettings
4343GO
4344CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_GetPageSettings (
4345    @ApplicationName  NVARCHAR(256),
4346    @UserName         NVARCHAR(256),
4347    @Path             NVARCHAR(256),
4348    @CurrentTimeUtc   DATETIME)
4349AS
4350BEGIN
4351    DECLARE @ApplicationId UNIQUEIDENTIFIER
4352    DECLARE @PathId UNIQUEIDENTIFIER
4353    DECLARE @UserId UNIQUEIDENTIFIER
4354
4355    SELECT @ApplicationId = NULL
4356    SELECT @PathId = NULL
4357    SELECT @UserId = NULL
4358
4359    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4360    IF (@ApplicationId IS NULL)
4361    BEGIN
4362        RETURN
4363    END
4364
4365    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
4366    IF (@PathId IS NULL)
4367    BEGIN
4368        RETURN
4369    END
4370
4371    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
4372    IF (@UserId IS NULL)
4373    BEGIN
4374        RETURN
4375    END
4376
4377    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
4378    SET      LastActivityDate = @CurrentTimeUtc
4379    WHERE    UserId = @UserId
4380    IF (@@ROWCOUNT = 0) -- Username not found
4381        RETURN
4382
4383    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
4384END
4385GO
4386
4387
4388IF (EXISTS (SELECT name
4389              FROM sysobjects
4390             WHERE (name = N'aspnet_PersonalizationPerUser_ResetPageSettings')
4391               AND (type = 'P')))
4392DROP PROCEDURE dbo.aspnet_PersonalizationPerUser_ResetPageSettings
4393GO
4394CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_ResetPageSettings (
4395    @ApplicationName  NVARCHAR(256),
4396    @UserName         NVARCHAR(256),
4397    @Path             NVARCHAR(256),
4398    @CurrentTimeUtc   DATETIME)
4399AS
4400BEGIN
4401    DECLARE @ApplicationId UNIQUEIDENTIFIER
4402    DECLARE @PathId UNIQUEIDENTIFIER
4403    DECLARE @UserId UNIQUEIDENTIFIER
4404
4405    SELECT @ApplicationId = NULL
4406    SELECT @PathId = NULL
4407    SELECT @UserId = NULL
4408
4409    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4410    IF (@ApplicationId IS NULL)
4411    BEGIN
4412        RETURN
4413    END
4414
4415    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
4416    IF (@PathId IS NULL)
4417    BEGIN
4418        RETURN
4419    END
4420
4421    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
4422    IF (@UserId IS NULL)
4423    BEGIN
4424        RETURN
4425    END
4426
4427    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
4428    SET      LastActivityDate = @CurrentTimeUtc
4429    WHERE    UserId = @UserId
4430    IF (@@ROWCOUNT = 0) -- Username not found
4431        RETURN
4432
4433    DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
4434    RETURN 0
4435END
4436GO
4437
4438
4439IF (EXISTS (SELECT name
4440              FROM sysobjects
4441             WHERE (name = N'aspnet_PersonalizationPerUser_SetPageSettings')
4442               AND (type = 'P')))
4443DROP PROCEDURE dbo.aspnet_PersonalizationPerUser_SetPageSettings
4444GO
4445CREATE PROCEDURE dbo.aspnet_PersonalizationPerUser_SetPageSettings (
4446    @ApplicationName  NVARCHAR(256),
4447    @UserName         NVARCHAR(256),
4448    @Path             NVARCHAR(256),
4449    @PageSettings     IMAGE,
4450    @CurrentTimeUtc   DATETIME)
4451AS
4452BEGIN
4453    DECLARE @ApplicationId UNIQUEIDENTIFIER
4454    DECLARE @PathId UNIQUEIDENTIFIER
4455    DECLARE @UserId UNIQUEIDENTIFIER
4456
4457    SELECT @ApplicationId = NULL
4458    SELECT @PathId = NULL
4459    SELECT @UserId = NULL
4460
4461    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
4462
4463    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
4464    IF (@PathId IS NULL)
4465    BEGIN
4466        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
4467    END
4468
4469    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
4470    IF (@UserId IS NULL)
4471    BEGIN
4472        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
4473    END
4474
4475    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
4476    SET      LastActivityDate = @CurrentTimeUtc
4477    WHERE    UserId = @UserId
4478    IF (@@ROWCOUNT = 0) -- Username not found
4479        RETURN
4480
4481    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
4482        UPDATE dbo.aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
4483    ELSE
4484        INSERT INTO dbo.aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
4485    RETURN 0
4486END
4487GO
4488
4489/*************************************************************/
4490/* Personalization Administration                            */
4491/*************************************************************/
4492
4493IF (EXISTS (SELECT name
4494              FROM sysobjects
4495             WHERE (name = N'aspnet_PersonalizationAdministration_DeleteAllState')
4496               AND (type = 'P')))
4497DROP PROCEDURE dbo.aspnet_PersonalizationAdministration_DeleteAllState
4498GO
4499CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_DeleteAllState (
4500    @AllUsersScope bit,
4501    @ApplicationName NVARCHAR(256),
4502    @Count int OUT)
4503AS
4504BEGIN
4505    DECLARE @ApplicationId UNIQUEIDENTIFIER
4506    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4507    IF (@ApplicationId IS NULL)
4508        SELECT @Count = 0
4509    ELSE
4510    BEGIN
4511        IF (@AllUsersScope = 1)
4512            DELETE FROM aspnet_PersonalizationAllUsers
4513            WHERE PathId IN
4514               (SELECT Paths.PathId
4515                FROM dbo.aspnet_Paths Paths
4516                WHERE Paths.ApplicationId = @ApplicationId)
4517        ELSE
4518            DELETE FROM aspnet_PersonalizationPerUser
4519            WHERE PathId IN
4520               (SELECT Paths.PathId
4521                FROM dbo.aspnet_Paths Paths
4522                WHERE Paths.ApplicationId = @ApplicationId)
4523
4524        SELECT @Count = @@ROWCOUNT
4525    END
4526END
4527GO
4528
4529/*************************************************************/
4530IF (EXISTS (SELECT name
4531              FROM sysobjects
4532             WHERE (name = N'aspnet_PersonalizationAdministration_ResetSharedState')
4533               AND (type = 'P')))
4534DROP PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetSharedState
4535GO
4536CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetSharedState (
4537    @Count int OUT,
4538    @ApplicationName NVARCHAR(256),
4539    @Path NVARCHAR(256))
4540AS
4541BEGIN
4542    DECLARE @ApplicationId UNIQUEIDENTIFIER
4543    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4544    IF (@ApplicationId IS NULL)
4545        SELECT @Count = 0
4546    ELSE
4547    BEGIN
4548        DELETE FROM dbo.aspnet_PersonalizationAllUsers
4549        WHERE PathId IN
4550            (SELECT AllUsers.PathId
4551             FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
4552             WHERE Paths.ApplicationId = @ApplicationId
4553                   AND AllUsers.PathId = Paths.PathId
4554                   AND Paths.LoweredPath = LOWER(@Path))
4555
4556        SELECT @Count = @@ROWCOUNT
4557    END
4558END
4559GO
4560
4561/*************************************************************/
4562IF (EXISTS (SELECT name
4563              FROM sysobjects
4564             WHERE (name = N'aspnet_PersonalizationAdministration_ResetUserState')
4565               AND (type = 'P')))
4566DROP PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetUserState
4567GO
4568CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_ResetUserState (
4569    @Count                  int                 OUT,
4570    @ApplicationName        NVARCHAR(256),
4571    @InactiveSinceDate      DATETIME            = NULL,
4572    @UserName               NVARCHAR(256)       = NULL,
4573    @Path                   NVARCHAR(256)       = NULL)
4574AS
4575BEGIN
4576    DECLARE @ApplicationId UNIQUEIDENTIFIER
4577    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4578    IF (@ApplicationId IS NULL)
4579        SELECT @Count = 0
4580    ELSE
4581    BEGIN
4582        DELETE FROM dbo.aspnet_PersonalizationPerUser
4583        WHERE Id IN (SELECT PerUser.Id
4584                     FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
4585                     WHERE Paths.ApplicationId = @ApplicationId
4586                           AND PerUser.UserId = Users.UserId
4587                           AND PerUser.PathId = Paths.PathId
4588                           AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
4589                           AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
4590                           AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))
4591
4592        SELECT @Count = @@ROWCOUNT
4593    END
4594END
4595GO
4596
4597/*************************************************************/
4598IF (EXISTS (SELECT name
4599              FROM sysobjects
4600             WHERE (name = N'aspnet_PersonalizationAdministration_FindState')
4601               AND (type = 'P')))
4602DROP PROCEDURE dbo.aspnet_PersonalizationAdministration_FindState
4603GO
4604CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_FindState (
4605    @AllUsersScope bit,
4606    @ApplicationName NVARCHAR(256),
4607    @PageIndex              INT,
4608    @PageSize               INT,
4609    @Path NVARCHAR(256) = NULL,
4610    @UserName NVARCHAR(256) = NULL,
4611    @InactiveSinceDate DATETIME = NULL)
4612AS
4613BEGIN
4614    DECLARE @ApplicationId UNIQUEIDENTIFIER
4615    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4616    IF (@ApplicationId IS NULL)
4617        RETURN
4618
4619    -- Set the page bounds
4620    DECLARE @PageLowerBound INT
4621    DECLARE @PageUpperBound INT
4622    DECLARE @TotalRecords   INT
4623    SET @PageLowerBound = @PageSize * @PageIndex
4624    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
4625
4626    -- Create a temp table to store the selected results
4627    CREATE TABLE #PageIndex (
4628        IndexId int IDENTITY (0, 1) NOT NULL,
4629        ItemId UNIQUEIDENTIFIER
4630    )
4631
4632    IF (@AllUsersScope = 1)
4633    BEGIN
4634        -- Insert into our temp table
4635        INSERT INTO #PageIndex (ItemId)
4636        SELECT Paths.PathId
4637        FROM dbo.aspnet_Paths Paths,
4638             ((SELECT Paths.PathId
4639               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
4640               WHERE Paths.ApplicationId = @ApplicationId
4641                      AND AllUsers.PathId = Paths.PathId
4642                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
4643              ) AS SharedDataPerPath
4644              FULL OUTER JOIN
4645              (SELECT DISTINCT Paths.PathId
4646               FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
4647               WHERE Paths.ApplicationId = @ApplicationId
4648                      AND PerUser.PathId = Paths.PathId
4649                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
4650              ) AS UserDataPerPath
4651              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
4652             )
4653        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
4654        ORDER BY Paths.Path ASC
4655
4656        SELECT @TotalRecords = @@ROWCOUNT
4657
4658        SELECT Paths.Path,
4659               SharedDataPerPath.LastUpdatedDate,
4660               SharedDataPerPath.SharedDataLength,
4661               UserDataPerPath.UserDataLength,
4662               UserDataPerPath.UserCount
4663        FROM dbo.aspnet_Paths Paths,
4664             ((SELECT PageIndex.ItemId AS PathId,
4665                      AllUsers.LastUpdatedDate AS LastUpdatedDate,
4666                      DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
4667               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
4668               WHERE AllUsers.PathId = PageIndex.ItemId
4669                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
4670              ) AS SharedDataPerPath
4671              FULL OUTER JOIN
4672              (SELECT PageIndex.ItemId AS PathId,
4673                      SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
4674                      COUNT(*) AS UserCount
4675               FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
4676               WHERE PerUser.PathId = PageIndex.ItemId
4677                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
4678               GROUP BY PageIndex.ItemId
4679              ) AS UserDataPerPath
4680              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
4681             )
4682        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
4683        ORDER BY Paths.Path ASC
4684    END
4685    ELSE
4686    BEGIN
4687        -- Insert into our temp table
4688        INSERT INTO #PageIndex (ItemId)
4689        SELECT PerUser.Id
4690        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
4691        WHERE Paths.ApplicationId = @ApplicationId
4692              AND PerUser.UserId = Users.UserId
4693              AND PerUser.PathId = Paths.PathId
4694              AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
4695              AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
4696              AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
4697        ORDER BY Paths.Path ASC, Users.UserName ASC
4698
4699        SELECT @TotalRecords = @@ROWCOUNT
4700
4701        SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
4702        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
4703        WHERE PerUser.Id = PageIndex.ItemId
4704              AND PerUser.UserId = Users.UserId
4705              AND PerUser.PathId = Paths.PathId
4706              AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
4707        ORDER BY Paths.Path ASC, Users.UserName ASC
4708    END
4709
4710    RETURN @TotalRecords
4711END
4712GO
4713
4714/*************************************************************/
4715IF (EXISTS (SELECT name
4716              FROM sysobjects
4717             WHERE (name = N'aspnet_PersonalizationAdministration_GetCountOfState')
4718               AND (type = 'P')))
4719DROP PROCEDURE dbo.aspnet_PersonalizationAdministration_GetCountOfState
4720GO
4721CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_GetCountOfState (
4722    @Count int OUT,
4723    @AllUsersScope bit,
4724    @ApplicationName NVARCHAR(256),
4725    @Path NVARCHAR(256) = NULL,
4726    @UserName NVARCHAR(256) = NULL,
4727    @InactiveSinceDate DATETIME = NULL)
4728AS
4729BEGIN
4730
4731    DECLARE @ApplicationId UNIQUEIDENTIFIER
4732    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
4733    IF (@ApplicationId IS NULL)
4734        SELECT @Count = 0
4735    ELSE
4736        IF (@AllUsersScope = 1)
4737            SELECT @Count = COUNT(*)
4738            FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
4739            WHERE Paths.ApplicationId = @ApplicationId
4740                  AND AllUsers.PathId = Paths.PathId
4741                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
4742        ELSE
4743            SELECT @Count = COUNT(*)
4744            FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
4745            WHERE Paths.ApplicationId = @ApplicationId
4746                  AND PerUser.UserId = Users.UserId
4747                  AND PerUser.PathId = Paths.PathId
4748                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
4749                  AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
4750                  AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
4751END
4752GO
4753
4754/*************************************************************/
4755/*************************************************************/
4756
4757IF (NOT EXISTS (SELECT name
4758                FROM sysobjects
4759                WHERE (name = N'vw_aspnet_WebPartState_Paths')
4760                  AND (type = 'V')))
4761BEGIN
4762  PRINT 'Creating the vw_aspnet_WebPartState_Paths view...'
4763  EXEC(N'
4764  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]
4765  AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
4766  FROM [dbo].[aspnet_Paths]
4767  ')
4768END
4769
4770/*************************************************************/
4771/*************************************************************/
4772
4773IF (NOT EXISTS (SELECT name
4774                FROM sysobjects
4775                WHERE (name = N'vw_aspnet_WebPartState_Shared')
4776                  AND (type = 'V')))
4777BEGIN
4778  PRINT 'Creating the vw_aspnet_WebPartState_Shared view...'
4779  EXEC(N'
4780  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]
4781  AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
4782  FROM [dbo].[aspnet_PersonalizationAllUsers]
4783  ')
4784END
4785
4786/*************************************************************/
4787/*************************************************************/
4788
4789IF (NOT EXISTS (SELECT name
4790                FROM sysobjects
4791                WHERE (name = N'vw_aspnet_WebPartState_User')
4792                  AND (type = 'V')))
4793BEGIN
4794  PRINT 'Creating the vw_aspnet_WebPartState_User view...'
4795  EXEC(N'
4796  CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]
4797  AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
4798  FROM [dbo].[aspnet_PersonalizationPerUser]
4799  ')
4800END
4801GO
4802
4803/*************************************************************/
4804/*************************************************************/
4805
4806--
4807--Create Personalization schema version
4808--
4809
4810DECLARE @command nvarchar(4000)
4811SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
4812EXECUTE (@command)
4813GO
4814
4815EXEC [dbo].aspnet_RegisterSchemaVersion N'Personalization', N'1', 1, 1
4816GO
4817
4818/*************************************************************/
4819/*************************************************************/
4820
4821--
4822--Create Personalization roles
4823--
4824
4825IF ( NOT EXISTS ( SELECT name
4826                  FROM sysusers
4827                  WHERE issqlrole = 1
4828                  AND name = N'aspnet_Personalization_FullAccess'  ) )
4829EXEC sp_addrole N'aspnet_Personalization_FullAccess'
4830
4831IF ( NOT EXISTS ( SELECT name
4832                  FROM dbo.sysusers
4833                  WHERE issqlrole = 1
4834                  AND name = N'aspnet_Personalization_BasicAccess'  ) )
4835EXEC sp_addrole N'aspnet_Personalization_BasicAccess'
4836
4837IF ( NOT EXISTS ( SELECT name
4838                  FROM sysusers
4839                  WHERE issqlrole = 1
4840                  AND name = N'aspnet_Personalization_ReportingAccess'  ) )
4841EXEC sp_addrole N'aspnet_Personalization_ReportingAccess'
4842GO
4843
4844EXEC sp_addrolemember N'aspnet_Personalization_BasicAccess', N'aspnet_Personalization_FullAccess'
4845EXEC sp_addrolemember N'aspnet_Personalization_ReportingAccess', N'aspnet_Personalization_FullAccess'
4846GO
4847
4848--
4849--Stored Procedure rights for BasicAccess
4850--
4851GRANT EXECUTE ON dbo.aspnet_Paths_CreatePath TO aspnet_Personalization_BasicAccess
4852
4853GRANT EXECUTE ON dbo.aspnet_PersonalizationAllUsers_GetPageSettings TO aspnet_Personalization_BasicAccess
4854GRANT EXECUTE ON dbo.aspnet_PersonalizationPerUser_GetPageSettings TO aspnet_Personalization_BasicAccess
4855GRANT EXECUTE ON dbo.aspnet_PersonalizationAllUsers_ResetPageSettings TO aspnet_Personalization_BasicAccess
4856GRANT EXECUTE ON dbo.aspnet_PersonalizationPerUser_ResetPageSettings TO aspnet_Personalization_BasicAccess
4857GRANT EXECUTE ON dbo.aspnet_PersonalizationAllUsers_SetPageSettings TO aspnet_Personalization_BasicAccess
4858GRANT EXECUTE ON dbo.aspnet_PersonalizationPerUser_SetPageSettings TO aspnet_Personalization_BasicAccess
4859
4860GRANT EXECUTE ON dbo.aspnet_Personalization_GetApplicationId TO aspnet_Personalization_BasicAccess
4861
4862GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Personalization_BasicAccess
4863GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Personalization_BasicAccess
4864GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Personalization_BasicAccess
4865
4866--
4867--Stored Procedure rights for ReportingAccess
4868--
4869GRANT EXECUTE ON dbo.aspnet_PersonalizationAdministration_FindState TO aspnet_Personalization_ReportingAccess
4870GRANT EXECUTE ON dbo.aspnet_PersonalizationAdministration_GetCountOfState TO aspnet_Personalization_ReportingAccess
4871
4872GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Personalization_ReportingAccess
4873GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Personalization_ReportingAccess
4874GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Personalization_ReportingAccess
4875
4876--
4877--Additional stored procedure rights for FullAccess
4878--
4879GRANT EXECUTE ON dbo.aspnet_PersonalizationAdministration_ResetUserState TO aspnet_Personalization_FullAccess
4880GRANT EXECUTE ON dbo.aspnet_PersonalizationAdministration_ResetSharedState TO aspnet_Personalization_FullAccess
4881GRANT EXECUTE ON dbo.aspnet_PersonalizationAdministration_DeleteAllState TO aspnet_Personalization_FullAccess
4882
4883--
4884--View rights
4885--
4886GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Personalization_ReportingAccess
4887GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Personalization_ReportingAccess
4888
4889GRANT SELECT ON dbo.vw_aspnet_WebPartState_Paths TO aspnet_Personalization_ReportingAccess
4890GRANT SELECT ON dbo.vw_aspnet_WebPartState_Shared TO aspnet_Personalization_ReportingAccess
4891GRANT SELECT ON dbo.vw_aspnet_WebPartState_User TO aspnet_Personalization_ReportingAccess
4892GO
4893-------------------------------------------------------------------------
4894--- Version specific install
4895-------------------------------------------------------------------------
4896
4897DECLARE @ver INT
4898DECLARE @version NCHAR(100)
4899DECLARE @dot INT
4900DECLARE @hyphen INT
4901DECLARE @SqlToExec NCHAR(400)
4902
4903SELECT @ver = 8
4904SELECT @version = @@Version
4905SELECT @hyphen  = CHARINDEX(N' - ', @version)
4906IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
4907BEGIN
4908    SELECT @hyphen = @hyphen + 3
4909    SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
4910    IF (NOT(@dot IS NULL) AND @dot > @hyphen)
4911    BEGIN
4912        SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
4913        SELECT @ver     = CONVERT(INT, @version)
4914    END
4915END
4916
4917IF (@ver >= 8)
4918BEGIN
4919    EXEC sp_tableoption N'aspnet_PersonalizationAllUsers', 'text in row', 6000
4920    EXEC sp_tableoption N'aspnet_PersonalizationPerUser', 'text in row', 6000
4921END
4922GO
4923
4924/*************************************************************/
4925/*************************************************************/
4926/*************************************************************/
4927/*************************************************************/
4928
4929DECLARE @command nvarchar(4000)
4930SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
4931EXECUTE (@command)
4932GO
4933
4934PRINT '-------------------------------------------------'
4935PRINT 'Completed execution of InstallPersonalization.SQL'
4936PRINT '-------------------------------------------------'