How to fix the users after SQL migration?

msSQL has system stored procedure called sp_change_users_login which can be used to report about broken user accounts and auto fix them.

After restoring a msSQL backup from one server on another server all user accounts will not be properly linked to the database. The sp_change_users_login stored procedure can help you find all users that are not linked and need to be fixed before they van be used again.

The command to use is: sp_change_users_login ‘report’
Executing this sp in ms SQL server management studio (Query Analyzer) will list all users that are not properly linked to the current database.

To fix these users run the same Stored Procedure with the ‘Auto_Fix’ parameter once for every user.

The command to use is: sp_change_users_login ‘Auto_Fix’, ‘[USERNAME]’
(replace [USERNAME] with the appropriate username)

To check if all users are properly linked after “auto_fixing” them, run the “report” again.

For more details about the sp_change_users_login see Microsoft Developer Network

Using EAN barcodes in Excel

VB code for generating the check digit for EAN8 and EAN13 barcodes
This code generates a checksum needed for EAN-8 and EAN-13 borcodes. The input (Base) is either a 7 or a 12 digit string. The number generated by the EAN function should be added to the end of the Base to make a valid barcode number.

Function EAN(Base As Variant) As Integer
    Z = 0: Odd = True
    For x = Len(Base) To 1 Step -1
        If Odd Then
            Z = Z + Mid(Base, x, 1) * 3
            Odd = False
            Z = Z + Mid(Base, x, 1)
            Odd = True
        End If
    EAN = 10 - Z Mod 10
    If EAN = 10 Then EAN = 0
End Function

Excel VBA conditional formating with more than 3 conditions

conditional formating in Excel on the basis of more than 3 conditions
How to change the font color and weight and shading of the first [X] cells in all rows in a given excel sheet? Most of the times the conditional formatting option in Excel is enough, but not when you have more than 3 conditions. Here is a simple piece of VB code to help you make it possible to use as many conditions as you can think of. It gives you the ability to change the font color, the background color and the font weight (bold or not) based on all the cases (conditions) you define. In this case it the formatting is changed when the first collumn (A:A) cantains an A, B, C, D, E or F.


Private Sub Worksheet_Change(ByVal Target As Range)
'fc = font color
'bc = background color
'bf = bold or not
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
'set d if Target is intersecting with column A
Set d = Intersect(Range("A:A"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
    'c = the value of the cell that just changed value
    'MsgBox UCase(c)
    Select Case UCase(c)
        Case "A"
            fc = 5: fb = True: bc = 10
        Case "B"
            fc = 6: fb = True: bc = 9
        Case "C"
            fc = 7: fb = True: bc = 8
        Case "D"
            fc = 8: fb = True: bc = 7
        Case "E"
            fc = 9: fb = True: bc = 6
        Case "F"
            fc = 10: fb = True: bc = 5
        Case Else
            fc = 1: fb = False: bc = xlNone
    End Select
'here 20 is the number of cells you want to change the format for each row

    With Cells(c.Row, 1).Resize(, 20)
'for each cell in the row change the font color, font weight and background color
        .Font.ColorIndex = fc
        .Font.Bold = fb
        .Interior.ColorIndex = bc
    End With
End Sub