Hi Guys,I have a bit of a problem with a script that I’m trying to write which will move the GUID and Username from one table (tblUser) in a database (DB1) to another (UserCredential) on a different DB (DB2) (Both DB’s are on the same server). Now, that bit is simple, but as well as those 2 fields, ‘tblUser’ also has a ‘password’ column. The trickier bit is that I have a function which is going to change the regular password into a secure Salt and hashed password and gives me an output of 2 columns.Now, to go through the database and run the function for each user is going to need to use a cursor (I believe) so I have so far come up with the following script. However, I’ve not really used cursors before and am hitting a brick wall.How do I get the cursor to read from one table then insert the results into another? Any help would be hugely appreciated.DECLARE @UserId uniqueidentifier, @UserName nvarchar(50), @Salt nvarchar(50), @PasswordSalt nvarchar(80), @IsLocked int, @IsDeadLocked int, @LoginFailedAttempts int, @SecurityQuestionFailedAttempts int, @CreationDate datetime, @LastActivityDate datetime--Declare the CursorDECLARE c1 CURSOR FOR SELECT U.UserId ,U.UserName ,U.Password ,(SELECT Salt FROM msdb.dbo.GetSaltAndPassword(@Password)) Salt ,(SELECT HashedPassword FROM msdb.dbo.GetSaltAndPassword(@Password)) HashedPassword FROM DB1.ids.tblUser U WHERE UserId = @UserId-- Start the cursorOPEN c1 FETCH NEXT FROM c1 INTO @UserId, @UserName, @Salt, @PasswordSalt, @IsLocked, @IsDeadLocked, @LoginFailedAttempts, @SecurityQuestionFailedAttempts, @CreationDate, @LastActivityDateWHILE @@fetch_status = 0 BEGIN--Reset the variable to nothing each time round the loop SET @UserId = null BEGIN--Insert details into StandardUserCredential table INSERT INTO DB2.ids.UserCredential ( UserId ,UserName ,PasswordSalt ,[Password] ,IsLocked ,IsDeadLocked ,LoginFailedAttempts ,SecurityQuestionFailedAttempts ,CreationDate ,LastActivityDate ) VALUES (@UserId ,@UserName ,@Salt ,@PasswordSalt ,0 ,0 ,0 ,0 ,GETDATE() ,GETDATE() ) ENDFETCH NEXT FROM c1 INTO @UserId, @UserName, @Salt, @PasswordSalt, @IsLocked, @IsDeadLocked, @LoginFailedAttempts, @SecurityQuestionFailedAttempts, @CreationDate, @LastActivityDateENDCLOSE c1DEALLOCATE c1
Cursor with Insert and Function
Posted - 2010-08-11 : 12:00:19
