locked
Light Switch Project exported as .exe with SQL Script for user permission ! HELP HELP :( RRS feed

  • Question

  • Dear All, <o:p></o:p>

    I am new in the light switch world, and finally after 3 weeks of hard working I was able to create my project which users 2 views in my database to retrieve data and  just show them in the interface.<o:p></o:p>

    everything is fine till now, the problem is:<o:p></o:p>

    after my project is completed, I want to export it in order for the user to install in locally and user it. I created some security groups in the project. I want the Light switch project to know which user login from our directory. since our all user are within one directory for example Company\zmo02.<o:p></o:p>

    So before exporting the project I set in the properties that administrator should be company\zmo02.then I exported the project. Of course I exported the project with 2 sql scripts to modify the connection to the data base.<o:p></o:p>

    I WAS SCHOKED !!! the script is so huge and I have no idea what the hell is this. I just want to take the logged user name from our directory and compare it with the member which I specify in the security group which I created in light switch. 

    Ps: Forgive me if the question is hard, but I donotknow how can I changed this script, this is the first time for me to work with scripts.

    Thanks a lot,

    Zayed


    Wednesday, July 16, 2014 8:47 AM

All replies

  • /*
    Deployment script for NavisionView
    */
    
    GO
    SET ANSI_PADDING, QUOTED_IDENTIFIER ON;
    
    SET ANSI_NULLS, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT OFF;
    
    
    GO
    :setvar DatabaseName "NavisionView"
    :setvar DefaultDataPath ""
    :setvar DefaultLogPath ""
    
    GO
    :on error exit
    GO
    USE [master]
    GO
    IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
        AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
    BEGIN
        RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
        RETURN
    END
    
    GO
    IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
    	BEGIN
    		DECLARE @rc      int,                       -- return code
    				@fn      nvarchar(4000),            -- file name to back up to
    				@dir     nvarchar(4000)             -- backup directory
    
    		EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @dir output, 'no_output'
    
    		IF (@dir IS NULL)
    		BEGIN 
    			EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @dir output, 'no_output'
    		END
    
    		IF (@dir IS NULL)
    		BEGIN
    			EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @dir output, 'no_output'
    			SELECT @dir = @dir + N'\Backup'
    		END
    
    		SELECT  @fn = @dir + N'\' + N'$(DatabaseName)' + N'-' + 
    				CONVERT(nchar(8), GETDATE(), 112) + N'-' + 
    				RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(hh, GETDATE()))), 2) + 
    				RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(mi, getdate()))), 2) + 
    				RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(ss, getdate()))), 2) + 
    				N'.bak' 
    				BACKUP DATABASE [$(DatabaseName)] TO DISK = @fn
    	END
    
    GO
    IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE [$(DatabaseName)];
    END
    
    GO
    PRINT N'Creating $(DatabaseName)...'
    GO
    CREATE DATABASE [$(DatabaseName)]
    GO
    ALTER DATABASE [$(DatabaseName)]
        SET SINGLE_USER 
        WITH ROLLBACK IMMEDIATE
    GO
    EXECUTE sp_dbcmptlevel [$(DatabaseName)], 90;
    
    
    GO
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            ALTER DATABASE [$(DatabaseName)]
                SET ANSI_NULLS OFF,
                    ANSI_PADDING ON,
                    ANSI_WARNINGS OFF,
                    ARITHABORT OFF,
                    CONCAT_NULL_YIELDS_NULL OFF,
                    NUMERIC_ROUNDABORT OFF,
                    QUOTED_IDENTIFIER ON,
                    ANSI_NULL_DEFAULT OFF,
                    CURSOR_DEFAULT LOCAL,
                    RECOVERY SIMPLE,
                    CURSOR_CLOSE_ON_COMMIT OFF,
                    AUTO_CREATE_STATISTICS ON,
                    AUTO_SHRINK OFF,
                    AUTO_UPDATE_STATISTICS ON,
                    RECURSIVE_TRIGGERS OFF 
                WITH ROLLBACK IMMEDIATE;
            ALTER DATABASE [$(DatabaseName)]
                SET AUTO_CLOSE OFF 
                WITH ROLLBACK IMMEDIATE;
        END
    
    
    GO
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            ALTER DATABASE [$(DatabaseName)]
                SET ALLOW_SNAPSHOT_ISOLATION OFF;
        END
    
    
    GO
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            ALTER DATABASE [$(DatabaseName)]
                SET READ_COMMITTED_SNAPSHOT OFF;
        END
    
    
    GO
    IF EXISTS (SELECT 1
               FROM   [master].[dbo].[sysdatabases]
               WHERE  [name] = N'$(DatabaseName)')
        BEGIN
            ALTER DATABASE [$(DatabaseName)]
                SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                    PAGE_VERIFY NONE,
                    DATE_CORRELATION_OPTIMIZATION OFF,
                    DISABLE_BROKER,
                    PARAMETERIZATION SIMPLE,
                    SUPPLEMENTAL_LOGGING OFF 
                WITH ROLLBACK IMMEDIATE;
        END
    
    
    GO
    IF IS_SRVROLEMEMBER(N'sysadmin') = 1
        BEGIN
            IF EXISTS (SELECT 1
                       FROM   [master].[dbo].[sysdatabases]
                       WHERE  [name] = N'$(DatabaseName)')
                BEGIN
                    EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
        SET TRUSTWORTHY OFF,
            DB_CHAINING OFF 
        WITH ROLLBACK IMMEDIATE';
                END
        END
    ELSE
        BEGIN
            PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
        END
    
    
    GO
    USE [$(DatabaseName)]
    GO
    IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
        EXECUTE sp_fulltext_database 'disable';
    
    
    GO
    PRINT N'Creating [dbo].[RolePermissions]...';
    
    
    GO
    CREATE TABLE [dbo].[RolePermissions] (
        [RoleName]     NVARCHAR (128) NOT NULL,
        [PermissionId] NVARCHAR (322) NOT NULL
    );
    
    
    GO
    PRINT N'Creating PK_RolePermissions...';
    
    
    GO
    ALTER TABLE [dbo].[RolePermissions]
        ADD CONSTRAINT [PK_RolePermissions] PRIMARY KEY CLUSTERED ([RoleName] ASC, [PermissionId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
    
    
    GO
    PRINT N'Creating DF_RolePermissions_RoleName...';
    
    
    GO
    ALTER TABLE [dbo].[RolePermissions]
        ADD CONSTRAINT [DF_RolePermissions_RoleName] DEFAULT ('') FOR [RoleName];
    
    
    GO
    PRINT N'Creating DF_RolePermissions_PermissionId...';
    
    
    GO
    ALTER TABLE [dbo].[RolePermissions]
        ADD CONSTRAINT [DF_RolePermissions_PermissionId] DEFAULT ('') FOR [PermissionId];
    
    
    GO
    -- Refactoring step to update target server with deployed transaction logs
    CREATE TABLE  [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
    GO
    sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
    GO
    
    GO
    ALTER DATABASE [$(DatabaseName)]
        SET MULTI_USER 
        WITH ROLLBACK IMMEDIATE;
    
    
    GO
    
    /**********************************************************************/
    /* InstallCommon.SQL                                                  */
    /*                                                                    */
    /* Installs the tables, triggers and stored procedures necessary for  */
    /* supporting some features of ASP.Net                                */
    /*
    ** Copyright Microsoft, Inc. 2003
    ** All Rights Reserved.
    */
    /**********************************************************************/
    
    PRINT '---------------------------------------'
    PRINT 'Starting execution of InstallCommon.SQL'
    PRINT '---------------------------------------'
    GO
    
    SET QUOTED_IDENTIFIER OFF
    SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_NULL_DFLT_ON ON
    GO
    
    DECLARE @dbname nvarchar(128)
    DECLARE @dboptions nvarchar(1024)
    
    SET @dboptions = N'/**/'
    SET @dbname = N'$(DatabaseName)'
    
    IF (NOT EXISTS (SELECT name
                    FROM master.dbo.sysdatabases
                    WHERE name = @dbname))
    BEGIN
      PRINT 'Creating the ' + @dbname + ' database...'
      DECLARE @cmd nvarchar(500)
      SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions
      EXEC(@cmd)
    END
    GO
    
    USE [$(DatabaseName)]
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    -- Create the temporary permission tables and stored procedures
    -- TO preserve the permissions of an object.
    --
    -- We use this method instead of using CREATE (if the object
    -- doesn't exist) and ALTER (if the object exists) because the
    -- latter one either requires the use of dynamic SQL (which we want to
    -- avoid) or writing the body of the object (e.g. an SP or view) twice,
    -- once use CREATE and again using ALTER.
    
    
    IF (OBJECT_ID('tempdb.#aspnet_Permissions') IS NOT NULL)
    BEGIN
        DROP TABLE #aspnet_Permissions
    END
    GO
    
    CREATE TABLE #aspnet_Permissions
    (
        Owner     sysname,
        Object    sysname,
        Grantee   sysname,
        Grantor   sysname,
        ProtectType char(10),
        [Action]    varchar(60),
        [Column]    sysname
    )
    
    INSERT INTO #aspnet_Permissions
    EXEC sp_helprotect
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Setup_RestorePermissions')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions
    GO
    
    CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions
        @name   sysname
    AS
    BEGIN
        DECLARE @object sysname
        DECLARE @protectType char(10)
        DECLARE @action varchar(60)
        DECLARE @grantee sysname
        DECLARE @cmd nvarchar(500)
        DECLARE c1 cursor FORWARD_ONLY FOR
            SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name
    
        OPEN c1
    
        FETCH c1 INTO @object, @protectType, @action, @grantee
        WHILE (@@fetch_status = 0)
        BEGIN
            SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
            EXEC (@cmd)
            FETCH c1 INTO @object, @protectType, @action, @grantee
        END
    
        CLOSE c1
        DEALLOCATE c1
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Setup_RemoveAllRoleMembers')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
    GO
    
    CREATE PROCEDURE [dbo].aspnet_Setup_RemoveAllRoleMembers
        @name   sysname
    AS
    BEGIN
        CREATE TABLE #aspnet_RoleMembers
        (
            Group_name      sysname,
            Group_id        smallint,
            Users_in_group  sysname,
            User_id         smallint
        )
    
        INSERT INTO #aspnet_RoleMembers
        EXEC sp_helpuser @name
    
        DECLARE @user_id smallint
        DECLARE @cmd nvarchar(500)
        DECLARE c1 cursor FORWARD_ONLY FOR
            SELECT User_id FROM #aspnet_RoleMembers
    
        OPEN c1
    
        FETCH c1 INTO @user_id
        WHILE (@@fetch_status = 0)
        BEGIN
            SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
            EXEC (@cmd)
            FETCH c1 INTO @user_id
        END
    
        CLOSE c1
        DEALLOCATE c1
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    -- Create the aspnet_Applications table.
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Applications')
                      AND (type = 'U')))
    BEGIN
      PRINT 'Creating the aspnet_Applications table...'
      CREATE TABLE [dbo].aspnet_Applications (
        ApplicationName         nvarchar(256)               NOT NULL UNIQUE,
        LoweredApplicationName  nvarchar(256)               NOT NULL UNIQUE,
        ApplicationId           uniqueidentifier            PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
        Description             nvarchar(256)       )
      CREATE CLUSTERED INDEX aspnet_Applications_Index ON [dbo].aspnet_Applications(LoweredApplicationName)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    -- Create the aspnet_Users table
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Users')
                      AND (type = 'U')))
    BEGIN
      PRINT 'Creating the aspnet_Users table...'
      CREATE TABLE [dbo].aspnet_Users (
        ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES [dbo].aspnet_Applications(ApplicationId),
        UserId           uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
        UserName         nvarchar(256)       NOT NULL,
        LoweredUserName  nvarchar(256)	     NOT NULL,
        MobileAlias      nvarchar(16)        DEFAULT NULL,
        IsAnonymous      bit                 NOT NULL DEFAULT 0,
        LastActivityDate DATETIME            NOT NULL)
    
       CREATE UNIQUE CLUSTERED INDEX aspnet_Users_Index ON [dbo].aspnet_Users(ApplicationId, LoweredUserName)
       CREATE NONCLUSTERED INDEX aspnet_Users_Index2 ON [dbo].aspnet_Users(ApplicationId, LastActivityDate)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    -- Create the aspnet_SchemaVersions table
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_SchemaVersions')
                      AND (type = 'U')))
    BEGIN
      PRINT 'Creating the aspnet_SchemaVersions table...'
      CREATE TABLE [dbo].aspnet_SchemaVersions (
        Feature                  nvarchar(128)  NOT NULL PRIMARY KEY CLUSTERED( Feature, CompatibleSchemaVersion ),
        CompatibleSchemaVersion  nvarchar(128)	NOT NULL,
        IsCurrentVersion         bit            NOT NULL )
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    ------------- Create Stored Procedures
    /*************************************************************/
    /*************************************************************/
    -- RegisterSchemaVersion SP
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_RegisterSchemaVersion')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_RegisterSchemaVersion
    GO
    
    CREATE PROCEDURE [dbo].aspnet_RegisterSchemaVersion
        @Feature                   nvarchar(128),
        @CompatibleSchemaVersion   nvarchar(128),
        @IsCurrentVersion          bit,
        @RemoveIncompatibleSchema  bit
    AS
    BEGIN
        IF( @RemoveIncompatibleSchema = 1 )
        BEGIN
            DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
        END
        ELSE
        BEGIN
            IF( @IsCurrentVersion = 1 )
            BEGIN
                UPDATE dbo.aspnet_SchemaVersions
                SET IsCurrentVersion = 0
                WHERE Feature = LOWER( @Feature )
            END
        END
    
        INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
        VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
    END
    GO
    
    DECLARE @command nvarchar(4000)
    
    SET @command = 'GRANT EXECUTE ON [dbo].aspnet_Setup_RestorePermissions TO ' + QUOTENAME(user)
    EXEC (@command)
    SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
    EXEC (@command)
    GO
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_RegisterSchemaVersion'
    GO
    
    -- Create common schema version
    EXEC [dbo].aspnet_RegisterSchemaVersion N'Common', N'1', 1, 1
    GO
    
    /*************************************************************/
    /*************************************************************/
    -- CheckSchemaVersion SP
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_CheckSchemaVersion')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_CheckSchemaVersion
    GO
    
    CREATE PROCEDURE [dbo].aspnet_CheckSchemaVersion
        @Feature                   nvarchar(128),
        @CompatibleSchemaVersion   nvarchar(128)
    AS
    BEGIN
        IF (EXISTS( SELECT  *
                    FROM    dbo.aspnet_SchemaVersions
                    WHERE   Feature = LOWER( @Feature ) AND
                            CompatibleSchemaVersion = @CompatibleSchemaVersion ))
            RETURN 0
    
        RETURN 1
    END
    GO
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_CheckSchemaVersion'
    GO
    
    /*************************************************************/
    /*************************************************************/
    -- CreateApplication SP
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Applications_CreateApplication')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_Applications_CreateApplication
    GO
    
    CREATE PROCEDURE [dbo].aspnet_Applications_CreateApplication
        @ApplicationName      nvarchar(256),
        @ApplicationId        uniqueidentifier OUTPUT
    AS
    BEGIN
        SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    
        IF(@ApplicationId IS NULL)
        BEGIN
            DECLARE @TranStarted   bit
            SET @TranStarted = 0
    
            IF( @@TRANCOUNT = 0 )
            BEGIN
    	        BEGIN TRANSACTION
    	        SET @TranStarted = 1
            END
            ELSE
        	    SET @TranStarted = 0
    
            SELECT  @ApplicationId = ApplicationId
            FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
            WHERE LOWER(@ApplicationName) = LoweredApplicationName
    
            IF(@ApplicationId IS NULL)
            BEGIN
                SELECT  @ApplicationId = NEWID()
                INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
                VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
            END
    
    
            IF( @TranStarted = 1 )
            BEGIN
                IF(@@ERROR = 0)
                BEGIN
    	        SET @TranStarted = 0
    	        COMMIT TRANSACTION
                END
                ELSE
                BEGIN
                    SET @TranStarted = 0
                    ROLLBACK TRANSACTION
                END
            END
        END
    END
    GO
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Applications_CreateApplication'
    GO
    
    /*************************************************************/
    /*************************************************************/
    -- UnRegisterSchemaVersion SP
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_UnRegisterSchemaVersion')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
    GO
    
    CREATE PROCEDURE [dbo].aspnet_UnRegisterSchemaVersion
        @Feature                   nvarchar(128),
        @CompatibleSchemaVersion   nvarchar(128)
    AS
    BEGIN
        DELETE FROM dbo.aspnet_SchemaVersions
            WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
    END
    GO
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_UnRegisterSchemaVersion'
    GO
    
    /*************************************************************/
    /*************************************************************/
    -- CreateUser SP
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_CreateUser')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_Users_CreateUser
    GO
    
    CREATE PROCEDURE [dbo].aspnet_Users_CreateUser
        @ApplicationId    uniqueidentifier,
        @UserName         nvarchar(256),
        @IsUserAnonymous  bit,
        @LastActivityDate DATETIME,
        @UserId           uniqueidentifier OUTPUT
    AS
    BEGIN
        IF( @UserId IS NULL )
            SELECT @UserId = NEWID()
        ELSE
        BEGIN
            IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
                        WHERE @UserId = UserId ) )
                RETURN -1
        END
    
        INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
        VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
    
        RETURN 0
    END
    GO
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_CreateUser'
    GO
    
    /*************************************************************/
    /*************************************************************/
    --- DeleteUser SP
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_DeleteUser')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_Users_DeleteUser
    GO
    CREATE PROCEDURE [dbo].aspnet_Users_DeleteUser
        @ApplicationName  nvarchar(256),
        @UserName         nvarchar(256),
        @TablesToDeleteFrom int,
        @NumTablesDeletedFrom int OUTPUT
    AS
    BEGIN
        DECLARE @UserId               uniqueidentifier
        SELECT  @UserId               = NULL
        SELECT  @NumTablesDeletedFrom = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
    	    BEGIN TRANSACTION
    	    SET @TranStarted = 1
        END
        ELSE
    	SET @TranStarted = 0
    
        DECLARE @ErrorCode   int
        DECLARE @RowCount    int
    
        SET @ErrorCode = 0
        SET @RowCount  = 0
    
        SELECT  @UserId = u.UserId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a
        WHERE   u.LoweredUserName       = LOWER(@UserName)
            AND u.ApplicationId         = a.ApplicationId
            AND LOWER(@ApplicationName) = a.LoweredApplicationName
    
        IF (@UserId IS NULL)
        BEGIN
            GOTO Cleanup
        END
    
        -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
        IF ((@TablesToDeleteFrom & 1) <> 0 AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
        BEGIN
            DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
    
            SELECT @ErrorCode = @@ERROR,
                   @RowCount = @@ROWCOUNT
    
            IF( @ErrorCode <> 0 )
                GOTO Cleanup
    
            IF (@RowCount <> 0)
                SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
        END
    
        -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
        IF ((@TablesToDeleteFrom & 2) <> 0  AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
        BEGIN
            DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
    
            SELECT @ErrorCode = @@ERROR,
                    @RowCount = @@ROWCOUNT
    
            IF( @ErrorCode <> 0 )
                GOTO Cleanup
    
            IF (@RowCount <> 0)
                SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
        END
    
        -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
        IF ((@TablesToDeleteFrom & 4) <> 0  AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
        BEGIN
            DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
    
            SELECT @ErrorCode = @@ERROR,
                    @RowCount = @@ROWCOUNT
    
            IF( @ErrorCode <> 0 )
                GOTO Cleanup
    
            IF (@RowCount <> 0)
                SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
        END
    
        -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
        IF ((@TablesToDeleteFrom & 8) <> 0  AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
        BEGIN
            DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
    
            SELECT @ErrorCode = @@ERROR,
                    @RowCount = @@ROWCOUNT
    
            IF( @ErrorCode <> 0 )
                GOTO Cleanup
    
            IF (@RowCount <> 0)
                SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
        END
    
        -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
        IF ((@TablesToDeleteFrom & 1) <> 0 AND
            (@TablesToDeleteFrom & 2) <> 0 AND
            (@TablesToDeleteFrom & 4) <> 0 AND
            (@TablesToDeleteFrom & 8) <> 0 AND
            (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
        BEGIN
            DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
    
            SELECT @ErrorCode = @@ERROR,
                    @RowCount = @@ROWCOUNT
    
            IF( @ErrorCode <> 0 )
                GOTO Cleanup
    
            IF (@RowCount <> 0)
                SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
        END
    
        IF( @TranStarted = 1 )
        BEGIN
    	    SET @TranStarted = 0
    	    COMMIT TRANSACTION
        END
    
        RETURN 0
    
    Cleanup:
        SET @NumTablesDeletedFrom = 0
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
    	    ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_DeleteUser'
    GO
    
    /*************************************************************/
    /*************************************************************/
    --- aspnet_AnyDataInTables SP
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_AnyDataInTables')
                   AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_AnyDataInTables
    GO
    CREATE PROCEDURE [dbo].aspnet_AnyDataInTables
        @TablesToCheck int
    AS
    BEGIN
        -- Check Membership table if (@TablesToCheck & 1) is set
        IF ((@TablesToCheck & 1) <> 0 AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
        BEGIN
            IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
            BEGIN
                SELECT N'aspnet_Membership'
                RETURN
            END
        END
    
        -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
        IF ((@TablesToCheck & 2) <> 0  AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
        BEGIN
            IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
            BEGIN
                SELECT N'aspnet_Roles'
                RETURN
            END
        END
    
        -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
        IF ((@TablesToCheck & 4) <> 0  AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
        BEGIN
            IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
            BEGIN
                SELECT N'aspnet_Profile'
                RETURN
            END
        END
    
        -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
        IF ((@TablesToCheck & 8) <> 0  AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
        BEGIN
            IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
            BEGIN
                SELECT N'aspnet_PersonalizationPerUser'
                RETURN
            END
        END
    
        -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
        IF ((@TablesToCheck & 16) <> 0  AND
            (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
        BEGIN
            IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
            BEGIN
                SELECT N'aspnet_WebEvent_Events'
                RETURN
            END
        END
    
        -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
        IF ((@TablesToCheck & 1) <> 0 AND
            (@TablesToCheck & 2) <> 0 AND
            (@TablesToCheck & 4) <> 0 AND
            (@TablesToCheck & 8) <> 0 AND
            (@TablesToCheck & 32) <> 0 AND
            (@TablesToCheck & 128) <> 0 AND
            (@TablesToCheck & 256) <> 0 AND
            (@TablesToCheck & 512) <> 0 AND
            (@TablesToCheck & 1024) <> 0)
        BEGIN
            IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
            BEGIN
                SELECT N'aspnet_Users'
                RETURN
            END
            IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
            BEGIN
                SELECT N'aspnet_Applications'
                RETURN
            END
        END
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    DECLARE @command nvarchar(400)
    SET @command = 'GRANT EXECUTE ON [dbo].aspnet_AnyDataInTables TO ' + QUOTENAME(user)
    EXEC (@command)
    GO
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_AnyDataInTables'
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'vw_aspnet_Applications')
                      AND (type = 'V')))
    BEGIN
      PRINT 'Creating the vw_aspnet_Applications view...'
      EXEC('
      CREATE VIEW [dbo].[vw_aspnet_Applications]
      AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
      FROM [dbo].[aspnet_Applications]
      ')
    END
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Applications'
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'vw_aspnet_Users')
                      AND (type = 'V')))
    BEGIN
      PRINT 'Creating the vw_aspnet_Users view...'
      EXEC('
      CREATE VIEW [dbo].[vw_aspnet_Users]
      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]
      FROM [dbo].[aspnet_Users]
      ')
    END
    
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'vw_aspnet_Users'
    GO
    
    /*************************************************************/
    /*************************************************************/
    DECLARE @command nvarchar(4000)
    
    SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_Setup_RestorePermissions from ' + QUOTENAME(user)
    EXEC (@command)
    SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion from ' + QUOTENAME(user)
    EXEC (@command)
    GO
    
    DROP TABLE #aspnet_Permissions
    GO
    
    PRINT '----------------------------------------'
    PRINT 'Completed execution of InstallCommon.SQL'
    PRINT '----------------------------------------'
    /**********************************************************************/
    /* InstallMembership.SQL                                              */
    /*                                                                    */
    /* Installs the tables, triggers and stored procedures necessary for  */
    /* supporting the aspnet feature of ASP.Net                           */
    /*                                                                    */
    /* InstallCommon.sql must be run before running this file.            */
    /*
    ** Copyright Microsoft, Inc. 2002
    ** All Rights Reserved.
    */
    /**********************************************************************/
    
    PRINT '-------------------------------------------'
    PRINT 'Starting execution of InstallMembership.SQL'
    PRINT '-------------------------------------------'
    GO
    
    SET QUOTED_IDENTIFIER OFF
    SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_NULL_DFLT_ON ON
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    DECLARE @dbname nvarchar(128)
    
    SET @dbname = N'$(DatabaseName)'
    
    IF (NOT EXISTS (SELECT name
                    FROM master.dbo.sysdatabases
                    WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
    BEGIN
      RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
    END
    GO
    
    USE [$(DatabaseName)]
    GO
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Applications')
                      AND (type = 'U')))
    BEGIN
      RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Users')
                      AND (type = 'U')))
    BEGIN
      RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Applications_CreateApplication')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_CreateUser')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_DeleteUser')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    /*************************************************************/
    /*************************************************************/
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Membership')
                      AND (type = 'U')))
    BEGIN
      PRINT 'Creating the aspnet_Membership table...'
      CREATE TABLE dbo.aspnet_Membership (
            ApplicationId                           uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
            UserId                                  uniqueidentifier    NOT NULL PRIMARY KEY NONCLUSTERED FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),
            Password                                nvarchar(128)       NOT NULL,
            PasswordFormat                          int                 NOT NULL DEFAULT 0,
            PasswordSalt                            nvarchar(128)       NOT NULL,
            MobilePIN                               nvarchar(16),
            Email                                   nvarchar(256),
            LoweredEmail                            nvarchar(256),
            PasswordQuestion                        nvarchar(256),
            PasswordAnswer                          nvarchar(128),
            IsApproved                              bit                 NOT NULL,
            IsLockedOut                             bit                 NOT NULL,
            CreateDate                              datetime            NOT NULL,
            LastLoginDate                           datetime            NOT NULL,
            LastPasswordChangedDate                 datetime            NOT NULL,
            LastLockoutDate                         datetime            NOT NULL,
            FailedPasswordAttemptCount              int                 NOT NULL,
            FailedPasswordAttemptWindowStart        datetime            NOT NULL,
            FailedPasswordAnswerAttemptCount        int                 NOT NULL,
            FailedPasswordAnswerAttemptWindowStart  datetime            NOT NULL,
            Comment                                 ntext )
      CREATE CLUSTERED INDEX aspnet_Membership_index ON aspnet_Membership(ApplicationId, LoweredEmail)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    DECLARE @ver int
    DECLARE @version nchar(100)
    DECLARE @dot int
    DECLARE @hyphen int
    DECLARE @SqlToExec nchar(400)
    
    SELECT @ver = 8
    SELECT @version = @@Version
    SELECT @hyphen  = CHARINDEX(N' - ', @version)
    IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
    BEGIN
        SELECT @hyphen = @hyphen + 3
        SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
        IF (NOT(@dot IS NULL) AND @dot > @hyphen)
        BEGIN
            SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
            SELECT @ver     = CONVERT(int, @version)
        END
    END
    
    /*************************************************************/
    
    IF (@ver >= 8)
        EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_CreateUser')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_CreateUser
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_CreateUser
        @ApplicationName                        nvarchar(256),
        @UserName                               nvarchar(256),
        @Password                               nvarchar(128),
        @PasswordSalt                           nvarchar(128),
        @Email                                  nvarchar(256),
        @PasswordQuestion                       nvarchar(256),
        @PasswordAnswer                         nvarchar(128),
        @IsApproved                             bit,
        @CurrentTimeUtc                         datetime,
        @CreateDate                             datetime = NULL,
        @UniqueEmail                            int      = 0,
        @PasswordFormat                         int      = 0,
        @UserId                                 uniqueidentifier OUTPUT
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
    
        DECLARE @NewUserId uniqueidentifier
        SELECT @NewUserId = NULL
    
        DECLARE @IsLockedOut bit
        SET @IsLockedOut = 0
    
        DECLARE @LastLockoutDate  datetime
        SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
    
        DECLARE @FailedPasswordAttemptCount int
        SET @FailedPasswordAttemptCount = 0
    
        DECLARE @FailedPasswordAttemptWindowStart  datetime
        SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
    
        DECLARE @FailedPasswordAnswerAttemptCount int
        SET @FailedPasswordAnswerAttemptCount = 0
    
        DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
        SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
    
        DECLARE @NewUserCreated bit
        DECLARE @ReturnValue   int
        SET @ReturnValue = 0
    
        DECLARE @ErrorCode     int
        SET @ErrorCode = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
    	    BEGIN TRANSACTION
    	    SET @TranStarted = 1
        END
        ELSE
        	SET @TranStarted = 0
    
        EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        SET @CreateDate = @CurrentTimeUtc
    
        SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
        IF ( @NewUserId IS NULL )
        BEGIN
            SET @NewUserId = @UserId
            EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
            SET @NewUserCreated = 1
        END
        ELSE
        BEGIN
            SET @NewUserCreated = 0
            IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
            BEGIN
                SET @ErrorCode = 6
                GOTO Cleanup
            END
        END
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF( @ReturnValue = -1 )
        BEGIN
            SET @ErrorCode = 10
            GOTO Cleanup
        END
    
        IF ( EXISTS ( SELECT UserId
                      FROM   dbo.aspnet_Membership
                      WHERE  @NewUserId = UserId ) )
        BEGIN
            SET @ErrorCode = 6
            GOTO Cleanup
        END
    
        SET @UserId = @NewUserId
    
        IF (@UniqueEmail = 1)
        BEGIN
            IF (EXISTS (SELECT *
                        FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
                        WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
            BEGIN
                SET @ErrorCode = 7
                GOTO Cleanup
            END
        END
    
        IF (@NewUserCreated = 0)
        BEGIN
            UPDATE dbo.aspnet_Users
            SET    LastActivityDate = @CreateDate
            WHERE  @UserId = UserId
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
        END
    
        INSERT INTO dbo.aspnet_Membership
                    ( ApplicationId,
                      UserId,
                      Password,
                      PasswordSalt,
                      Email,
                      LoweredEmail,
                      PasswordQuestion,
                      PasswordAnswer,
                      PasswordFormat,
                      IsApproved,
                      IsLockedOut,
                      CreateDate,
                      LastLoginDate,
                      LastPasswordChangedDate,
                      LastLockoutDate,
                      FailedPasswordAttemptCount,
                      FailedPasswordAttemptWindowStart,
                      FailedPasswordAnswerAttemptCount,
                      FailedPasswordAnswerAttemptWindowStart )
             VALUES ( @ApplicationId,
                      @UserId,
                      @Password,
                      @PasswordSalt,
                      @Email,
                      LOWER(@Email),
                      @PasswordQuestion,
                      @PasswordAnswer,
                      @PasswordFormat,
                      @IsApproved,
                      @IsLockedOut,
                      @CreateDate,
                      @CreateDate,
                      @CreateDate,
                      @LastLockoutDate,
                      @FailedPasswordAttemptCount,
                      @FailedPasswordAttemptWindowStart,
                      @FailedPasswordAnswerAttemptCount,
                      @FailedPasswordAnswerAttemptWindowStart )
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF( @TranStarted = 1 )
        BEGIN
    	    SET @TranStarted = 0
    	    COMMIT TRANSACTION
        END
    
        RETURN 0
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
        	ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    
    END
    GO
    

    Wednesday, July 16, 2014 8:51 AM
  • /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_GetUserByName')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_GetUserByName
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName
        @ApplicationName      nvarchar(256),
        @UserName             nvarchar(256),
        @CurrentTimeUtc       datetime,
        @UpdateLastActivity   bit = 0
    AS
    BEGIN
        DECLARE @UserId uniqueidentifier
    
        IF (@UpdateLastActivity = 1)
        BEGIN
            -- select user ID from aspnet_users table
            SELECT TOP 1 @UserId = u.UserId
            FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                    u.ApplicationId = a.ApplicationId    AND
                    LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
    
            IF (@@ROWCOUNT = 0) -- Username not found
                RETURN -1
    
            UPDATE   dbo.aspnet_Users
            SET      LastActivityDate = @CurrentTimeUtc
            WHERE    @UserId = UserId
    
            SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                    m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                    u.UserId, m.IsLockedOut, m.LastLockoutDate
            FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
        END
        ELSE
        BEGIN
            SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                    m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                    u.UserId, m.IsLockedOut,m.LastLockoutDate
            FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                    u.ApplicationId = a.ApplicationId    AND
                    LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
    
            IF (@@ROWCOUNT = 0) -- Username not found
                RETURN -1
        END
    
        RETURN 0
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_GetUserByUserId')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_GetUserByUserId
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_GetUserByUserId
        @UserId               uniqueidentifier,
        @CurrentTimeUtc       datetime,
        @UpdateLastActivity   bit = 0
    AS
    BEGIN
        IF ( @UpdateLastActivity = 1 )
        BEGIN
            UPDATE   dbo.aspnet_Users
            SET      LastActivityDate = @CurrentTimeUtc
            FROM     dbo.aspnet_Users
            WHERE    @UserId = UserId
    
            IF ( @@ROWCOUNT = 0 ) -- User ID not found
                RETURN -1
        END
    
        SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate,
                m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
                m.LastLockoutDate
        FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   @UserId = u.UserId AND u.UserId = m.UserId
    
        IF ( @@ROWCOUNT = 0 ) -- User ID not found
           RETURN -1
    
        RETURN 0
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_GetUserByEmail')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_GetUserByEmail
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_GetUserByEmail
        @ApplicationName  nvarchar(256),
        @Email            nvarchar(256)
    AS
    BEGIN
        IF( @Email IS NULL )
            SELECT  u.UserName
            FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                    u.ApplicationId = a.ApplicationId    AND
                    u.UserId = m.UserId AND
                    m.LoweredEmail IS NULL
        ELSE
            SELECT  u.UserName
            FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                    u.ApplicationId = a.ApplicationId    AND
                    u.UserId = m.UserId AND
                    LOWER(@Email) = m.LoweredEmail
    
        IF (@@rowcount = 0)
            RETURN(1)
        RETURN(0)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF ( EXISTS( SELECT name
                 FROM sysobjects
                 WHERE ( name = N'aspnet_Membership_GetPasswordWithFormat' )
                       AND ( type = 'P' ) ) )
    DROP PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
        @ApplicationName                nvarchar(256),
        @UserName                       nvarchar(256),
        @UpdateLastLoginActivityDate    bit,
        @CurrentTimeUtc                 datetime
    AS
    BEGIN
        DECLARE @IsLockedOut                        bit
        DECLARE @UserId                             uniqueidentifier
        DECLARE @Password                           nvarchar(128)
        DECLARE @PasswordSalt                       nvarchar(128)
        DECLARE @PasswordFormat                     int
        DECLARE @FailedPasswordAttemptCount         int
        DECLARE @FailedPasswordAnswerAttemptCount   int
        DECLARE @IsApproved                         bit
        DECLARE @LastActivityDate                   datetime
        DECLARE @LastLoginDate                      datetime
    
        SELECT  @UserId          = NULL
    
        SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
                @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
    		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
                @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@UserName) = u.LoweredUserName
    
        IF (@UserId IS NULL)
            RETURN 1
    
        IF (@IsLockedOut = 1)
            RETURN 99
    
        SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
                 @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate
    
        IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
        BEGIN
            UPDATE  dbo.aspnet_Membership
            SET     LastLoginDate = @CurrentTimeUtc
            WHERE   UserId = @UserId
    
            UPDATE  dbo.aspnet_Users
            SET     LastActivityDate = @CurrentTimeUtc
            WHERE   @UserId = UserId
        END
    
    
        RETURN 0
    END
    GO
    /*************************************************************/
    /*************************************************************/
    
    IF ( EXISTS( SELECT name
                 FROM sysobjects
                 WHERE ( name = N'aspnet_Membership_UpdateUserInfo' )
                       AND ( type = 'P' ) ) )
    DROP PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
        @ApplicationName                nvarchar(256),
        @UserName                       nvarchar(256),
        @IsPasswordCorrect              bit,
        @UpdateLastLoginActivityDate    bit,
        @MaxInvalidPasswordAttempts     int,
        @PasswordAttemptWindow          int,
        @CurrentTimeUtc                 datetime,
        @LastLoginDate                  datetime,
        @LastActivityDate               datetime
    AS
    BEGIN
        DECLARE @UserId                                 uniqueidentifier
        DECLARE @IsApproved                             bit
        DECLARE @IsLockedOut                            bit
        DECLARE @LastLockoutDate                        datetime
        DECLARE @FailedPasswordAttemptCount             int
        DECLARE @FailedPasswordAttemptWindowStart       datetime
        DECLARE @FailedPasswordAnswerAttemptCount       int
        DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
    
        DECLARE @ErrorCode     int
        SET @ErrorCode = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
    	    BEGIN TRANSACTION
    	    SET @TranStarted = 1
        END
        ELSE
        	SET @TranStarted = 0
    
        SELECT  @UserId = u.UserId,
                @IsApproved = m.IsApproved,
                @IsLockedOut = m.IsLockedOut,
                @LastLockoutDate = m.LastLockoutDate,
                @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
                @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
                @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
                @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@UserName) = u.LoweredUserName
    
        IF ( @@rowcount = 0 )
        BEGIN
            SET @ErrorCode = 1
            GOTO Cleanup
        END
    
        IF( @IsLockedOut = 1 )
        BEGIN
            GOTO Cleanup
        END
    
        IF( @IsPasswordCorrect = 0 )
        BEGIN
            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
            END
    
            BEGIN
                IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @CurrentTimeUtc
                END
            END
        END
        ELSE
        BEGIN
            IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
            BEGIN
                SET @FailedPasswordAttemptCount = 0
                SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
                SET @FailedPasswordAnswerAttemptCount = 0
                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
                SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
            END
        END
    
        IF( @UpdateLastLoginActivityDate = 1 )
        BEGIN
            UPDATE  dbo.aspnet_Users
            SET     LastActivityDate = @LastActivityDate
            WHERE   @UserId = UserId
    
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
    
            UPDATE  dbo.aspnet_Membership
            SET     LastLoginDate = @LastLoginDate
            WHERE   UserId = @UserId
    
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
        END
    
    
        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
        WHERE @UserId = UserId
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF( @TranStarted = 1 )
        BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
        END
    
        RETURN @ErrorCode
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
        	ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_GetPassword')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_GetPassword
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_GetPassword
        @ApplicationName                nvarchar(256),
        @UserName                       nvarchar(256),
        @MaxInvalidPasswordAttempts     int,
        @PasswordAttemptWindow          int,
        @CurrentTimeUtc                 datetime,
        @PasswordAnswer                 nvarchar(128) = NULL
    AS
    BEGIN
        DECLARE @UserId                                 uniqueidentifier
        DECLARE @PasswordFormat                         int
        DECLARE @Password                               nvarchar(128)
        DECLARE @passAns                                nvarchar(128)
        DECLARE @IsLockedOut                            bit
        DECLARE @LastLockoutDate                        datetime
        DECLARE @FailedPasswordAttemptCount             int
        DECLARE @FailedPasswordAttemptWindowStart       datetime
        DECLARE @FailedPasswordAnswerAttemptCount       int
        DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
    
        DECLARE @ErrorCode     int
        SET @ErrorCode = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
    	    BEGIN TRANSACTION
    	    SET @TranStarted = 1
        END
        ELSE
        	SET @TranStarted = 0
    
        SELECT  @UserId = u.UserId,
                @Password = m.Password,
                @passAns = m.PasswordAnswer,
                @PasswordFormat = m.PasswordFormat,
                @IsLockedOut = m.IsLockedOut,
                @LastLockoutDate = m.LastLockoutDate,
                @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
                @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
                @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
                @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@UserName) = u.LoweredUserName
    
        IF ( @@rowcount = 0 )
        BEGIN
            SET @ErrorCode = 1
            GOTO Cleanup
        END
    
        IF( @IsLockedOut = 1 )
        BEGIN
            SET @ErrorCode = 99
            GOTO Cleanup
        END
    
        IF ( NOT( @PasswordAnswer IS NULL ) )
        BEGIN
            IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
            BEGIN
                IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
                BEGIN
                    SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                    SET @FailedPasswordAnswerAttemptCount = 1
                END
                ELSE
                BEGIN
                    SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                    SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                END
    
                BEGIN
                    IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                    BEGIN
                        SET @IsLockedOut = 1
                        SET @LastLockoutDate = @CurrentTimeUtc
                    END
                END
    
                SET @ErrorCode = 3
            END
            ELSE
            BEGIN
                IF( @FailedPasswordAnswerAttemptCount > 0 )
                BEGIN
                    SET @FailedPasswordAnswerAttemptCount = 0
                    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
                END
            END
    
            UPDATE dbo.aspnet_Membership
            SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
                FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
                FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
                FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
                FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
            WHERE @UserId = UserId
    
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
        END
    
        IF( @TranStarted = 1 )
        BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
        END
    
        IF( @ErrorCode = 0 )
            SELECT @Password, @PasswordFormat
    
        RETURN @ErrorCode
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
        	ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_SetPassword')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_SetPassword
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_SetPassword
        @ApplicationName  nvarchar(256),
        @UserName         nvarchar(256),
        @NewPassword      nvarchar(128),
        @PasswordSalt     nvarchar(128),
        @CurrentTimeUtc   datetime,
        @PasswordFormat   int = 0
    AS
    BEGIN
        DECLARE @UserId uniqueidentifier
        SELECT  @UserId = NULL
        SELECT  @UserId = u.UserId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
        WHERE   LoweredUserName = LOWER(@UserName) AND
                u.ApplicationId = a.ApplicationId  AND
                LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.UserId = m.UserId
    
        IF (@UserId IS NULL)
            RETURN(1)
    
        UPDATE dbo.aspnet_Membership
        SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
            LastPasswordChangedDate = @CurrentTimeUtc
        WHERE @UserId = UserId
        RETURN(0)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_ResetPassword')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_ResetPassword
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_ResetPassword
        @ApplicationName             nvarchar(256),
        @UserName                    nvarchar(256),
        @NewPassword                 nvarchar(128),
        @MaxInvalidPasswordAttempts  int,
        @PasswordAttemptWindow       int,
        @PasswordSalt                nvarchar(128),
        @CurrentTimeUtc              datetime,
        @PasswordFormat              int = 0,
        @PasswordAnswer              nvarchar(128) = NULL
    AS
    BEGIN
        DECLARE @IsLockedOut                            bit
        DECLARE @LastLockoutDate                        datetime
        DECLARE @FailedPasswordAttemptCount             int
        DECLARE @FailedPasswordAttemptWindowStart       datetime
        DECLARE @FailedPasswordAnswerAttemptCount       int
        DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
    
        DECLARE @UserId                                 uniqueidentifier
        SET     @UserId = NULL
    
        DECLARE @ErrorCode     int
        SET @ErrorCode = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
    	    BEGIN TRANSACTION
    	    SET @TranStarted = 1
        END
        ELSE
        	SET @TranStarted = 0
    
        SELECT  @UserId = u.UserId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
        WHERE   LoweredUserName = LOWER(@UserName) AND
                u.ApplicationId = a.ApplicationId  AND
                LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.UserId = m.UserId
    
        IF ( @UserId IS NULL )
        BEGIN
            SET @ErrorCode = 1
            GOTO Cleanup
        END
    
        SELECT @IsLockedOut = IsLockedOut,
               @LastLockoutDate = LastLockoutDate,
               @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
               @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
               @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
               @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
        FROM dbo.aspnet_Membership WITH ( UPDLOCK )
        WHERE @UserId = UserId
    
        IF( @IsLockedOut = 1 )
        BEGIN
            SET @ErrorCode = 99
            GOTO Cleanup
        END
    
        UPDATE dbo.aspnet_Membership
        SET    Password = @NewPassword,
               LastPasswordChangedDate = @CurrentTimeUtc,
               PasswordFormat = @PasswordFormat,
               PasswordSalt = @PasswordSalt
        WHERE  @UserId = UserId AND
               ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
    
        IF ( @@ROWCOUNT = 0 )
            BEGIN
                IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
                BEGIN
                    SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                    SET @FailedPasswordAnswerAttemptCount = 1
                END
                ELSE
                BEGIN
                    SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                    SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                END
    
                BEGIN
                    IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                    BEGIN
                        SET @IsLockedOut = 1
                        SET @LastLockoutDate = @CurrentTimeUtc
                    END
                END
    
                SET @ErrorCode = 3
            END
        ELSE
            BEGIN
                IF( @FailedPasswordAnswerAttemptCount > 0 )
                BEGIN
                    SET @FailedPasswordAnswerAttemptCount = 0
                    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
                END
            END
    
        IF( NOT ( @PasswordAnswer IS NULL ) )
        BEGIN
            UPDATE dbo.aspnet_Membership
            SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
                FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
                FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
                FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
                FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
            WHERE @UserId = UserId
    
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
        END
    
        IF( @TranStarted = 1 )
        BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
        END
    
        RETURN @ErrorCode
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
        	ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_UnlockUser')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_UnlockUser
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_UnlockUser
        @ApplicationName                         nvarchar(256),
        @UserName                                nvarchar(256)
    AS
    BEGIN
        DECLARE @UserId uniqueidentifier
        SELECT  @UserId = NULL
        SELECT  @UserId = u.UserId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
        WHERE   LoweredUserName = LOWER(@UserName) AND
                u.ApplicationId = a.ApplicationId  AND
                LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.UserId = m.UserId
    
        IF ( @UserId IS NULL )
            RETURN 1
    
        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = 0,
            FailedPasswordAttemptCount = 0,
            FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
            FailedPasswordAnswerAttemptCount = 0,
            FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
            LastLockoutDate = CONVERT( datetime, '17540101', 112 )
        WHERE @UserId = UserId
    
        RETURN 0
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_UpdateUser')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_UpdateUser
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_UpdateUser
        @ApplicationName      nvarchar(256),
        @UserName             nvarchar(256),
        @Email                nvarchar(256),
        @Comment              ntext,
        @IsApproved           bit,
        @LastLoginDate        datetime,
        @LastActivityDate     datetime,
        @UniqueEmail          int,
        @CurrentTimeUtc       datetime
    AS
    BEGIN
        DECLARE @UserId uniqueidentifier
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @UserId = NULL
        SELECT  @UserId = u.UserId, @ApplicationId = a.ApplicationId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
        WHERE   LoweredUserName = LOWER(@UserName) AND
                u.ApplicationId = a.ApplicationId  AND
                LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.UserId = m.UserId
    
        IF (@UserId IS NULL)
            RETURN(1)
    
        IF (@UniqueEmail = 1)
        BEGIN
            IF (EXISTS (SELECT *
                        FROM  dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
                        WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
            BEGIN
                RETURN(7)
            END
        END
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
    	    BEGIN TRANSACTION
    	    SET @TranStarted = 1
        END
        ELSE
    	SET @TranStarted = 0
    
        UPDATE dbo.aspnet_Users WITH (ROWLOCK)
        SET
             LastActivityDate = @LastActivityDate
        WHERE
           @UserId = UserId
    
        IF( @@ERROR <> 0 )
            GOTO Cleanup
    
        UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
        SET
             Email            = @Email,
             LoweredEmail     = LOWER(@Email),
             Comment          = @Comment,
             IsApproved       = @IsApproved,
             LastLoginDate    = @LastLoginDate
        WHERE
           @UserId = UserId
    
        IF( @@ERROR <> 0 )
            GOTO Cleanup
    
        IF( @TranStarted = 1 )
        BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
        END
    
        RETURN 0
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
        	ROLLBACK TRANSACTION
        END
    
        RETURN -1
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_ChangePasswordQuestionAndAnswer')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer
        @ApplicationName       nvarchar(256),
        @UserName              nvarchar(256),
        @NewPasswordQuestion   nvarchar(256),
        @NewPasswordAnswer     nvarchar(128)
    AS
    BEGIN
        DECLARE @UserId uniqueidentifier
        SELECT  @UserId = NULL
        SELECT  @UserId = u.UserId
        FROM    dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
        WHERE   LoweredUserName = LOWER(@UserName) AND
                u.ApplicationId = a.ApplicationId  AND
                LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.UserId = m.UserId
        IF (@UserId IS NULL)
        BEGIN
            RETURN(1)
        END
    
        UPDATE dbo.aspnet_Membership
        SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
        WHERE  UserId=@UserId
        RETURN(0)
    END
    GO
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_GetAllUsers')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_GetAllUsers
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers
        @ApplicationName       nvarchar(256),
        @PageIndex             int,
        @PageSize              int
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN 0
    
    
        -- Set the page bounds
        DECLARE @PageLowerBound int
        DECLARE @PageUpperBound int
        DECLARE @TotalRecords   int
        SET @PageLowerBound = @PageSize * @PageIndex
        SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
    
        -- Create a temp table TO store the select results
        CREATE TABLE #PageIndexForUsers
        (
            IndexId int IDENTITY (0, 1) NOT NULL,
            UserId uniqueidentifier
        )
    
        -- Insert into our temp table
        INSERT INTO #PageIndexForUsers (UserId)
        SELECT u.UserId
        FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
        WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
        ORDER BY u.UserName
    
        SELECT @TotalRecords = @@ROWCOUNT
    
        SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate,
                m.LastLoginDate,
                u.LastActivityDate,
                m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,
                m.LastLockoutDate
        FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
        WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
               p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
        ORDER BY u.UserName
        RETURN @TotalRecords
    END
    GO
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_GetNumberOfUsersOnline')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
        @ApplicationName            nvarchar(256),
        @MinutesSinceLastInActive   int,
        @CurrentTimeUtc             datetime
    AS
    BEGIN
        DECLARE @DateActive datetime
        SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)
    
        DECLARE @NumOnline int
        SELECT  @NumOnline = COUNT(*)
        FROM    dbo.aspnet_Users u(NOLOCK),
                dbo.aspnet_Applications a(NOLOCK),
                dbo.aspnet_Membership m(NOLOCK)
        WHERE   u.ApplicationId = a.ApplicationId                  AND
                LastActivityDate > @DateActive                     AND
                a.LoweredApplicationName = LOWER(@ApplicationName) AND
                u.UserId = m.UserId
        RETURN(@NumOnline)
    END
    GO
    
    
    /*************************************************************/
    /*************************************************************/
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_FindUsersByName')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_FindUsersByName
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByName
        @ApplicationName       nvarchar(256),
        @UserNameToMatch       nvarchar(256),
        @PageIndex             int,
        @PageSize              int
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN 0
    
        -- Set the page bounds
        DECLARE @PageLowerBound int
        DECLARE @PageUpperBound int
        DECLARE @TotalRecords   int
        SET @PageLowerBound = @PageSize * @PageIndex
        SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
    
        -- Create a temp table TO store the select results
        CREATE TABLE #PageIndexForUsers
        (
            IndexId int IDENTITY (0, 1) NOT NULL,
            UserId uniqueidentifier
        )
    
        -- Insert into our temp table
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT u.UserId
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
            ORDER BY u.UserName
    
    
        SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate,
                m.LastLoginDate,
                u.LastActivityDate,
                m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,
                m.LastLockoutDate
        FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
        WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
               p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
        ORDER BY u.UserName
    
        SELECT  @TotalRecords = COUNT(*)
        FROM    #PageIndexForUsers
        RETURN @TotalRecords
    END
    GO
    /*************************************************************/
    /*************************************************************/
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Membership_FindUsersByEmail')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
    GO
    CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByEmail
        @ApplicationName       nvarchar(256),
        @EmailToMatch          nvarchar(256),
        @PageIndex             int,
        @PageSize              int
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN 0
    
        -- Set the page bounds
        DECLARE @PageLowerBound int
        DECLARE @PageUpperBound int
        DECLARE @TotalRecords   int
        SET @PageLowerBound = @PageSize * @PageIndex
        SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
    
        -- Create a temp table TO store the select results
        CREATE TABLE #PageIndexForUsers
        (
            IndexId int IDENTITY (0, 1) NOT NULL,
            UserId uniqueidentifier
        )
    
        -- Insert into our temp table
        IF( @EmailToMatch IS NULL )
            INSERT INTO #PageIndexForUsers (UserId)
                SELECT u.UserId
                FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
                WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
                ORDER BY m.LoweredEmail
        ELSE
            INSERT INTO #PageIndexForUsers (UserId)
                SELECT u.UserId
                FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
                WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
                ORDER BY m.LoweredEmail
    
        SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate,
                m.LastLoginDate,
                u.LastActivityDate,
                m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,
                m.LastLockoutDate
        FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
        WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
               p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
        ORDER BY m.LoweredEmail
    
        SELECT  @TotalRecords = COUNT(*)
        FROM    #PageIndexForUsers
        RETURN @TotalRecords
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'vw_aspnet_MembershipUsers')
                      AND (type = 'V')))
    BEGIN
      PRINT 'Creating the vw_aspnet_MembershipUsers view...'
      EXEC('
      CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
      AS SELECT [dbo].[aspnet_Membership].[UserId],
                [dbo].[aspnet_Membership].[PasswordFormat],
                [dbo].[aspnet_Membership].[MobilePIN],
                [dbo].[aspnet_Membership].[Email],
                [dbo].[aspnet_Membership].[LoweredEmail],
                [dbo].[aspnet_Membership].[PasswordQuestion],
                [dbo].[aspnet_Membership].[PasswordAnswer],
                [dbo].[aspnet_Membership].[IsApproved],
                [dbo].[aspnet_Membership].[IsLockedOut],
                [dbo].[aspnet_Membership].[CreateDate],
                [dbo].[aspnet_Membership].[LastLoginDate],
                [dbo].[aspnet_Membership].[LastPasswordChangedDate],
                [dbo].[aspnet_Membership].[LastLockoutDate],
                [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
                [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
                [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
                [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
                [dbo].[aspnet_Membership].[Comment],
                [dbo].[aspnet_Users].[ApplicationId],
                [dbo].[aspnet_Users].[UserName],
                [dbo].[aspnet_Users].[MobileAlias],
                [dbo].[aspnet_Users].[IsAnonymous],
                [dbo].[aspnet_Users].[LastActivityDate]
      FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
          ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]
      ')
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    --
    --Create Membership schema version
    --
    DECLARE @command nvarchar(4000)
    SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
    EXECUTE (@command)
    GO
    
    EXEC [dbo].aspnet_RegisterSchemaVersion N'Membership', N'1', 1, 1
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    --
    --Create Membership roles
    --
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Membership_FullAccess'  ) )
    EXEC sp_addrole N'aspnet_Membership_FullAccess'
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Membership_BasicAccess'  ) )
    EXEC sp_addrole N'aspnet_Membership_BasicAccess'
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Membership_ReportingAccess'  ) )
    EXEC sp_addrole N'aspnet_Membership_ReportingAccess'
    GO
    
    EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'aspnet_Membership_FullAccess'
    EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'aspnet_Membership_FullAccess'
    GO

    Wednesday, July 16, 2014 8:52 AM
  • --
    --Stored Procedure rights for BasicAcess
    --
    GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetPassword TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetPasswordWithFormat TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUserInfo TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_BasicAccess
    
    --
    --Stored Procedure rights for ReportingAccess
    --
    GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetAllUsers TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByName TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByEmail TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_ReportingAccess
    
    --
    --Additional stored procedure rights for FullAccess
    --
    GRANT EXECUTE ON dbo.aspnet_Users_DeleteUser TO aspnet_Membership_FullAccess
    
    GRANT EXECUTE ON dbo.aspnet_Membership_CreateUser TO aspnet_Membership_FullAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_SetPassword TO aspnet_Membership_FullAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_ResetPassword TO aspnet_Membership_FullAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUser TO aspnet_Membership_FullAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer TO aspnet_Membership_FullAccess
    GRANT EXECUTE ON dbo.aspnet_Membership_UnlockUser TO aspnet_Membership_FullAccess
    
    --
    --View rights
    --
    GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Membership_ReportingAccess
    GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Membership_ReportingAccess
    
    GRANT SELECT ON dbo.vw_aspnet_MembershipUsers TO aspnet_Membership_ReportingAccess
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    DECLARE @command nvarchar(4000)
    SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
    EXECUTE (@command)
    GO
    
    PRINT '--------------------------------------------'
    PRINT 'Completed execution of InstallMembership.SQL'
    PRINT '--------------------------------------------'
    /**********************************************************************/
    /* InstallProfile.SQL                                         */
    /*                                                                    */
    /* Installs the tables, triggers and stored procedures necessary for  */
    /* supporting the aspnet feature of ASP.Net                           */
    /*                                                                    */
    /* InstallCommon.sql must be run before running this file.            */
    /*
    ** Copyright Microsoft, Inc. 2002
    ** All Rights Reserved.
    */
    /**********************************************************************/
    
    PRINT '------------------------------------------------'
    PRINT 'Starting execution of InstallProfile.SQL'
    PRINT '------------------------------------------------'
    GO
    
    SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
    SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_NULL_DFLT_ON ON
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    DECLARE @dbname nvarchar(128)
    
    SET @dbname = N'$(DatabaseName)'
    
    IF (NOT EXISTS (SELECT name
                    FROM master.dbo.sysdatabases
                    WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
    BEGIN
      RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
    END
    GO
    
    USE [$(DatabaseName)]
    GO
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Applications')
                      AND (type = 'U')))
    BEGIN
      RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Users')
                      AND (type = 'U')))
    BEGIN
      RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Applications_CreateApplication')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_CreateUser')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_DeleteUser')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Profile')
                      AND (type = 'U')))
    BEGIN
      PRINT 'Creating the aspnet_Profile table...'
      CREATE TABLE dbo.aspnet_Profile (
            UserId                   uniqueidentifier   PRIMARY KEY FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),
            PropertyNames            ntext NOT NULL,
            PropertyValuesString     ntext NOT NULL,
            PropertyValuesBinary     image NOT NULL,
            LastUpdatedDate          datetime NOT NULL)
    END
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Profile_GetProperties')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Profile_GetProperties
    GO
    
    CREATE PROCEDURE dbo.aspnet_Profile_GetProperties
        @ApplicationName      nvarchar(256),
        @UserName             nvarchar(256),
        @CurrentTimeUtc       datetime
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN
    
        DECLARE @UserId uniqueidentifier
        SELECT  @UserId = NULL
    
        SELECT @UserId = UserId
        FROM   dbo.aspnet_Users
        WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
    
        IF (@UserId IS NULL)
            RETURN
        SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
        FROM         dbo.aspnet_Profile
        WHERE        UserId = @UserId
    
        IF (@@ROWCOUNT > 0)
        BEGIN
            UPDATE dbo.aspnet_Users
            SET    LastActivityDate=@CurrentTimeUtc
            WHERE  UserId = @UserId
        END
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Profile_SetProperties')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Profile_SetProperties
    GO
    
    CREATE PROCEDURE dbo.aspnet_Profile_SetProperties
        @ApplicationName        nvarchar(256),
        @PropertyNames          ntext,
        @PropertyValuesString   ntext,
        @PropertyValuesBinary   image,
        @UserName               nvarchar(256),
        @IsUserAnonymous        bit,
        @CurrentTimeUtc         datetime
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
    
        DECLARE @ErrorCode     int
        SET @ErrorCode = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
           BEGIN TRANSACTION
           SET @TranStarted = 1
        END
        ELSE
        	SET @TranStarted = 0
    
        EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        DECLARE @UserId uniqueidentifier
        DECLARE @LastActivityDate datetime
        SELECT  @UserId = NULL
        SELECT  @LastActivityDate = @CurrentTimeUtc
    
        SELECT @UserId = UserId
        FROM   dbo.aspnet_Users
        WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
        IF (@UserId IS NULL)
            EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF (EXISTS( SELECT *
                   FROM   dbo.aspnet_Profile
                   WHERE  UserId = @UserId))
            UPDATE dbo.aspnet_Profile
            SET    PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
                   PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
            WHERE  UserId = @UserId
        ELSE
            INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
                 VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF( @TranStarted = 1 )
        BEGIN
        	SET @TranStarted = 0
        	COMMIT TRANSACTION
        END
    
        RETURN 0
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
        	ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    
    END
    GO
    /*************************************************************/
    /*************************************************************/
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Profile_DeleteProfiles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Profile_DeleteProfiles
    GO
    
    CREATE PROCEDURE dbo.aspnet_Profile_DeleteProfiles
        @ApplicationName        nvarchar(256),
        @UserNames              nvarchar(4000)
    AS
    BEGIN
        DECLARE @UserName     nvarchar(256)
        DECLARE @CurrentPos   int
        DECLARE @NextPos      int
        DECLARE @NumDeleted   int
        DECLARE @DeletedUser  int
        DECLARE @TranStarted  bit
        DECLARE @ErrorCode    int
    
        SET @ErrorCode = 0
        SET @CurrentPos = 1
        SET @NumDeleted = 0
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
            BEGIN TRANSACTION
            SET @TranStarted = 1
        END
        ELSE
        	SET @TranStarted = 0
    
        WHILE (@CurrentPos <= LEN(@UserNames))
        BEGIN
            SELECT @NextPos = CHARINDEX(N',', @UserNames,  @CurrentPos)
            IF (@NextPos = 0 OR @NextPos IS NULL)
                SELECT @NextPos = LEN(@UserNames) + 1
    
            SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
            SELECT @CurrentPos = @NextPos+1
    
            IF (LEN(@UserName) > 0)
            BEGIN
                SELECT @DeletedUser = 0
                EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
                IF( @@ERROR <> 0 )
                BEGIN
                    SET @ErrorCode = -1
                    GOTO Cleanup
                END
                IF (@DeletedUser <> 0)
                    SELECT @NumDeleted = @NumDeleted + 1
            END
        END
        SELECT @NumDeleted
        IF (@TranStarted = 1)
        BEGIN
        	SET @TranStarted = 0
        	COMMIT TRANSACTION
        END
        SET @TranStarted = 0
    
        RETURN 0
    
    Cleanup:
        IF (@TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
        	ROLLBACK TRANSACTION
        END
        RETURN @ErrorCode
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Profile_DeleteInactiveProfiles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Profile_DeleteInactiveProfiles
    GO
    
    CREATE PROCEDURE dbo.aspnet_Profile_DeleteInactiveProfiles
        @ApplicationName        nvarchar(256),
        @ProfileAuthOptions     int,
        @InactiveSinceDate      datetime
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
        BEGIN
            SELECT  0
            RETURN
        END
    
        DELETE
        FROM    dbo.aspnet_Profile
        WHERE   UserId IN
                (   SELECT  UserId
                    FROM    dbo.aspnet_Users u
                    WHERE   ApplicationId = @ApplicationId
                            AND (LastActivityDate <= @InactiveSinceDate)
                            AND (
                                    (@ProfileAuthOptions = 2)
                                 OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                                 OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                                )
                )
    
        SELECT  @@ROWCOUNT
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
     IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Profile_GetNumberOfInactiveProfiles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
    GO
    
    CREATE PROCEDURE dbo.aspnet_Profile_GetNumberOfInactiveProfiles
        @ApplicationName        nvarchar(256),
        @ProfileAuthOptions     int,
        @InactiveSinceDate      datetime
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
        BEGIN
            SELECT 0
            RETURN
        END
    
        SELECT  COUNT(*)
        FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
        WHERE   ApplicationId = @ApplicationId
            AND u.UserId = p.UserId
            AND (LastActivityDate <= @InactiveSinceDate)
            AND (
                    (@ProfileAuthOptions = 2)
                    OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                    OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                )
    END
    GO
    
    
    /*************************************************************/
    /*************************************************************/
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Profile_GetProfiles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Profile_GetProfiles
    GO
    
    CREATE PROCEDURE dbo.aspnet_Profile_GetProfiles
        @ApplicationName        nvarchar(256),
        @ProfileAuthOptions     int,
        @PageIndex              int,
        @PageSize               int,
        @UserNameToMatch        nvarchar(256) = NULL,
        @InactiveSinceDate      datetime      = NULL
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN
    
        -- Set the page bounds
        DECLARE @PageLowerBound int
        DECLARE @PageUpperBound int
        DECLARE @TotalRecords   int
        SET @PageLowerBound = @PageSize * @PageIndex
        SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
    
        -- Create a temp table TO store the select results
        CREATE TABLE #PageIndexForUsers
        (
            IndexId int IDENTITY (0, 1) NOT NULL,
            UserId uniqueidentifier
        )
    
        -- Insert into our temp table
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT  u.UserId
            FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
            WHERE   ApplicationId = @ApplicationId
                AND u.UserId = p.UserId
                AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
                AND (     (@ProfileAuthOptions = 2)
                       OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                       OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                     )
                AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
            ORDER BY UserName
    
        SELECT  u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
                DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
        FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
        WHERE   u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
    
        SELECT COUNT(*)
        FROM   #PageIndexForUsers
    
        DROP TABLE #PageIndexForUsers
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'vw_aspnet_Profiles')
                      AND (type = 'V')))
    BEGIN
      PRINT 'Creating the vw_aspnet_Profiles view...'
      EXEC(N'
      CREATE VIEW [dbo].[vw_aspnet_Profiles]
      AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
          [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
                     + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
                     + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
      FROM [dbo].[aspnet_Profile]
      ')
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    --
    --Create Profile schema version
    --
    
    DECLARE @command nvarchar(4000)
    SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
    EXECUTE (@command)
    GO
    
    EXEC [dbo].aspnet_RegisterSchemaVersion N'Profile', N'1', 1, 1
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    --
    --Create Profile roles
    --
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Profile_FullAccess' ) )
    EXEC sp_addrole N'aspnet_Profile_FullAccess'
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Profile_BasicAccess' ) )
    EXEC sp_addrole N'aspnet_Profile_BasicAccess'
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Profile_ReportingAccess' ) )
    EXEC sp_addrole N'aspnet_Profile_ReportingAccess'
    GO
    
    EXEC sp_addrolemember N'aspnet_Profile_BasicAccess', N'aspnet_Profile_FullAccess'
    EXEC sp_addrolemember N'aspnet_Profile_ReportingAccess', N'aspnet_Profile_FullAccess'
    GO
    
    --
    --Stored Procedure rights for BasicAccess
    --
    GRANT EXECUTE ON dbo.aspnet_Profile_GetProperties TO aspnet_Profile_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_Profile_SetProperties TO aspnet_Profile_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Profile_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Profile_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Profile_BasicAccess
    
    --
    --Stored Procedure rights for ReportingAccess
    --
    GRANT EXECUTE ON dbo.aspnet_Profile_GetNumberOfInactiveProfiles TO aspnet_Profile_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Profile_GetProfiles TO aspnet_Profile_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Profile_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Profile_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Profile_ReportingAccess
    
    --
    --Additional stored procedure rights for FullAccess
    --
    GRANT EXECUTE ON dbo.aspnet_Profile_DeleteProfiles TO aspnet_Profile_FullAccess
    GRANT EXECUTE ON dbo.aspnet_Profile_DeleteInactiveProfiles TO aspnet_Profile_FullAccess
    
    --
    --View rights
    --
    GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Profile_ReportingAccess
    GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Profile_ReportingAccess
    
    GRANT SELECT ON dbo.vw_aspnet_Profiles TO aspnet_Profile_ReportingAccess
    GO
    
    -------------------------------------------------------------------------
    --- Version specific install
    -------------------------------------------------------------------------
    
    DECLARE @ver int
    DECLARE @version nchar(100)
    DECLARE @dot int
    DECLARE @hyphen int
    DECLARE @SqlToExec nchar(400)
    
    SELECT @ver = 8
    SELECT @version = @@Version
    SELECT @hyphen  = CHARINDEX(N' - ', @version)
    IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
    BEGIN
        SELECT @hyphen = @hyphen + 3
        SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
        IF (NOT(@dot IS NULL) AND @dot > @hyphen)
        BEGIN
            SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
            SELECT @ver     = CONVERT(int, @version)
        END
    END
    
    IF (@ver >= 8)
    BEGIN
        EXEC sp_tableoption N'aspnet_Profile', 'text in row', 6000
    END
    GO
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    
    DECLARE @command nvarchar(4000)
    SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
    EXECUTE (@command)
    GO
    
    PRINT '-------------------------------------------------'
    PRINT 'Completed execution of InstallProfile.SQL'
    PRINT '-------------------------------------------------'
    /**********************************************************************/
    /* InstallRoles.SQL                                                   */
    /*                                                                    */
    /* Installs the tables, triggers and stored procedures necessary for  */
    /* supporting the aspnet feature of ASP.Net                           */
    /*                                                                    */
    /* InstallCommon.sql must be run before running this file.            */
    /*
    ** Copyright Microsoft, Inc. 2002
    ** All Rights Reserved.
    */
    /**********************************************************************/
    
    PRINT '--------------------------------------'
    PRINT 'Starting execution of InstallRoles.SQL'
    PRINT '--------------------------------------'
    GO
    
    SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
    SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
    GO
    SET ANSI_PADDING ON
    GO
    SET ANSI_NULL_DFLT_ON ON
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    DECLARE @dbname nvarchar(128)
    
    SET @dbname = N'$(DatabaseName)'
    
    IF (NOT EXISTS (SELECT name
                    FROM master.dbo.sysdatabases
                    WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
    BEGIN
      RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
    END
    GO
    
    USE [$(DatabaseName)]
    GO
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Applications')
                      AND (type = 'U')))
    BEGIN
      RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Users')
                      AND (type = 'U')))
    BEGIN
      RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Applications_CreateApplication')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_CreateUser')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    IF (NOT EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Users_DeleteUser')
                   AND (type = 'P')))
    BEGIN
      RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1)
    END
    
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_Roles')
                      AND (type = 'U')))
    BEGIN
      PRINT 'Creating the aspnet_Roles table...'
      CREATE TABLE dbo.aspnet_Roles (
            ApplicationId    uniqueidentifier    NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
            RoleId           uniqueidentifier    PRIMARY KEY  NONCLUSTERED DEFAULT NEWID(),
            RoleName         nvarchar(256)       NOT NULL,
            LoweredRoleName  nvarchar(256)       NOT NULL,
            Description      nvarchar(256)       )
     CREATE UNIQUE  CLUSTERED  INDEX aspnet_Roles_index1 ON  dbo.aspnet_Roles(ApplicationId, LoweredRoleName)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'aspnet_UsersInRoles')
                      AND (type = 'U')))
    BEGIN
      PRINT 'Creating the aspnet_UsersInRoles table...'
      CREATE TABLE dbo.aspnet_UsersInRoles (
            UserId     uniqueidentifier NOT NULL PRIMARY KEY(UserId, RoleId) FOREIGN KEY REFERENCES dbo.aspnet_Users (UserId),
            RoleId     uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Roles (RoleId))
    
      CREATE INDEX aspnet_UsersInRoles_index ON  dbo.aspnet_UsersInRoles(RoleId)
    END
    
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_UsersInRoles_IsUserInRole')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
    GO
    
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_IsUserInRole
        @ApplicationName  nvarchar(256),
        @UserName         nvarchar(256),
        @RoleName         nvarchar(256)
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN(2)
        DECLARE @UserId uniqueidentifier
        SELECT  @UserId = NULL
        DECLARE @RoleId uniqueidentifier
        SELECT  @RoleId = NULL
    
        SELECT  @UserId = UserId
        FROM    dbo.aspnet_Users
        WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
    
        IF (@UserId IS NULL)
            RETURN(2)
    
        SELECT  @RoleId = RoleId
        FROM    dbo.aspnet_Roles
        WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
    
        IF (@RoleId IS NULL)
            RETURN(3)
    
        IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
            RETURN(1)
        ELSE
            RETURN(0)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_UsersInRoles_GetRolesForUser')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
    GO
    
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser
        @ApplicationName  nvarchar(256),
        @UserName         nvarchar(256)
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN(1)
        DECLARE @UserId uniqueidentifier
        SELECT  @UserId = NULL
    
        SELECT  @UserId = UserId
        FROM    dbo.aspnet_Users
        WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId
    
        IF (@UserId IS NULL)
            RETURN(1)
    
        SELECT r.RoleName
        FROM   dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
        WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
        ORDER BY r.RoleName
        RETURN (0)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Roles_CreateRole')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Roles_CreateRole
    GO
    CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
        @ApplicationName  nvarchar(256),
        @RoleName         nvarchar(256)
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
    
        DECLARE @ErrorCode     int
        SET @ErrorCode = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
            BEGIN TRANSACTION
            SET @TranStarted = 1
        END
        ELSE
            SET @TranStarted = 0
    
        EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
        BEGIN
            SET @ErrorCode = 1
            GOTO Cleanup
        END
    
        INSERT INTO dbo.aspnet_Roles
                    (ApplicationId, RoleName, LoweredRoleName)
             VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
            COMMIT TRANSACTION
        END
    
        RETURN(0)
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
            ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Roles_DeleteRole')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Roles_DeleteRole
    GO
    
    CREATE PROCEDURE dbo.aspnet_Roles_DeleteRole
        @ApplicationName            nvarchar(256),
        @RoleName                   nvarchar(256),
        @DeleteOnlyIfRoleIsEmpty    bit
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN(1)
    
        DECLARE @ErrorCode     int
        SET @ErrorCode = 0
    
        DECLARE @TranStarted   bit
        SET @TranStarted = 0
    
        IF( @@TRANCOUNT = 0 )
        BEGIN
            BEGIN TRANSACTION
            SET @TranStarted = 1
        END
        ELSE
            SET @TranStarted = 0
    
        DECLARE @RoleId   uniqueidentifier
        SELECT  @RoleId = NULL
        SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId
    
        IF (@RoleId IS NULL)
        BEGIN
            SELECT @ErrorCode = 1
            GOTO Cleanup
        END
        IF (@DeleteOnlyIfRoleIsEmpty <> 0)
        BEGIN
            IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId))
            BEGIN
                SELECT @ErrorCode = 2
                GOTO Cleanup
            END
        END
    
    
        DELETE FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId  AND ApplicationId = @ApplicationId
    
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
            COMMIT TRANSACTION
        END
    
        RETURN(0)
    
    Cleanup:
    
        IF( @TranStarted = 1 )
        BEGIN
            SET @TranStarted = 0
            ROLLBACK TRANSACTION
        END
    
        RETURN @ErrorCode
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Roles_RoleExists')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Roles_RoleExists
    GO
    
    CREATE PROCEDURE dbo.aspnet_Roles_RoleExists
        @ApplicationName  nvarchar(256),
        @RoleName         nvarchar(256)
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN(0)
        IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
            RETURN(1)
        ELSE
            RETURN(0)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_UsersInRoles_AddUsersToRoles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
    GO
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_UsersInRoles_RemoveUsersFromRoles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
    GO
    
    DECLARE @ver            int
    DECLARE @version        nchar(100)
    DECLARE @dot            int
    DECLARE @hyphen         int
    DECLARE @SqlToExec      nchar(4000)
    
    SELECT @ver = 7
    SELECT @version = @@Version
    SELECT @hyphen  = CHARINDEX(N' - ', @version)
    IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
    BEGIN
        SELECT @hyphen = @hyphen + 3
        SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)
        IF (NOT(@dot IS NULL) AND @dot > @hyphen)
        BEGIN
            SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
            SELECT @ver     = CONVERT(int, @version)
        END
    END
    
    IF (@ver > 7)
    SELECT @SqlToExec = N'
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
    	@ApplicationName  nvarchar(256),
    	@UserNames		  nvarchar(4000),
    	@RoleNames		  nvarchar(4000),
    	@CurrentTimeUtc   datetime
    AS
    BEGIN
    	DECLARE @AppId uniqueidentifier
    	SELECT  @AppId = NULL
    	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    	IF (@AppId IS NULL)
    		RETURN(2)
    	DECLARE @TranStarted   bit
    	SET @TranStarted = 0
    
    	IF( @@TRANCOUNT = 0 )
    	BEGIN
    		BEGIN TRANSACTION
    		SET @TranStarted = 1
    	END
    
    	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
    	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
    	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
    	DECLARE @Num		int
    	DECLARE @Pos		int
    	DECLARE @NextPos	int
    	DECLARE @Name		nvarchar(256)
    
    	SET @Num = 0
    	SET @Pos = 1
    	WHILE(@Pos <= LEN(@RoleNames))
    	BEGIN
    		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
    		IF (@NextPos = 0 OR @NextPos IS NULL)
    			SELECT @NextPos = LEN(@RoleNames) + 1
    		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
    		SELECT @Pos = @NextPos+1
    
    		INSERT INTO @tbNames VALUES (@Name)
    		SET @Num = @Num + 1
    	END
    
    	INSERT INTO @tbRoles
    	  SELECT RoleId
    	  FROM   dbo.aspnet_Roles ar, @tbNames t
    	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
    
    	IF (@@ROWCOUNT <> @Num)
    	BEGIN
    		SELECT TOP 1 Name
    		FROM   @tbNames
    		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
    		IF( @TranStarted = 1 )
    			ROLLBACK TRANSACTION
    		RETURN(2)
    	END
    
    	DELETE FROM @tbNames WHERE 1=1
    	SET @Num = 0
    	SET @Pos = 1
    
    	WHILE(@Pos <= LEN(@UserNames))
    	BEGIN
    		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
    		IF (@NextPos = 0 OR @NextPos IS NULL)
    			SELECT @NextPos = LEN(@UserNames) + 1
    		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
    		SELECT @Pos = @NextPos+1
    
    		INSERT INTO @tbNames VALUES (@Name)
    		SET @Num = @Num + 1
    	END
    
    	INSERT INTO @tbUsers
    	  SELECT UserId
    	  FROM   dbo.aspnet_Users ar, @tbNames t
    	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
    
    	IF (@@ROWCOUNT <> @Num)
    	BEGIN
    		DELETE FROM @tbNames
    		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)
    
    		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
    		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
    		  FROM   @tbNames
    
    		INSERT INTO @tbUsers
    		  SELECT  UserId
    		  FROM	dbo.aspnet_Users au, @tbNames t
    		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
    	END
    
    	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
    	BEGIN
    		SELECT TOP 1 UserName, RoleName
    		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
    		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId
    
    		IF( @TranStarted = 1 )
    			ROLLBACK TRANSACTION
    		RETURN(3)
    	END
    
    	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
    	SELECT UserId, RoleId
    	FROM @tbUsers, @tbRoles
    
    	IF( @TranStarted = 1 )
    		COMMIT TRANSACTION
    	RETURN(0)
    END'
    ELSE
    SELECT @SqlToExec = N'
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_AddUsersToRoles
    	@ApplicationName	nvarchar(256),
    	@UserNames			nvarchar(4000),
    	@RoleNames			nvarchar(4000),
    	@CurrentTimeUtc		datetime
    AS
    BEGIN
    	DECLARE @AppId uniqueidentifier
    	SELECT  @AppId = NULL
    	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    	IF (@AppId IS NULL)
    		RETURN(2)
    
    	DECLARE @TranStarted   bit
    	SET @TranStarted = 0
    	IF( @@TRANCOUNT = 0 )
    	BEGIN
    		BEGIN TRANSACTION
    		SET @TranStarted = 1
    	END
    
    	DECLARE @RoleId		uniqueidentifier
    	DECLARE @UserId		uniqueidentifier
    	DECLARE @UserName	nvarchar(256)
    	DECLARE @RoleName	nvarchar(256)
    
    	DECLARE @CurrentPosU	int
    	DECLARE @NextPosU		int
    	DECLARE @CurrentPosR	int
    	DECLARE @NextPosR		int
    
    	SELECT  @CurrentPosU = 1
    
    	WHILE(@CurrentPosU <= LEN(@UserNames))
    	BEGIN
    		SELECT @NextPosU = CHARINDEX(N'','', @UserNames,  @CurrentPosU)
    		IF (@NextPosU = 0 OR @NextPosU IS NULL)
    			SELECT @NextPosU = LEN(@UserNames) + 1
    
    		SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
    		SELECT @CurrentPosU = @NextPosU+1
    
    		SELECT @CurrentPosR = 1
    		WHILE(@CurrentPosR <= LEN(@RoleNames))
    		BEGIN
    			SELECT @NextPosR = CHARINDEX(N'','', @RoleNames,  @CurrentPosR)
    			IF (@NextPosR = 0 OR @NextPosR IS NULL)
    				SELECT @NextPosR = LEN(@RoleNames) + 1
    			SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
    			SELECT @CurrentPosR = @NextPosR+1
    			SELECT @RoleId = NULL
    			SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
    			IF (@RoleId IS NULL)
    			BEGIN
    				SELECT @RoleName
    				IF( @TranStarted = 1 )
    					ROLLBACK TRANSACTION
    				RETURN(2)
    			END
    
    			SELECT @UserId = NULL
    			SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
    			IF (@UserId IS NULL)
    			BEGIN
    				EXEC dbo.aspnet_Users_CreateUser @AppId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
    			END
    
    			IF (EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId))
    			BEGIN
    				SELECT @UserName, @RoleName
    				IF( @TranStarted = 1 )
    					ROLLBACK TRANSACTION
    				RETURN(3)
    			END
    			INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId) VALUES(@UserId, @RoleId)
    		END
    	END
    	IF( @TranStarted = 1 )
    		COMMIT TRANSACTION
    	RETURN(0)
    END'
    
    EXEC sp_executesql @SqlToExec
    
    IF (@ver > 7)
    SELECT @SqlToExec = N'
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
    	@ApplicationName  nvarchar(256),
    	@UserNames		  nvarchar(4000),
    	@RoleNames		  nvarchar(4000)
    AS
    BEGIN
    	DECLARE @AppId uniqueidentifier
    	SELECT  @AppId = NULL
    	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    	IF (@AppId IS NULL)
    		RETURN(2)
    
    
    	DECLARE @TranStarted   bit
    	SET @TranStarted = 0
    
    	IF( @@TRANCOUNT = 0 )
    	BEGIN
    		BEGIN TRANSACTION
    		SET @TranStarted = 1
    	END
    
    	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
    	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
    	DECLARE @tbUsers  table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
    	DECLARE @Num	  int
    	DECLARE @Pos	  int
    	DECLARE @NextPos  int
    	DECLARE @Name	  nvarchar(256)
    	DECLARE @CountAll int
    	DECLARE @CountU	  int
    	DECLARE @CountR	  int
    
    
    	SET @Num = 0
    	SET @Pos = 1
    	WHILE(@Pos <= LEN(@RoleNames))
    	BEGIN
    		SELECT @NextPos = CHARINDEX(N'','', @RoleNames,  @Pos)
    		IF (@NextPos = 0 OR @NextPos IS NULL)
    			SELECT @NextPos = LEN(@RoleNames) + 1
    		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
    		SELECT @Pos = @NextPos+1
    
    		INSERT INTO @tbNames VALUES (@Name)
    		SET @Num = @Num + 1
    	END
    
    	INSERT INTO @tbRoles
    	  SELECT RoleId
    	  FROM   dbo.aspnet_Roles ar, @tbNames t
    	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
    	SELECT @CountR = @@ROWCOUNT
    
    	IF (@CountR <> @Num)
    	BEGIN
    		SELECT TOP 1 N'''', Name
    		FROM   @tbNames
    		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
    		IF( @TranStarted = 1 )
    			ROLLBACK TRANSACTION
    		RETURN(2)
    	END
    
    
    	DELETE FROM @tbNames WHERE 1=1
    	SET @Num = 0
    	SET @Pos = 1
    
    
    	WHILE(@Pos <= LEN(@UserNames))
    	BEGIN
    		SELECT @NextPos = CHARINDEX(N'','', @UserNames,  @Pos)
    		IF (@NextPos = 0 OR @NextPos IS NULL)
    			SELECT @NextPos = LEN(@UserNames) + 1
    		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
    		SELECT @Pos = @NextPos+1
    
    		INSERT INTO @tbNames VALUES (@Name)
    		SET @Num = @Num + 1
    	END
    
    	INSERT INTO @tbUsers
    	  SELECT UserId
    	  FROM   dbo.aspnet_Users ar, @tbNames t
    	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
    
    	SELECT @CountU = @@ROWCOUNT
    	IF (@CountU <> @Num)
    	BEGIN
    		SELECT TOP 1 Name, N''''
    		FROM   @tbNames
    		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)
    
    		IF( @TranStarted = 1 )
    			ROLLBACK TRANSACTION
    		RETURN(1)
    	END
    
    	SELECT  @CountAll = COUNT(*)
    	FROM	dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
    	WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId
    
    	IF (@CountAll <> @CountU * @CountR)
    	BEGIN
    		SELECT TOP 1 UserName, RoleName
    		FROM		 @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
    		WHERE		 u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
    					 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
    					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
    		IF( @TranStarted = 1 )
    			ROLLBACK TRANSACTION
    		RETURN(3)
    	END
    
    	DELETE FROM dbo.aspnet_UsersInRoles
    	WHERE UserId IN (SELECT UserId FROM @tbUsers)
    	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
    	IF( @TranStarted = 1 )
    		COMMIT TRANSACTION
    	RETURN(0)
    END
    '
    ELSE
    SELECT @SqlToExec = N'
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_RemoveUsersFromRoles
    	@ApplicationName  nvarchar(256),
    	@UserNames		  nvarchar(4000),
    	@RoleNames		  nvarchar(4000)
    AS
    BEGIN
    	DECLARE @AppId uniqueidentifier
    	SELECT  @AppId = NULL
    	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    	IF (@AppId IS NULL)
    		RETURN(2)
    
    
    	DECLARE @TranStarted   bit
    	SET @TranStarted = 0
    
    	IF( @@TRANCOUNT = 0 )
    	BEGIN
    		BEGIN TRANSACTION
    		SET @TranStarted = 1
    	END
    
    	DECLARE @RoleId		uniqueidentifier
    	DECLARE @UserId		uniqueidentifier
    	DECLARE @UserName	nvarchar(256)
    	DECLARE @RoleName	nvarchar(256)
    
    	DECLARE @CurrentPosU	int
    	DECLARE @NextPosU		int
    	DECLARE @CurrentPosR	int
    	DECLARE @NextPosR		int
    
    	SELECT  @CurrentPosU = 1
    
    	WHILE(@CurrentPosU <= LEN(@UserNames))
    	BEGIN
    		SELECT @NextPosU = CHARINDEX(N'','', @UserNames,  @CurrentPosU)
    		IF (@NextPosU = 0  OR @NextPosU IS NULL)
    			SELECT @NextPosU = LEN(@UserNames)+1
    		SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
    		SELECT @CurrentPosU = @NextPosU+1
    
    		SELECT @CurrentPosR = 1
    		WHILE(@CurrentPosR <= LEN(@RoleNames))
    		BEGIN
    			SELECT @NextPosR = CHARINDEX(N'','', @RoleNames,  @CurrentPosR)
    			IF (@NextPosR = 0 OR @NextPosR IS NULL)
    				SELECT @NextPosR = LEN(@RoleNames)+1
    			SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
    			SELECT @CurrentPosR = @NextPosR+1
    
    			SELECT @RoleId = NULL
    			SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
    			IF (@RoleId IS NULL)
    			BEGIN
    				SELECT N'''', @RoleName
    				IF( @TranStarted = 1 )
    					ROLLBACK TRANSACTION
    				RETURN(2)
    			END
    
    			SELECT @UserId = NULL
    			SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
    			IF (@UserId IS NULL)
    			BEGIN
    				SELECT @UserName, N''''
    				IF( @TranStarted = 1 )
    					ROLLBACK TRANSACTION
    				RETURN(1)
    			END
    
    			IF (NOT(EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId)))
    			BEGIN
    				SELECT @UserName, @RoleName
    				IF( @TranStarted = 1 )
    					ROLLBACK TRANSACTION
    				RETURN(3)
    			END
    			DELETE FROM dbo.aspnet_UsersInRoles WHERE (UserId = @UserId AND RoleId = @RoleId)
    		END
    	END
    	IF( @TranStarted = 1 )
    		COMMIT TRANSACTION
    	RETURN(0)
    END
    '
    EXEC sp_executesql @SqlToExec
    GO
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_UsersInRoles_GetUsersInRoles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
    GO
    
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_GetUsersInRoles
        @ApplicationName  nvarchar(256),
        @RoleName         nvarchar(256)
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN(1)
         DECLARE @RoleId uniqueidentifier
         SELECT  @RoleId = NULL
    
         SELECT  @RoleId = RoleId
         FROM    dbo.aspnet_Roles
         WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
    
         IF (@RoleId IS NULL)
             RETURN(1)
    
        SELECT u.UserName
        FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
        WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
        ORDER BY u.UserName
        RETURN(0)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_UsersInRoles_FindUsersInRole')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
    GO
    
    CREATE PROCEDURE dbo.aspnet_UsersInRoles_FindUsersInRole
        @ApplicationName  nvarchar(256),
        @RoleName         nvarchar(256),
        @UserNameToMatch  nvarchar(256)
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN(1)
         DECLARE @RoleId uniqueidentifier
         SELECT  @RoleId = NULL
    
         SELECT  @RoleId = RoleId
         FROM    dbo.aspnet_Roles
         WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
    
         IF (@RoleId IS NULL)
             RETURN(1)
    
        SELECT u.UserName
        FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
        WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
        ORDER BY u.UserName
        RETURN(0)
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (EXISTS (SELECT name
                  FROM sysobjects
                 WHERE (name = N'aspnet_Roles_GetAllRoles')
                   AND (type = 'P')))
    DROP PROCEDURE dbo.aspnet_Roles_GetAllRoles
    GO
    
    CREATE PROCEDURE dbo.aspnet_Roles_GetAllRoles (
        @ApplicationName           nvarchar(256))
    AS
    BEGIN
        DECLARE @ApplicationId uniqueidentifier
        SELECT  @ApplicationId = NULL
        SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
        IF (@ApplicationId IS NULL)
            RETURN
        SELECT RoleName
        FROM   dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
        ORDER BY RoleName
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'vw_aspnet_Roles')
                      AND (type = 'V')))
    BEGIN
      PRINT 'Creating the vw_aspnet_Roles view...'
      EXEC(N'
      CREATE VIEW [dbo].[vw_aspnet_Roles]
      AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
      FROM [dbo].[aspnet_Roles]
      ')
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    IF (NOT EXISTS (SELECT name
                    FROM sysobjects
                    WHERE (name = N'vw_aspnet_UsersInRoles')
                      AND (type = 'V')))
    BEGIN
      PRINT 'Creating the vw_aspnet_UsersInRoles view...'
      EXEC(N'
      CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
      AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
      FROM [dbo].[aspnet_UsersInRoles]
      ')
    END
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    --
    --Create Role Manager schema version
    --
    
    DECLARE @command nvarchar(4000)
    SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user)
    EXECUTE (@command)
    GO
    
    EXEC [dbo].aspnet_RegisterSchemaVersion N'Role Manager', N'1', 1, 1
    GO
    
    /*************************************************************/
    /*************************************************************/
    
    --
    --Create Role Manager roles
    --
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Roles_FullAccess'  ) )
    EXEC sp_addrole N'aspnet_Roles_FullAccess'
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Roles_BasicAccess'  ) )
    EXEC sp_addrole N'aspnet_Roles_BasicAccess'
    
    IF ( NOT EXISTS ( SELECT name
                      FROM sysusers
                      WHERE issqlrole = 1
                      AND name = N'aspnet_Roles_ReportingAccess'  ) )
    EXEC sp_addrole N'aspnet_Roles_ReportingAccess'
    GO
    
    EXEC sp_addrolemember N'aspnet_Roles_BasicAccess', N'aspnet_Roles_FullAccess'
    EXEC sp_addrolemember N'aspnet_Roles_ReportingAccess', N'aspnet_Roles_FullAccess'
    GO
    
    --
    --Stored Procedure rights for BasicAccess
    --
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_BasicAccess
    GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_BasicAccess
    
    --
    --Stored Procedure rights for ReportingAccess
    --
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_IsUserInRole TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetRolesForUser TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Roles_RoleExists TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_GetUsersInRoles TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_FindUsersInRole TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_Roles_GetAllRoles TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Roles_ReportingAccess
    GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Roles_ReportingAccess
    
    --
    --Additional stored procedure rights for FullAccess
    --
    
    GRANT EXECUTE ON dbo.aspnet_Roles_CreateRole TO aspnet_Roles_FullAccess
    GRANT EXECUTE ON dbo.aspnet_Roles_DeleteRole TO aspnet_Roles_FullAccess
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_AddUsersToRoles TO aspnet_Roles_FullAccess
    GRANT EXECUTE ON dbo.aspnet_UsersInRoles_RemoveUsersFromRoles TO aspnet_Roles_FullAccess
    
    --
    --View rights
    --
    GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Roles_ReportingAccess
    GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Roles_ReportingAccess
    
    GRANT SELECT ON dbo.vw_aspnet_Roles TO aspnet_Roles_ReportingAccess
    GRANT SELECT ON dbo.vw_aspnet_UsersInRoles TO aspnet_Roles_ReportingAccess
    
    GO
    
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    
    DECLARE @command nvarchar(4000)
    SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user)
    EXECUTE (@command)
    GO
    
    PRINT '---------------------------------------'
    PRINT 'Completed execution of InstallRoles.SQL'
    PRINT '---------------------------------------'

    Wednesday, July 16, 2014 8:53 AM
  • So before exporting the project I set in the properties that administrator should be company\zmo02.then I exported the project. Of course I exported the project with 2 sql scripts to modify the connection to the data base.<o:p></o:p>
    Can you explicitly describe what you mean by this?

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Wednesday, July 16, 2014 9:10 PM
    Moderator
  • Hi David,

    in the light switch Publish Wizard/Publish output/I selected "Create a Script file to install and configure the data base"

    then as a result the folder was genrated with 2 SQL scripts, the first one I post before and the second one I will post it now.

    the second one called creat user:

    :setvar DatabaseName "NavisionView"
    :setvar DatabaseUserName "lightswitch"
    :setvar DatabaseUserPassword "9rukroizcQ"
    GO
    
    USE [$(DatabaseName)]
    
    DECLARE @usercount int
    SELECT @usercount=COUNT(name) FROM sys.database_principals WHERE name = '$(DatabaseUserName)'
    IF @usercount = 0
    	CREATE USER $(DatabaseUserName) FOR LOGIN $(DatabaseUserName)
    GO
    
    EXEC sp_addrolemember db_datareader, $(DatabaseUserName)
    EXEC sp_addrolemember db_datawriter, $(DatabaseUserName)
    EXEC sp_addrolemember aspnet_Membership_FullAccess, $(DatabaseUserName)
    EXEC sp_addrolemember aspnet_Roles_FullAccess, $(DatabaseUserName)
    GO
    

    Thursday, July 17, 2014 6:19 AM