Membership Provider: how to create initial user acct. after deploy

Discussion in 'ASP .Net' started by hfdev, Nov 20, 2007.

  1. hfdev

    hfdev Guest

    Hello,

    I have a web application that makes use of the SQL Membership and Role
    providers. My app has admin screens to manage users (membership), roles, and
    supplementary user data. I have just deployed the application to a
    production server.

    My Question: How do I create the initial Admin role and user in the
    clean/unpopulated database that has the Membership and Role schema on this
    production server?

    The production server does not have the Visual Studio solution/project, it
    only has the project deliverables.

    Thanks for your help,

    Josh Blair
    HydraForce, Inc.
     
    hfdev, Nov 20, 2007
    #1
    1. Advertising

  2. hfdev

    sloan Guest

    You can either write your own little application.

    I've written one, which uses 3 text files as the source. Users, Roles, and
    UserToRoles.
    This is "owned' code so I can't share it.
    I wrote a console application, which reads these 3 files..and then does the
    Membership.CreateUser type calls through it.


    ...

    You can use the "create tsql code" helper I made:
    THis will generate the Tsql code...that you can run on the production
    database.





    Note, this code generates tsql code. It does not actually perform the
    inserts.

    Also note that an Application/RoleName/UserName with a single quote in the
    name will make the script create bad code.

    -------START TSQL


    SET NOCOUNT ON
    print '-- You probably should set your "Results To Text"'
    print '-- You need to copy and paste the OUTPUT of this query..and run
    against a different aspnetdb membership/roles db'
    print ''
    print ''
    --**************************************************
    print 'SET NOCOUNT ON'
    --**************************************************
    print '/*'
    print '--These next delete lines are optional, but if you want a clean
    transfer, you can run them (uncomment them)'
    print 'delete from dbo.aspnet_Membership'
    print 'delete from dbo.aspnet_UsersInRoles'
    print 'delete from dbo.aspnet_Roles'
    print 'delete from dbo.aspnet_Profile'
    print 'delete from dbo.aspnet_Users'
    print 'delete from dbo.aspnet_Applications'
    print '*/'
    print ''
    print ''

    --**************************************************

    select
    'INSERT INTO dbo.aspnet_Applications (
    ApplicationName,LoweredApplicationName,ApplicationId,[Description] ) values
    (' as [--Comment],
    char(39) + t1.ApplicationName + char(39) , ',' ,
    char(39) + t1.LoweredApplicationName + char(39) , ',' ,
    char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    char(39) + t1.Description + char(39)
    , ')'
    FROM
    dbo.aspnet_Applications t1

    --**************************************************

    -- select top 1 * from dbo.aspnet_Roles
    --ApplicationId,RoleId,RoleName,LoweredRoleName,[Description]
    Select
    'INSERT INTO dbo.aspnet_Roles (
    ApplicationId,RoleId,RoleName,LoweredRoleName,[Description] ) values (' as
    [--Comment],
    char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    char(39) + convert(varchar(38) , t1.RoleId ) + char(39) , ',' ,
    char(39) + t1.RoleName + char(39) , ',' ,
    char(39) + t1.LoweredRoleName + char(39) , ',' ,
    char(39) + t1.Description + char(39)
    , ')'
    FROM
    dbo.aspnet_Roles t1

    --**************************************************

    --select top 1 * from dbo.aspnet_Users
    --ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    Select
    'INSERT INTO dbo.aspnet_Users (
    ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    ) values (' as [--Comment] ,
    char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    char(39) + t1.UserName + char(39) , ',' ,
    char(39) + t1.LoweredUserName + char(39) , ',' ,
    char(39) + t1.MobileAlias + char(39) , ',' ,
    IsAnonymous, ',' ,
    char(39) + convert(varchar(38) , t1.LastActivityDate ) + char(39)
    , ')'
    FROM
    dbo.aspnet_Users t1

    --**************************************************
    print ''
    print '--TO DO-- dbo.aspnet_Profile'
    print '--I did not utilize the Profile information, so I didnt code it
    up....you can follow the pattern and implement Profile information here'
    print ''
    print ''
    --**************************************************
    --select top 1 * from dbo.aspnet_UsersInRoles
    --UserID, RoleID
    Select
    'INSERT INTO dbo.aspnet_UsersInRoles ( UserID, RoleID ) values (' as
    [--Comment],
    char(39) + convert(varchar(38) , t1.UserID ) + char(39) , ',' ,
    char(39) + convert(varchar(38) , t1.RoleID ) + char(39)
    , ')'
    FROM
    dbo.aspnet_UsersInRoles t1

    --**************************************************

    -- select top 1 * from dbo.aspnet_Membership
    --ApplicationId,UserId,Password,PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    --ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,
    --MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,
    --IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,
    --LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,
    --Comment
    Select
    'INSERT INTO dbo.aspnet_Membership (
    ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    ) values (' as [--Comment],
    char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    char(39) + t1.[Password] + char(39) , ',' ,
    PasswordFormat , ',' ,
    char(39) + t1.PasswordSalt + char(39) , ',' ,
    char(39) + t1.MobilePIN + char(39) , ',' ,
    char(39) + t1.Email + char(39) , ',' ,
    char(39) + t1.LoweredEmail + char(39) , ',' ,
    char(39) + t1.PasswordQuestion + char(39) , ',' ,
    char(39) + t1.PasswordAnswer + char(39) , ',' ,
    IsApproved , ',' ,
    IsLockedOut , ',' ,
    char(39) + convert(varchar(38) , t1.CreateDate) + char(39) , ',' ,
    char(39) + convert(varchar(38) , t1.LastLoginDate) + char(39) , ',' ,
    char(39) + convert(varchar(38) , t1.LastPasswordChangedDate) + char(39) ,
    ',' ,
    char(39) + convert(varchar(38) , t1.LastLockoutDate) + char(39) , ',' ,
    FailedPasswordAttemptCount , ',' ,
    char(39) + convert(varchar(38) , t1.FailedPasswordAttemptWindowStart) +
    char(39) , ',' ,
    FailedPasswordAnswerAttemptCount , ',' ,
    char(39) + convert(varchar(38) ,t1.FailedPasswordAnswerAttemptWindowStart )
    + char(39) , ',' ,
    char(39) + convert(varchar(1028) , t1.Comment) + char(39)
    , ')'

    FROM
    dbo.aspnet_Membership t1

    --**************************************************

    print ''
    print ''
    print 'Select * from dbo.aspnet_Applications'
    print 'Select * from dbo.aspnet_Users'
    print 'Select * from dbo.aspnet_Profile'
    print 'Select * from dbo.aspnet_Roles'
    print 'Select * from dbo.aspnet_UsersInRoles'
    print 'Select * from dbo.aspnet_Membership'




    "hfdev" <> wrote in message
    news:...
    > Hello,
    >
    > I have a web application that makes use of the SQL Membership and Role
    > providers. My app has admin screens to manage users (membership), roles,
    > and
    > supplementary user data. I have just deployed the application to a
    > production server.
    >
    > My Question: How do I create the initial Admin role and user in the
    > clean/unpopulated database that has the Membership and Role schema on this
    > production server?
    >
    > The production server does not have the Visual Studio solution/project, it
    > only has the project deliverables.
    >
    > Thanks for your help,
    >
    > Josh Blair
    > HydraForce, Inc.
     
    sloan, Nov 20, 2007
    #2
    1. Advertising

  3. hfdev

    hfdev Guest

    Re: Membership Provider: how to create initial user acct. after de

    Sloan,

    Thanks very much for your insight. I would like to build a console app like
    the one that you mentioned (because I have 4 such application to deploy with
    many user accounts that I need to pull from legacy applications' databases.
    I wasn't sure how a console app could use the provider APIs because of how
    the APIs are configured via the web.config? Can you use the Membership and
    Role configuration xml tags in an app.config? Also, does your console app
    create the aspnet_Applications record? If so, do you generate the GUID?

    Thanks,

    Josh Blair
    HydraForce, Inc.

    "sloan" wrote:

    > You can either write your own little application.
    >
    > I've written one, which uses 3 text files as the source. Users, Roles, and
    > UserToRoles.
    > This is "owned' code so I can't share it.
    > I wrote a console application, which reads these 3 files..and then does the
    > Membership.CreateUser type calls through it.
    >
    >
    > ...
    >
    > You can use the "create tsql code" helper I made:
    > THis will generate the Tsql code...that you can run on the production
    > database.
    >
    >
    >
    >
    >
    > Note, this code generates tsql code. It does not actually perform the
    > inserts.
    >
    > Also note that an Application/RoleName/UserName with a single quote in the
    > name will make the script create bad code.
    >
    > -------START TSQL
    >
    >
    > SET NOCOUNT ON
    > print '-- You probably should set your "Results To Text"'
    > print '-- You need to copy and paste the OUTPUT of this query..and run
    > against a different aspnetdb membership/roles db'
    > print ''
    > print ''
    > --**************************************************
    > print 'SET NOCOUNT ON'
    > --**************************************************
    > print '/*'
    > print '--These next delete lines are optional, but if you want a clean
    > transfer, you can run them (uncomment them)'
    > print 'delete from dbo.aspnet_Membership'
    > print 'delete from dbo.aspnet_UsersInRoles'
    > print 'delete from dbo.aspnet_Roles'
    > print 'delete from dbo.aspnet_Profile'
    > print 'delete from dbo.aspnet_Users'
    > print 'delete from dbo.aspnet_Applications'
    > print '*/'
    > print ''
    > print ''
    >
    > --**************************************************
    >
    > select
    > 'INSERT INTO dbo.aspnet_Applications (
    > ApplicationName,LoweredApplicationName,ApplicationId,[Description] ) values
    > (' as [--Comment],
    > char(39) + t1.ApplicationName + char(39) , ',' ,
    > char(39) + t1.LoweredApplicationName + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + t1.Description + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_Applications t1
    >
    > --**************************************************
    >
    > -- select top 1 * from dbo.aspnet_Roles
    > --ApplicationId,RoleId,RoleName,LoweredRoleName,[Description]
    > Select
    > 'INSERT INTO dbo.aspnet_Roles (
    > ApplicationId,RoleId,RoleName,LoweredRoleName,[Description] ) values (' as
    > [--Comment],
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.RoleId ) + char(39) , ',' ,
    > char(39) + t1.RoleName + char(39) , ',' ,
    > char(39) + t1.LoweredRoleName + char(39) , ',' ,
    > char(39) + t1.Description + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_Roles t1
    >
    > --**************************************************
    >
    > --select top 1 * from dbo.aspnet_Users
    > --ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    > Select
    > 'INSERT INTO dbo.aspnet_Users (
    > ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    > ) values (' as [--Comment] ,
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    > char(39) + t1.UserName + char(39) , ',' ,
    > char(39) + t1.LoweredUserName + char(39) , ',' ,
    > char(39) + t1.MobileAlias + char(39) , ',' ,
    > IsAnonymous, ',' ,
    > char(39) + convert(varchar(38) , t1.LastActivityDate ) + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_Users t1
    >
    > --**************************************************
    > print ''
    > print '--TO DO-- dbo.aspnet_Profile'
    > print '--I did not utilize the Profile information, so I didnt code it
    > up....you can follow the pattern and implement Profile information here'
    > print ''
    > print ''
    > --**************************************************
    > --select top 1 * from dbo.aspnet_UsersInRoles
    > --UserID, RoleID
    > Select
    > 'INSERT INTO dbo.aspnet_UsersInRoles ( UserID, RoleID ) values (' as
    > [--Comment],
    > char(39) + convert(varchar(38) , t1.UserID ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.RoleID ) + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_UsersInRoles t1
    >
    > --**************************************************
    >
    > -- select top 1 * from dbo.aspnet_Membership
    > --ApplicationId,UserId,Password,PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    > --ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,
    > --MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,
    > --IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,
    > --LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,
    > --Comment
    > Select
    > 'INSERT INTO dbo.aspnet_Membership (
    > ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    > ) values (' as [--Comment],
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    > char(39) + t1.[Password] + char(39) , ',' ,
    > PasswordFormat , ',' ,
    > char(39) + t1.PasswordSalt + char(39) , ',' ,
    > char(39) + t1.MobilePIN + char(39) , ',' ,
    > char(39) + t1.Email + char(39) , ',' ,
    > char(39) + t1.LoweredEmail + char(39) , ',' ,
    > char(39) + t1.PasswordQuestion + char(39) , ',' ,
    > char(39) + t1.PasswordAnswer + char(39) , ',' ,
    > IsApproved , ',' ,
    > IsLockedOut , ',' ,
    > char(39) + convert(varchar(38) , t1.CreateDate) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.LastLoginDate) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.LastPasswordChangedDate) + char(39) ,
    > ',' ,
    > char(39) + convert(varchar(38) , t1.LastLockoutDate) + char(39) , ',' ,
    > FailedPasswordAttemptCount , ',' ,
    > char(39) + convert(varchar(38) , t1.FailedPasswordAttemptWindowStart) +
    > char(39) , ',' ,
    > FailedPasswordAnswerAttemptCount , ',' ,
    > char(39) + convert(varchar(38) ,t1.FailedPasswordAnswerAttemptWindowStart )
    > + char(39) , ',' ,
    > char(39) + convert(varchar(1028) , t1.Comment) + char(39)
    > , ')'
    >
    > FROM
    > dbo.aspnet_Membership t1
    >
    > --**************************************************
    >
    > print ''
    > print ''
    > print 'Select * from dbo.aspnet_Applications'
    > print 'Select * from dbo.aspnet_Users'
    > print 'Select * from dbo.aspnet_Profile'
    > print 'Select * from dbo.aspnet_Roles'
    > print 'Select * from dbo.aspnet_UsersInRoles'
    > print 'Select * from dbo.aspnet_Membership'
    >
    >
    >
    >
    > "hfdev" <> wrote in message
    > news:...
    > > Hello,
    > >
    > > I have a web application that makes use of the SQL Membership and Role
    > > providers. My app has admin screens to manage users (membership), roles,
    > > and
    > > supplementary user data. I have just deployed the application to a
    > > production server.
    > >
    > > My Question: How do I create the initial Admin role and user in the
    > > clean/unpopulated database that has the Membership and Role schema on this
    > > production server?
    > >
    > > The production server does not have the Visual Studio solution/project, it
    > > only has the project deliverables.
    > >
    > > Thanks for your help,
    > >
    > > Josh Blair
    > > HydraForce, Inc.

    >
    >
    >
     
    hfdev, Nov 20, 2007
    #3
  4. hfdev

    hfdev Guest

    Re: Membership Provider: how to create initial user acct. after de

    Sloan,

    Your TSQL generator worked perfectly. Thanks very much.

    Just a note, my collation must be different than yours and I had to change
    UserID to UserId and RoleID to RoleId.

    Again, thanks for your help,

    Josh Blair
    HydraForce, Inc.

    "sloan" wrote:

    > You can either write your own little application.
    >
    > I've written one, which uses 3 text files as the source. Users, Roles, and
    > UserToRoles.
    > This is "owned' code so I can't share it.
    > I wrote a console application, which reads these 3 files..and then does the
    > Membership.CreateUser type calls through it.
    >
    >
    > ...
    >
    > You can use the "create tsql code" helper I made:
    > THis will generate the Tsql code...that you can run on the production
    > database.
    >
    >
    >
    >
    >
    > Note, this code generates tsql code. It does not actually perform the
    > inserts.
    >
    > Also note that an Application/RoleName/UserName with a single quote in the
    > name will make the script create bad code.
    >
    > -------START TSQL
    >
    >
    > SET NOCOUNT ON
    > print '-- You probably should set your "Results To Text"'
    > print '-- You need to copy and paste the OUTPUT of this query..and run
    > against a different aspnetdb membership/roles db'
    > print ''
    > print ''
    > --**************************************************
    > print 'SET NOCOUNT ON'
    > --**************************************************
    > print '/*'
    > print '--These next delete lines are optional, but if you want a clean
    > transfer, you can run them (uncomment them)'
    > print 'delete from dbo.aspnet_Membership'
    > print 'delete from dbo.aspnet_UsersInRoles'
    > print 'delete from dbo.aspnet_Roles'
    > print 'delete from dbo.aspnet_Profile'
    > print 'delete from dbo.aspnet_Users'
    > print 'delete from dbo.aspnet_Applications'
    > print '*/'
    > print ''
    > print ''
    >
    > --**************************************************
    >
    > select
    > 'INSERT INTO dbo.aspnet_Applications (
    > ApplicationName,LoweredApplicationName,ApplicationId,[Description] ) values
    > (' as [--Comment],
    > char(39) + t1.ApplicationName + char(39) , ',' ,
    > char(39) + t1.LoweredApplicationName + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + t1.Description + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_Applications t1
    >
    > --**************************************************
    >
    > -- select top 1 * from dbo.aspnet_Roles
    > --ApplicationId,RoleId,RoleName,LoweredRoleName,[Description]
    > Select
    > 'INSERT INTO dbo.aspnet_Roles (
    > ApplicationId,RoleId,RoleName,LoweredRoleName,[Description] ) values (' as
    > [--Comment],
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.RoleId ) + char(39) , ',' ,
    > char(39) + t1.RoleName + char(39) , ',' ,
    > char(39) + t1.LoweredRoleName + char(39) , ',' ,
    > char(39) + t1.Description + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_Roles t1
    >
    > --**************************************************
    >
    > --select top 1 * from dbo.aspnet_Users
    > --ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    > Select
    > 'INSERT INTO dbo.aspnet_Users (
    > ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    > ) values (' as [--Comment] ,
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    > char(39) + t1.UserName + char(39) , ',' ,
    > char(39) + t1.LoweredUserName + char(39) , ',' ,
    > char(39) + t1.MobileAlias + char(39) , ',' ,
    > IsAnonymous, ',' ,
    > char(39) + convert(varchar(38) , t1.LastActivityDate ) + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_Users t1
    >
    > --**************************************************
    > print ''
    > print '--TO DO-- dbo.aspnet_Profile'
    > print '--I did not utilize the Profile information, so I didnt code it
    > up....you can follow the pattern and implement Profile information here'
    > print ''
    > print ''
    > --**************************************************
    > --select top 1 * from dbo.aspnet_UsersInRoles
    > --UserID, RoleID
    > Select
    > 'INSERT INTO dbo.aspnet_UsersInRoles ( UserID, RoleID ) values (' as
    > [--Comment],
    > char(39) + convert(varchar(38) , t1.UserID ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.RoleID ) + char(39)
    > , ')'
    > FROM
    > dbo.aspnet_UsersInRoles t1
    >
    > --**************************************************
    >
    > -- select top 1 * from dbo.aspnet_Membership
    > --ApplicationId,UserId,Password,PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    > --ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,
    > --MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,
    > --IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,
    > --LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,
    > --Comment
    > Select
    > 'INSERT INTO dbo.aspnet_Membership (
    > ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    > ) values (' as [--Comment],
    > char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    > char(39) + t1.[Password] + char(39) , ',' ,
    > PasswordFormat , ',' ,
    > char(39) + t1.PasswordSalt + char(39) , ',' ,
    > char(39) + t1.MobilePIN + char(39) , ',' ,
    > char(39) + t1.Email + char(39) , ',' ,
    > char(39) + t1.LoweredEmail + char(39) , ',' ,
    > char(39) + t1.PasswordQuestion + char(39) , ',' ,
    > char(39) + t1.PasswordAnswer + char(39) , ',' ,
    > IsApproved , ',' ,
    > IsLockedOut , ',' ,
    > char(39) + convert(varchar(38) , t1.CreateDate) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.LastLoginDate) + char(39) , ',' ,
    > char(39) + convert(varchar(38) , t1.LastPasswordChangedDate) + char(39) ,
    > ',' ,
    > char(39) + convert(varchar(38) , t1.LastLockoutDate) + char(39) , ',' ,
    > FailedPasswordAttemptCount , ',' ,
    > char(39) + convert(varchar(38) , t1.FailedPasswordAttemptWindowStart) +
    > char(39) , ',' ,
    > FailedPasswordAnswerAttemptCount , ',' ,
    > char(39) + convert(varchar(38) ,t1.FailedPasswordAnswerAttemptWindowStart )
    > + char(39) , ',' ,
    > char(39) + convert(varchar(1028) , t1.Comment) + char(39)
    > , ')'
    >
    > FROM
    > dbo.aspnet_Membership t1
    >
    > --**************************************************
    >
    > print ''
    > print ''
    > print 'Select * from dbo.aspnet_Applications'
    > print 'Select * from dbo.aspnet_Users'
    > print 'Select * from dbo.aspnet_Profile'
    > print 'Select * from dbo.aspnet_Roles'
    > print 'Select * from dbo.aspnet_UsersInRoles'
    > print 'Select * from dbo.aspnet_Membership'
    >
    >
    >
    >
    > "hfdev" <> wrote in message
    > news:...
    > > Hello,
    > >
    > > I have a web application that makes use of the SQL Membership and Role
    > > providers. My app has admin screens to manage users (membership), roles,
    > > and
    > > supplementary user data. I have just deployed the application to a
    > > production server.
    > >
    > > My Question: How do I create the initial Admin role and user in the
    > > clean/unpopulated database that has the Membership and Role schema on this
    > > production server?
    > >
    > > The production server does not have the Visual Studio solution/project, it
    > > only has the project deliverables.
    > >
    > > Thanks for your help,
    > >
    > > Josh Blair
    > > HydraForce, Inc.

    >
    >
    >
     
    hfdev, Nov 20, 2007
    #4
  5. hfdev

    sloan Guest

    Re: Membership Provider: how to create initial user acct. after de

    Yeah, I need to blog that TSQL code generator thing.

    Can you privately email me your case sensitive version?

    ...

    You CAN put membership info inside a app.config file for a console
    application.
    You CAN add a reference to System.Web inside a Console Application as well.

    I'm not sure how your last question played out, I just know it worked.
    (Aka, when I said the appname was "/MySuperCoolApp", an entry got written to
    the aspnet_Application table.

    ...

    But if you got the tsql thing working, then its a moot issue anyways.


    Glad it worked.





    "hfdev" <> wrote in message
    news:D...
    > Sloan,
    >
    > Thanks very much for your insight. I would like to build a console app
    > like
    > the one that you mentioned (because I have 4 such application to deploy
    > with
    > many user accounts that I need to pull from legacy applications'
    > databases.
    > I wasn't sure how a console app could use the provider APIs because of how
    > the APIs are configured via the web.config? Can you use the Membership
    > and
    > Role configuration xml tags in an app.config? Also, does your console app
    > create the aspnet_Applications record? If so, do you generate the GUID?
    >
    > Thanks,
    >
    > Josh Blair
    > HydraForce, Inc.
    >
    > "sloan" wrote:
    >
    >> You can either write your own little application.
    >>
    >> I've written one, which uses 3 text files as the source. Users, Roles,
    >> and
    >> UserToRoles.
    >> This is "owned' code so I can't share it.
    >> I wrote a console application, which reads these 3 files..and then does
    >> the
    >> Membership.CreateUser type calls through it.
    >>
    >>
    >> ...
    >>
    >> You can use the "create tsql code" helper I made:
    >> THis will generate the Tsql code...that you can run on the production
    >> database.
    >>
    >>
    >>
    >>
    >>
    >> Note, this code generates tsql code. It does not actually perform the
    >> inserts.
    >>
    >> Also note that an Application/RoleName/UserName with a single quote in
    >> the
    >> name will make the script create bad code.
    >>
    >> -------START TSQL
    >>
    >>
    >> SET NOCOUNT ON
    >> print '-- You probably should set your "Results To Text"'
    >> print '-- You need to copy and paste the OUTPUT of this query..and run
    >> against a different aspnetdb membership/roles db'
    >> print ''
    >> print ''
    >> --**************************************************
    >> print 'SET NOCOUNT ON'
    >> --**************************************************
    >> print '/*'
    >> print '--These next delete lines are optional, but if you want a clean
    >> transfer, you can run them (uncomment them)'
    >> print 'delete from dbo.aspnet_Membership'
    >> print 'delete from dbo.aspnet_UsersInRoles'
    >> print 'delete from dbo.aspnet_Roles'
    >> print 'delete from dbo.aspnet_Profile'
    >> print 'delete from dbo.aspnet_Users'
    >> print 'delete from dbo.aspnet_Applications'
    >> print '*/'
    >> print ''
    >> print ''
    >>
    >> --**************************************************
    >>
    >> select
    >> 'INSERT INTO dbo.aspnet_Applications (
    >> ApplicationName,LoweredApplicationName,ApplicationId,[Description] )
    >> values
    >> (' as [--Comment],
    >> char(39) + t1.ApplicationName + char(39) , ',' ,
    >> char(39) + t1.LoweredApplicationName + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + t1.Description + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_Applications t1
    >>
    >> --**************************************************
    >>
    >> -- select top 1 * from dbo.aspnet_Roles
    >> --ApplicationId,RoleId,RoleName,LoweredRoleName,[Description]
    >> Select
    >> 'INSERT INTO dbo.aspnet_Roles (
    >> ApplicationId,RoleId,RoleName,LoweredRoleName,[Description] ) values ('
    >> as
    >> [--Comment],
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.RoleId ) + char(39) , ',' ,
    >> char(39) + t1.RoleName + char(39) , ',' ,
    >> char(39) + t1.LoweredRoleName + char(39) , ',' ,
    >> char(39) + t1.Description + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_Roles t1
    >>
    >> --**************************************************
    >>
    >> --select top 1 * from dbo.aspnet_Users
    >> --ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    >> Select
    >> 'INSERT INTO dbo.aspnet_Users (
    >> ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    >> ) values (' as [--Comment] ,
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    >> char(39) + t1.UserName + char(39) , ',' ,
    >> char(39) + t1.LoweredUserName + char(39) , ',' ,
    >> char(39) + t1.MobileAlias + char(39) , ',' ,
    >> IsAnonymous, ',' ,
    >> char(39) + convert(varchar(38) , t1.LastActivityDate ) + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_Users t1
    >>
    >> --**************************************************
    >> print ''
    >> print '--TO DO-- dbo.aspnet_Profile'
    >> print '--I did not utilize the Profile information, so I didnt code it
    >> up....you can follow the pattern and implement Profile information here'
    >> print ''
    >> print ''
    >> --**************************************************
    >> --select top 1 * from dbo.aspnet_UsersInRoles
    >> --UserID, RoleID
    >> Select
    >> 'INSERT INTO dbo.aspnet_UsersInRoles ( UserID, RoleID ) values (' as
    >> [--Comment],
    >> char(39) + convert(varchar(38) , t1.UserID ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.RoleID ) + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_UsersInRoles t1
    >>
    >> --**************************************************
    >>
    >> -- select top 1 * from dbo.aspnet_Membership
    >> --ApplicationId,UserId,Password,PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    >> --ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,
    >> --MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,
    >> --IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,
    >> --LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,
    >> --Comment
    >> Select
    >> 'INSERT INTO dbo.aspnet_Membership (
    >> ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    >> ) values (' as [--Comment],
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    >> char(39) + t1.[Password] + char(39) , ',' ,
    >> PasswordFormat , ',' ,
    >> char(39) + t1.PasswordSalt + char(39) , ',' ,
    >> char(39) + t1.MobilePIN + char(39) , ',' ,
    >> char(39) + t1.Email + char(39) , ',' ,
    >> char(39) + t1.LoweredEmail + char(39) , ',' ,
    >> char(39) + t1.PasswordQuestion + char(39) , ',' ,
    >> char(39) + t1.PasswordAnswer + char(39) , ',' ,
    >> IsApproved , ',' ,
    >> IsLockedOut , ',' ,
    >> char(39) + convert(varchar(38) , t1.CreateDate) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.LastLoginDate) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.LastPasswordChangedDate) + char(39)
    >> ,
    >> ',' ,
    >> char(39) + convert(varchar(38) , t1.LastLockoutDate) + char(39) , ',' ,
    >> FailedPasswordAttemptCount , ',' ,
    >> char(39) + convert(varchar(38) , t1.FailedPasswordAttemptWindowStart) +
    >> char(39) , ',' ,
    >> FailedPasswordAnswerAttemptCount , ',' ,
    >> char(39) + convert(varchar(38)
    >> ,t1.FailedPasswordAnswerAttemptWindowStart )
    >> + char(39) , ',' ,
    >> char(39) + convert(varchar(1028) , t1.Comment) + char(39)
    >> , ')'
    >>
    >> FROM
    >> dbo.aspnet_Membership t1
    >>
    >> --**************************************************
    >>
    >> print ''
    >> print ''
    >> print 'Select * from dbo.aspnet_Applications'
    >> print 'Select * from dbo.aspnet_Users'
    >> print 'Select * from dbo.aspnet_Profile'
    >> print 'Select * from dbo.aspnet_Roles'
    >> print 'Select * from dbo.aspnet_UsersInRoles'
    >> print 'Select * from dbo.aspnet_Membership'
    >>
    >>
    >>
    >>
    >> "hfdev" <> wrote in message
    >> news:...
    >> > Hello,
    >> >
    >> > I have a web application that makes use of the SQL Membership and Role
    >> > providers. My app has admin screens to manage users (membership),
    >> > roles,
    >> > and
    >> > supplementary user data. I have just deployed the application to a
    >> > production server.
    >> >
    >> > My Question: How do I create the initial Admin role and user in the
    >> > clean/unpopulated database that has the Membership and Role schema on
    >> > this
    >> > production server?
    >> >
    >> > The production server does not have the Visual Studio solution/project,
    >> > it
    >> > only has the project deliverables.
    >> >
    >> > Thanks for your help,
    >> >
    >> > Josh Blair
    >> > HydraForce, Inc.

    >>
    >>
    >>
     
    sloan, Nov 20, 2007
    #5
  6. hfdev

    sloan Guest

    Re: Membership Provider: how to create initial user acct. after de

    Josh.

    Here is the crux of the logic. (See snipplets below)

    I have 3 text files.
    Users.txt
    Roles.txt
    UserToRolesMapping.txt


    The 3 snipplets below are in 3 class files.
    The code not seen (but not hard) is to read the text files, and read
    contents...
    Then you just loop on the contents and call the 3 individual snipplets
    below.

    Its not rocket science. Basically, after you figure out you can add the
    System.Web reference to your app.config AND throw in the membership
    xml/stuff into the app.config of the console application, you're off and
    running.


    I find on a new deployment when the users are unknown, the work for this
    application is nice.
    I find when I have been developing and have a known/core set of info, the
    TSQL code generator works best.

    But I have both, so I'm ready for either situation.




    private List<string> _allImportedRoles = new List<string>() ;
    private void CreateRole(string roleName )
    {

    if(!Roles.RoleExists (roleName ))
    {
    Roles.CreateRole(roleName);
    this._allImportedRoles.Add(roleName);
    }
    }






    private List<string> _allImportedUser = new List<string>();

    private void CreateUser(string userName, string password)
    {

    if (null==System.Web.Security.Membership.GetUser (userName))
    {
    System.Web.Security.Membership.CreateUser(userName,
    password, userName + "@" + userName+ ".com");
    this._allImportedUser.Add(userName);
    }

    }






    private List<string> _actuallyImportedList = new List<string>();

    //Here I read the text file, and put the MyUser,MyRole combos in a
    List<string>, then I loop over these values, and split the User
    //and Role via a comma delimiter.....

    if (!Roles.IsUserInRole (userName, roleName ))
    {
    Roles.AddUsersToRole(new string[] { userName } ,
    roleName );
    _actuallyImportedList.Add(string.Format("{0} / {1}",
    userName, roleName));
    }










    "hfdev" <> wrote in message
    news:D...
    > Sloan,
    >
    > Thanks very much for your insight. I would like to build a console app
    > like
    > the one that you mentioned (because I have 4 such application to deploy
    > with
    > many user accounts that I need to pull from legacy applications'
    > databases.
    > I wasn't sure how a console app could use the provider APIs because of how
    > the APIs are configured via the web.config? Can you use the Membership
    > and
    > Role configuration xml tags in an app.config? Also, does your console app
    > create the aspnet_Applications record? If so, do you generate the GUID?
    >
    > Thanks,
    >
    > Josh Blair
    > HydraForce, Inc.
    >
    > "sloan" wrote:
    >
    >> You can either write your own little application.
    >>
    >> I've written one, which uses 3 text files as the source. Users, Roles,
    >> and
    >> UserToRoles.
    >> This is "owned' code so I can't share it.
    >> I wrote a console application, which reads these 3 files..and then does
    >> the
    >> Membership.CreateUser type calls through it.
    >>
    >>
    >> ...
    >>
    >> You can use the "create tsql code" helper I made:
    >> THis will generate the Tsql code...that you can run on the production
    >> database.
    >>
    >>
    >>
    >>
    >>
    >> Note, this code generates tsql code. It does not actually perform the
    >> inserts.
    >>
    >> Also note that an Application/RoleName/UserName with a single quote in
    >> the
    >> name will make the script create bad code.
    >>
    >> -------START TSQL
    >>
    >>
    >> SET NOCOUNT ON
    >> print '-- You probably should set your "Results To Text"'
    >> print '-- You need to copy and paste the OUTPUT of this query..and run
    >> against a different aspnetdb membership/roles db'
    >> print ''
    >> print ''
    >> --**************************************************
    >> print 'SET NOCOUNT ON'
    >> --**************************************************
    >> print '/*'
    >> print '--These next delete lines are optional, but if you want a clean
    >> transfer, you can run them (uncomment them)'
    >> print 'delete from dbo.aspnet_Membership'
    >> print 'delete from dbo.aspnet_UsersInRoles'
    >> print 'delete from dbo.aspnet_Roles'
    >> print 'delete from dbo.aspnet_Profile'
    >> print 'delete from dbo.aspnet_Users'
    >> print 'delete from dbo.aspnet_Applications'
    >> print '*/'
    >> print ''
    >> print ''
    >>
    >> --**************************************************
    >>
    >> select
    >> 'INSERT INTO dbo.aspnet_Applications (
    >> ApplicationName,LoweredApplicationName,ApplicationId,[Description] )
    >> values
    >> (' as [--Comment],
    >> char(39) + t1.ApplicationName + char(39) , ',' ,
    >> char(39) + t1.LoweredApplicationName + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + t1.Description + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_Applications t1
    >>
    >> --**************************************************
    >>
    >> -- select top 1 * from dbo.aspnet_Roles
    >> --ApplicationId,RoleId,RoleName,LoweredRoleName,[Description]
    >> Select
    >> 'INSERT INTO dbo.aspnet_Roles (
    >> ApplicationId,RoleId,RoleName,LoweredRoleName,[Description] ) values ('
    >> as
    >> [--Comment],
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.RoleId ) + char(39) , ',' ,
    >> char(39) + t1.RoleName + char(39) , ',' ,
    >> char(39) + t1.LoweredRoleName + char(39) , ',' ,
    >> char(39) + t1.Description + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_Roles t1
    >>
    >> --**************************************************
    >>
    >> --select top 1 * from dbo.aspnet_Users
    >> --ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    >> Select
    >> 'INSERT INTO dbo.aspnet_Users (
    >> ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    >> ) values (' as [--Comment] ,
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    >> char(39) + t1.UserName + char(39) , ',' ,
    >> char(39) + t1.LoweredUserName + char(39) , ',' ,
    >> char(39) + t1.MobileAlias + char(39) , ',' ,
    >> IsAnonymous, ',' ,
    >> char(39) + convert(varchar(38) , t1.LastActivityDate ) + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_Users t1
    >>
    >> --**************************************************
    >> print ''
    >> print '--TO DO-- dbo.aspnet_Profile'
    >> print '--I did not utilize the Profile information, so I didnt code it
    >> up....you can follow the pattern and implement Profile information here'
    >> print ''
    >> print ''
    >> --**************************************************
    >> --select top 1 * from dbo.aspnet_UsersInRoles
    >> --UserID, RoleID
    >> Select
    >> 'INSERT INTO dbo.aspnet_UsersInRoles ( UserID, RoleID ) values (' as
    >> [--Comment],
    >> char(39) + convert(varchar(38) , t1.UserID ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.RoleID ) + char(39)
    >> , ')'
    >> FROM
    >> dbo.aspnet_UsersInRoles t1
    >>
    >> --**************************************************
    >>
    >> -- select top 1 * from dbo.aspnet_Membership
    >> --ApplicationId,UserId,Password,PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    >> --ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,
    >> --MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,
    >> --IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,
    >> --LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,
    >> --Comment
    >> Select
    >> 'INSERT INTO dbo.aspnet_Membership (
    >> ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    >> ) values (' as [--Comment],
    >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    >> char(39) + t1.[Password] + char(39) , ',' ,
    >> PasswordFormat , ',' ,
    >> char(39) + t1.PasswordSalt + char(39) , ',' ,
    >> char(39) + t1.MobilePIN + char(39) , ',' ,
    >> char(39) + t1.Email + char(39) , ',' ,
    >> char(39) + t1.LoweredEmail + char(39) , ',' ,
    >> char(39) + t1.PasswordQuestion + char(39) , ',' ,
    >> char(39) + t1.PasswordAnswer + char(39) , ',' ,
    >> IsApproved , ',' ,
    >> IsLockedOut , ',' ,
    >> char(39) + convert(varchar(38) , t1.CreateDate) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.LastLoginDate) + char(39) , ',' ,
    >> char(39) + convert(varchar(38) , t1.LastPasswordChangedDate) + char(39)
    >> ,
    >> ',' ,
    >> char(39) + convert(varchar(38) , t1.LastLockoutDate) + char(39) , ',' ,
    >> FailedPasswordAttemptCount , ',' ,
    >> char(39) + convert(varchar(38) , t1.FailedPasswordAttemptWindowStart) +
    >> char(39) , ',' ,
    >> FailedPasswordAnswerAttemptCount , ',' ,
    >> char(39) + convert(varchar(38)
    >> ,t1.FailedPasswordAnswerAttemptWindowStart )
    >> + char(39) , ',' ,
    >> char(39) + convert(varchar(1028) , t1.Comment) + char(39)
    >> , ')'
    >>
    >> FROM
    >> dbo.aspnet_Membership t1
    >>
    >> --**************************************************
    >>
    >> print ''
    >> print ''
    >> print 'Select * from dbo.aspnet_Applications'
    >> print 'Select * from dbo.aspnet_Users'
    >> print 'Select * from dbo.aspnet_Profile'
    >> print 'Select * from dbo.aspnet_Roles'
    >> print 'Select * from dbo.aspnet_UsersInRoles'
    >> print 'Select * from dbo.aspnet_Membership'
    >>
    >>
    >>
    >>
    >> "hfdev" <> wrote in message
    >> news:...
    >> > Hello,
    >> >
    >> > I have a web application that makes use of the SQL Membership and Role
    >> > providers. My app has admin screens to manage users (membership),
    >> > roles,
    >> > and
    >> > supplementary user data. I have just deployed the application to a
    >> > production server.
    >> >
    >> > My Question: How do I create the initial Admin role and user in the
    >> > clean/unpopulated database that has the Membership and Role schema on
    >> > this
    >> > production server?
    >> >
    >> > The production server does not have the Visual Studio solution/project,
    >> > it
    >> > only has the project deliverables.
    >> >
    >> > Thanks for your help,
    >> >
    >> > Josh Blair
    >> > HydraForce, Inc.

    >>
    >>
    >>
     
    sloan, Nov 20, 2007
    #6
  7. hfdev

    hfdev Guest

    Re: Membership Provider: how to create initial user acct. after de

    Sloan,

    I just sent off the case insensetive version to your address. Let us know
    if you post the solution on your blog.

    Thanks again,

    Josh Blair
    HydraForce, Inc.

    "sloan" wrote:

    > Yeah, I need to blog that TSQL code generator thing.
    >
    > Can you privately email me your case sensitive version?
    >
    > ...
    >
    > You CAN put membership info inside a app.config file for a console
    > application.
    > You CAN add a reference to System.Web inside a Console Application as well.
    >
    > I'm not sure how your last question played out, I just know it worked.
    > (Aka, when I said the appname was "/MySuperCoolApp", an entry got written to
    > the aspnet_Application table.
    >
    > ...
    >
    > But if you got the tsql thing working, then its a moot issue anyways.
    >
    >
    > Glad it worked.
    >
    >
    >
    >
    >
    > "hfdev" <> wrote in message
    > news:D...
    > > Sloan,
    > >
    > > Thanks very much for your insight. I would like to build a console app
    > > like
    > > the one that you mentioned (because I have 4 such application to deploy
    > > with
    > > many user accounts that I need to pull from legacy applications'
    > > databases.
    > > I wasn't sure how a console app could use the provider APIs because of how
    > > the APIs are configured via the web.config? Can you use the Membership
    > > and
    > > Role configuration xml tags in an app.config? Also, does your console app
    > > create the aspnet_Applications record? If so, do you generate the GUID?
    > >
    > > Thanks,
    > >
    > > Josh Blair
    > > HydraForce, Inc.
    > >
    > > "sloan" wrote:
    > >
    > >> You can either write your own little application.
    > >>
    > >> I've written one, which uses 3 text files as the source. Users, Roles,
    > >> and
    > >> UserToRoles.
    > >> This is "owned' code so I can't share it.
    > >> I wrote a console application, which reads these 3 files..and then does
    > >> the
    > >> Membership.CreateUser type calls through it.
    > >>
    > >>
    > >> ...
    > >>
    > >> You can use the "create tsql code" helper I made:
    > >> THis will generate the Tsql code...that you can run on the production
    > >> database.
    > >>
    > >>
    > >>
    > >>
    > >>
    > >> Note, this code generates tsql code. It does not actually perform the
    > >> inserts.
    > >>
    > >> Also note that an Application/RoleName/UserName with a single quote in
    > >> the
    > >> name will make the script create bad code.
    > >>
    > >> -------START TSQL
    > >>
    > >>
    > >> SET NOCOUNT ON
    > >> print '-- You probably should set your "Results To Text"'
    > >> print '-- You need to copy and paste the OUTPUT of this query..and run
    > >> against a different aspnetdb membership/roles db'
    > >> print ''
    > >> print ''
    > >> --**************************************************
    > >> print 'SET NOCOUNT ON'
    > >> --**************************************************
    > >> print '/*'
    > >> print '--These next delete lines are optional, but if you want a clean
    > >> transfer, you can run them (uncomment them)'
    > >> print 'delete from dbo.aspnet_Membership'
    > >> print 'delete from dbo.aspnet_UsersInRoles'
    > >> print 'delete from dbo.aspnet_Roles'
    > >> print 'delete from dbo.aspnet_Profile'
    > >> print 'delete from dbo.aspnet_Users'
    > >> print 'delete from dbo.aspnet_Applications'
    > >> print '*/'
    > >> print ''
    > >> print ''
    > >>
    > >> --**************************************************
    > >>
    > >> select
    > >> 'INSERT INTO dbo.aspnet_Applications (
    > >> ApplicationName,LoweredApplicationName,ApplicationId,[Description] )
    > >> values
    > >> (' as [--Comment],
    > >> char(39) + t1.ApplicationName + char(39) , ',' ,
    > >> char(39) + t1.LoweredApplicationName + char(39) , ',' ,
    > >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > >> char(39) + t1.Description + char(39)
    > >> , ')'
    > >> FROM
    > >> dbo.aspnet_Applications t1
    > >>
    > >> --**************************************************
    > >>
    > >> -- select top 1 * from dbo.aspnet_Roles
    > >> --ApplicationId,RoleId,RoleName,LoweredRoleName,[Description]
    > >> Select
    > >> 'INSERT INTO dbo.aspnet_Roles (
    > >> ApplicationId,RoleId,RoleName,LoweredRoleName,[Description] ) values ('
    > >> as
    > >> [--Comment],
    > >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > >> char(39) + convert(varchar(38) , t1.RoleId ) + char(39) , ',' ,
    > >> char(39) + t1.RoleName + char(39) , ',' ,
    > >> char(39) + t1.LoweredRoleName + char(39) , ',' ,
    > >> char(39) + t1.Description + char(39)
    > >> , ')'
    > >> FROM
    > >> dbo.aspnet_Roles t1
    > >>
    > >> --**************************************************
    > >>
    > >> --select top 1 * from dbo.aspnet_Users
    > >> --ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    > >> Select
    > >> 'INSERT INTO dbo.aspnet_Users (
    > >> ApplicationId,UserId,UserName,LoweredUserName,MobileAlias,IsAnonymous,LastActivityDate
    > >> ) values (' as [--Comment] ,
    > >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > >> char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    > >> char(39) + t1.UserName + char(39) , ',' ,
    > >> char(39) + t1.LoweredUserName + char(39) , ',' ,
    > >> char(39) + t1.MobileAlias + char(39) , ',' ,
    > >> IsAnonymous, ',' ,
    > >> char(39) + convert(varchar(38) , t1.LastActivityDate ) + char(39)
    > >> , ')'
    > >> FROM
    > >> dbo.aspnet_Users t1
    > >>
    > >> --**************************************************
    > >> print ''
    > >> print '--TO DO-- dbo.aspnet_Profile'
    > >> print '--I did not utilize the Profile information, so I didnt code it
    > >> up....you can follow the pattern and implement Profile information here'
    > >> print ''
    > >> print ''
    > >> --**************************************************
    > >> --select top 1 * from dbo.aspnet_UsersInRoles
    > >> --UserID, RoleID
    > >> Select
    > >> 'INSERT INTO dbo.aspnet_UsersInRoles ( UserID, RoleID ) values (' as
    > >> [--Comment],
    > >> char(39) + convert(varchar(38) , t1.UserID ) + char(39) , ',' ,
    > >> char(39) + convert(varchar(38) , t1.RoleID ) + char(39)
    > >> , ')'
    > >> FROM
    > >> dbo.aspnet_UsersInRoles t1
    > >>
    > >> --**************************************************
    > >>
    > >> -- select top 1 * from dbo.aspnet_Membership
    > >> --ApplicationId,UserId,Password,PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    > >> --ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,
    > >> --MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,
    > >> --IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,
    > >> --LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,
    > >> --Comment
    > >> Select
    > >> 'INSERT INTO dbo.aspnet_Membership (
    > >> ApplicationId,UserId,[Password],PasswordFormat,PasswordSalt,MobilePIN,Email,LoweredEmail,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart,Comment
    > >> ) values (' as [--Comment],
    > >> char(39) + convert(varchar(38) , t1.ApplicationId ) + char(39) , ',' ,
    > >> char(39) + convert(varchar(38) , t1.UserId ) + char(39) , ',' ,
    > >> char(39) + t1.[Password] + char(39) , ',' ,
    > >> PasswordFormat , ',' ,
    > >> char(39) + t1.PasswordSalt + char(39) , ',' ,
    > >> char(39) + t1.MobilePIN + char(39) , ',' ,
    > >> char(39) + t1.Email + char(39) , ',' ,
    > >> char(39) + t1.LoweredEmail + char(39) , ',' ,
    > >> char(39) + t1.PasswordQuestion + char(39) , ',' ,
    > >> char(39) + t1.PasswordAnswer + char(39) , ',' ,
    > >> IsApproved , ',' ,
    > >> IsLockedOut , ',' ,
    > >> char(39) + convert(varchar(38) , t1.CreateDate) + char(39) , ',' ,
    > >> char(39) + convert(varchar(38) , t1.LastLoginDate) + char(39) , ',' ,
    > >> char(39) + convert(varchar(38) , t1.LastPasswordChangedDate) + char(39)
    > >> ,
    > >> ',' ,
    > >> char(39) + convert(varchar(38) , t1.LastLockoutDate) + char(39) , ',' ,
    > >> FailedPasswordAttemptCount , ',' ,
    > >> char(39) + convert(varchar(38) , t1.FailedPasswordAttemptWindowStart) +
    > >> char(39) , ',' ,
    > >> FailedPasswordAnswerAttemptCount , ',' ,
    > >> char(39) + convert(varchar(38)
    > >> ,t1.FailedPasswordAnswerAttemptWindowStart )
    > >> + char(39) , ',' ,
    > >> char(39) + convert(varchar(1028) , t1.Comment) + char(39)
    > >> , ')'
    > >>
    > >> FROM
    > >> dbo.aspnet_Membership t1
    > >>
    > >> --**************************************************
    > >>
    > >> print ''
    > >> print ''
    > >> print 'Select * from dbo.aspnet_Applications'
    > >> print 'Select * from dbo.aspnet_Users'
    > >> print 'Select * from dbo.aspnet_Profile'
    > >> print 'Select * from dbo.aspnet_Roles'
    > >> print 'Select * from dbo.aspnet_UsersInRoles'
    > >> print 'Select * from dbo.aspnet_Membership'
    > >>
    > >>
    > >>
    > >>
    > >> "hfdev" <> wrote in message
    > >> news:...
    > >> > Hello,
    > >> >
    > >> > I have a web application that makes use of the SQL Membership and Role
    > >> > providers. My app has admin screens to manage users (membership),
    > >> > roles,
    > >> > and
    > >> > supplementary user data. I have just deployed the application to a
    > >> > production server.
    > >> >
    > >> > My Question: How do I create the initial Admin role and user in the
    > >> > clean/unpopulated database that has the Membership and Role schema on
    > >> > this
    > >> > production server?
    > >> >
    > >> > The production server does not have the Visual Studio solution/project,
    > >> > it
    > >> > only has the project deliverables.
    > >> >
    > >> > Thanks for your help,
    > >> >
    > >> > Josh Blair
    > >> > HydraForce, Inc.
    > >>
    > >>
    > >>

    >
    >
    >
     
    hfdev, Nov 20, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chip Switzer

    merchant acct recommendations?

    Chip Switzer, Jul 7, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    426
    Chip Switzer
    Jul 7, 2003
  2. sloan
    Replies:
    5
    Views:
    1,519
    sloan
    Jun 4, 2006
  3. sloan
    Replies:
    1
    Views:
    521
    Chad Scharf
    Jul 3, 2007
  4. Chad
    Replies:
    1
    Views:
    108
  5. Burt
    Replies:
    0
    Views:
    132
Loading...

Share This Page