Running script from aspnet_regsql

Discussion in 'ASP.NET 2.0' started by donmiller714, Sep 15, 2005.

  1. donmiller714

    donmiller714

    Greetings all,
    Searched the forum but havn't got a clear answer...
    I created a script to generate the tables, procs, etc. for the ASPNETDB on my discountasp.net SQL account. As I understand it, the tables, procs, etc. for that database have to go into my discountasp.net database and then I point my web.config there to access the membership, photos, etc. for the personal site (I'm putting the personal site up as a test).
    When I open my discountasp.net SQL Manager, log in and open Query Analyzer, paste the script I created with aspnet_regsql.exe using my user name, password, and db name from the discountasp.net database, I get the following error (full script appears below):

    Request object error 'ASP 0104 : 80004005'
    Operation not Allowed
    /scripts/tools/query.asp, line 33
    Using Notepad.exe, I found that line 33 is a print line, so I commented it out (--) and it still didn't run. So what am I missing here[​IMG] [​IMG] [​IMG]

    Here's the script that aspnet_regsql.exe generated

    /**********************************************************************/
    /* InstallCommon.SQL */
    /* */
    /* Installs the tables, triggers and stored procedures necessary for */
    /* supporting the aspnet feature 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
    DECLARE @dbname NVARCHAR(128)
    DECLARE @dboptions NVARCHAR(1024)
    SET @dboptions = N'/**/'
    SET @dbname = N'DB_144429_bwg'
    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 [DB_144429_bwg]
    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(20),
    [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(20)
    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 &amp; 1) <> 0 AND
    (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership') AND (type = 'U'))))
    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 &amp; 2) is set
    IF ((@TablesToDeleteFrom &amp; 2) <> 0 AND
    (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_UsersInRoles') AND (type = 'U'))) )
    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 &amp; 4) is set
    IF ((@TablesToDeleteFrom &amp; 4) <> 0 AND
    (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Profile') AND (type = 'U'))) )
    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 &amp; 8) is set
    IF ((@TablesToDeleteFrom &amp; 8) <> 0 AND
    (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_PersonalizationPerUser') AND (type = 'U'))) )
    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 &amp; 1,2,4 &amp; 8) are all set
    IF ((@TablesToDeleteFrom &amp; 1) <> 0 AND
    (@TablesToDeleteFrom &amp; 2) <> 0 AND
    (@TablesToDeleteFrom &amp; 4) <> 0 AND
    (@TablesToDeleteFrom &amp; 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
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    IF (EXISTS (SELECT name
    FROM sysobjects
    WHERE (name = N'aspnet_GetUtcDate')
    AND (type = 'P')))
    DROP PROCEDURE [dbo].aspnet_GetUtcDate
    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)
    SET @SqlToExec = N'
    CREATE PROCEDURE [dbo].aspnet_GetUtcDate
    @TimeZoneAdjustment INT,
    @DateNow DATETIME OUTPUT
    AS
    BEGIN
    SELECT @DateNow = DATEADD(n, -@TimeZoneAdjustment, GETDATE())
    END
    '
    ELSE
    SET @SqlToExec = N'
    CREATE PROCEDURE [dbo].aspnet_GetUtcDate
    @TimeZoneAdjustment INT,
    @DateNow DATETIME OUTPUT
    AS
    BEGIN
    SELECT @DateNow = GETUTCDATE()
    END
    '
    EXEC (@SqlToExec)
    GO
    IF (OBJECT_ID('tempdb.#AspstateVer') IS NOT NULL)
    BEGIN
    DROP TABLE #AspstateVer
    END
    GO
    /*************************************************************/
    /*************************************************************/
    -- Restore the permissions
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_Users_DeleteUser'
    GO
    EXEC [dbo].aspnet_Setup_RestorePermissions N'aspnet_GetUtcDate'
    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].[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
    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
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    /*************************************************************/
    DECLARE @dbname NVARCHAR(128)
    SET @dbname = N'DB_144429_bwg'
    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 [DB_144429_bwg]
    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,
    @TimeZoneAdjustment INT,
    @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
    IF (@CreateDate IS NULL)
    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @CreateDate OUTPUT
    ELSE
    SELECT @CreateDate = DATEADD(n, -@TimeZoneAdjustment, @CreateDate) -- switch TO UTC time
    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
    INSERT INTO dbo.aspnet_Membership
    ( ApplicationId,
    UserId,
    Password,
    PasswordSalt,
    Email,
    LoweredEmail,
    PasswordQuestion,
    PasswordAnswer,
    PasswordFormat,
    IsApproved,
    IsLockedOut,
    CreateDate,
    LastLoginDate,
    LastPasswordChangedDate,
    LastLockoutDate,
    FailedPasswordAttemptCount,
    FailedPasswordAttemptWindowStart,
    FailedPasswordAnswerAttemptCount,
    &nbs
     
    donmiller714, Sep 15, 2005
    #1
  2. Deion

    Deion

    Looks like the script you have is fine, just that something in the asp page that they are using for the query analyzer is erroring out on you...
    i would suggest 2 things,

    1) break up your script into smaller chunks...and see if that helps at all

    2) i think the aspnet_regsql.exe can update the remote web site, i am pretty sure thats how i did it, just launch it form DOS and do aspnet_regsql.exe /? and check out the options or maybe even do it via the wizard...

    ~Deion
     
    Deion, Sep 16, 2005
    #2
  3. donmiller714

    donmiller714

    Thanks! What I ended up having to do is run the script, then check what actually got created, then find that spot in the script and execute the next block (e.g. CREATE PROCEDURE). I found out that the query analyzer doesn't like the GOTO statement (even though the error lines reported by the query analyzer don't report the correct line number!). I edited out all of the GOTO's and was able to get the script to run.


    What would be nice is a url we could connect to using our own enterprise manager, rather than the web-based tool provided.
     
    donmiller714, Sep 17, 2005
    #3
  4. pmarangoni

    pmarangoni

    You don't need to run the script in query analyzer. You just run the tool from your own machine, and point it to your SQL Server at discountasp. The program to run is aspnet_regsql.exe. It is basically a wizard.
     
    pmarangoni, Sep 23, 2005
    #4

Share This Page