Need to load 200 users into Form security DB

[randy.buchholz]

Hi All,
I have an application that is using Form security and a SQL Server 2008
security db. I have a list of users that I need to get into the system. I
have not been able to find a way to generate the UserID or ApplicationId in
the user table to enable a direct load. The names are used/come from other
systems so I need them to be exact matches. (I don't want to trust the
users to enter them themselves.) If I can't find a way to do a direct load
I thought about exposing a grid view next to the New User control and
populating the user name (read only) when a name is selected. I haven't
seen a way to get to the user name field though. Last resort would be
writing my own (or finding) another Create User control. I would really
like to find a way to load directly. Or next, populate the name field in
the standard New User control.
TIA

 

[sloan]

Are you using the MembershipProvider?

I'm having trouble following your question a little.

 

[randy.buchholz]

I believe so. I'm not really familiar with the specifics of the security
modules yet; this is my start. I ran the script that creates a security
database in SQL2008 with 11 tables, Membership being one. Also Users,
Roles, Profiles. The only thing I have done for security so far is to use
the little web app (ASP.Net Configuration) that has a security tab,
Application Tab, and Provider Tab. My provider is AspNetSqlProvider. In
the config.net I point localserver to the security database
<add name="LocalSqlServer" connectionString="Data Source=ET;Initial
Catalog=ASPSecurity;Integrated Security=True"
I have only had a handfull of users thus far so I was managing them from
the security tab on ASP.Net Configuration. With 200 new users that approach
would take forever. I have the users in a spreadsheet and need to get them
into the Membershp (?) database. Thanks.

 

[sloan]

Ok, that makes more sense.

You have 2 choices.

Create your own custom membership provider. Which is not super trivial.
OR
You have to jam your information to the default/existing MembershipProvider.

Here is a "hint" blog entry.

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!160.entry

My situation is that I had a development database with some users/roles in
it. And then I had to move them to production.

But I think you can learn how to manually create your own TSQL to have your
own versions.

INSERT INTO dbo.aspnet_Users
INSERT INTO dbo.aspnet_UsersInRoles
INSERT INTO dbo.aspnet_Membership

Aka, follow my blog entry up to the point of the above.
Then you'll create your own INSERT statements...using data from your source.

You're basically writing to to write code (tsql in this case).

 

[randy.buchholz]

The problem I have here is that I'm not moving data, I'm creating it. I
only have names in a spreadsheet. I can build all of the fields except the
UserID and ApplicationID for the User table, and I need those to insert.
I'm not sure what other fields must also be calculated or how they are
calculated.

 

[sloan]

Yep. I realized you were creating new data. That's why I had the "Follow
up to this point, then you're going to have to do something different"


You'll already have the ApplicationID. Here is a looksie:
'Select * from dbo.aspnet_Applications'
That query will give you a ApplicationID.

The UserID is simply a Guid. newid() (tsql) or System.Guid.NewGuid()
(dotnet). It doesn't matter where it comes from, as long as the mappings
(FK to other tables) are correct.
You'll create your own UserID using the System.Guid.NewGuid() call in
DotNet. Note. System.Guid.NewGuid().ToString() has some otherloads to get
the formatting correct.
System.Guid.NewGuid().ToString("N") and System.Guid.NewGuid().ToString("D")
are popular ones for me.


If you have an excel file, then I'd write a small program to read its data,
using an IDataReader or a DataSet, and then create your TSQL.


Don't know how to read Excel in DotNet?
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
Run the downloadable code and click the "Excel" radio button, and follow the
path.


Basically, you'll have to write code to write code. You'll use your excel
file as the User(s) datasource.
Write your stuff out to a .txt file (you can call it .sql of course), and
you'll have a nice tsql file to run.

Make it super nice by doing some "if not exists (select null from
dbo.aspnet_Users u where u.UserName = 'jsmith' )'
and you'll be able to handle the files when they send you updates.


Writing code that writes code is a great pragmatic suggestion:

http://www.pragprog.com/the-pragmatic-programmer/extracts/tips
Write Code That Writes Code
Code generators increase your productivity and help avoid duplication.


Good luck. I don't think I have anymore I can tell you, you gotta go make
it happen.


AGain, read my first post, "you have 2 choices". So you either gotta make
this work, or write your own custom membership provider. Google "custom
membership provider" if you want to see what that entails.
Very "not trivial" in my estimation. Because I've written my own, fyi.

 

[randy.buchholz]

Got it, Thanks sloan. I reversed the database and didn't see an
identity/etc on the GUID I see what to do now. With the info you provided I
can use SSIS to to finish. Thanks again.

 

[Hillbilly]

You'll also want to go to ScottGu's blog and get the Table Profile Provider
as the default for Profiles stores all Profiles as a delineated mess, i.e.
it's FUBAR. Even the newer Table Profile Provider is still not relational
but at least was can access data in the columns using conventional methods.