/*
The script below can be used as a helper script if you have been setting up Users/Roles/Applications on a development box, and need to transfer that information to a production aspnetdb 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.
 
*/

/*
 Change your output to "Results To Text"
 Under options....you may need to adjust the maximum column width property (Default of 512).  I set mine to 1024.

*/


-------START TSQL
 

SET NOCOUNT ON
print '-- You probably should set your "Results To Text"'
print '-- Change the maximum column width (under options) to a higher number like 1024.  (default is 512)'
print ''
print '-- You need to copy and paste the OUTPUT of this query..and run against a different aspnetdb membership/roles db'
print '-- Rememeber, this outputs TSQL code, it does not actually perform the inserts.'
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'