Bulk insert into ASPNET membership table

I have a set of ASPNETDB tables that I am trying to migrate exsiting user data to. I have over a 6000 usernames, passwords, etc. to bulk insert into the ASPNETDB from an older application.  I have inserted all 6000+ usernames into the aspnet_Users table, complete with new GUIDs for the userid. Now I need to insert the records into thr aspnet_Membership table. With this insert I want to generate a new password for the users. My thought is to set everyones password to one password, then later on handle everyone changing their password when they log in for the first time to the new application. I'm having trouble figuraing out how to inert the records from the aspnet_Users table with the hashed password and passwordsalt properly. I feel like I'm half done but I need to get the password and passwordsalt in correctly. Any help is appreciated.
dodgerfanAsked:
Who is Participating?
 
Marten RuneConnect With a Mentor SQL Expert/Infrastructure ArchitectCommented:
sp_help_revlogin gives the hash, but it wont work. You'll have to change password another way.

This is from sp_help_revlogin
***********************************************************
/* sp_help_revlogin script
** Generated Mar 10 2011 12:19PM on Q11109 */
 
 
-- Login: Testperson
CREATE LOGIN [Testperson] WITH PASSWORD = 0x01009FC60F6D54572F67B2F9A7AD0A85B17E39E04A9DF8C9B8B0 HASHED, SID = 0xDA96DE5683A9044881D6C330677B51A3, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
/* sp_help_revlogin script
** Generated Mar 10 2011 12:19PM on Q11109 */
 
 
-- Login: Testperson2
CREATE LOGIN [Testperson2] WITH PASSWORD = 0x0100841926CF01D34BF9F0174C3F090E11C59ED355891C3EF356 HASHED, SID = 0x104386C1FB8A9A49A3DDE9E7D2A4247F, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
***********************************************************

The same password is set for both Testperson and Testperson2. as you can see the hash is different. It's probably (and correctly) salted with the userSID and UserName. So this will not work!

You could batch set passwords later by scripting if you have all accounts.

By using:
USE [master]
GO
ALTER LOGIN [EnumeratSQLAccountsHere] WITH PASSWORD=N'YourDesiredPasswordHere'
GO

i e you need to loop this with different [EnumeratSQLAccountsHere] names.
You could use the exec command, like:

******************************************************
USE [master]
GO

declare @userNameVariable sysname

SET @userNameVariable = 'testperson'

exec('
ALTER LOGIN [' + @userNameVariable + '] WITH PASSWORD=N''YourDesiredPasswordHere''
')
******************************************************

You'll need to loop and alter:
SET @userNameVariable = 'testperson'
for each account name in your file.

This whole conclusion only applies to SQL Server accounts, not domain/nt local accounts.

Regarda Marten
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.