Fixing Orphaned Users: The SQL

A very long time ago, C & I were developers consulting to a large corporation with a not-very-large IT department and a not-very-expert SQL Server DBA.

The work we did for these folks was partially .NET and partially Visual FoxPro, if you care.  You shouldn’t care.  What matters is that we were developers of applications, and we had to partner with a DBA for database stuff.  As probably does happen, in your world too.

Every time this DBA restored a .BAK file from one server to another, we had to go to him and request that he fix the SQL identities in the databases that were used by our applications’ connections.

Every time, this DBA would roll his eyes, apologize, and tediously go into the SQL Mgmt Studio dialogs to drop and re-add the users, who did exist on the server but had been disconnected from their database contexts by the restore from a “foreign” server.

There’s a better way.  Actually two.

It was a very long time ago, he was a rather under-informed DBA, and I had no idea whether anybody else didn’t know that there was a better way.  So, recently, I wrote in this blog asking whether it was a concern to anybody.  Nobody wrote to ask, and I thought it was too obvious to write about.  End of subject.

But then, even more recently, I encountered a document specifying exactly the same process that not-very-expert DBA used, complete with copious screen shots.  Sigh.

So, I’m going to quickly tell you how I do this.  Just in case there is anybody else.

When you’re restoring interactively

Execute the following system procedure against any database: 

sp_change_users_login @Action=‘Report’

… you will find that it gives you a list of orphaned users.

Upon examining that procedure you will find the code on which I based the following statement:

select UserName = name, UserSID =sid,
   ‘ALTER USER [‘+ name +‘] WITH LOGIN = [‘+ name +‘] ; ‘
   as FixUserStatement
   from sysusers
   where issqluser = 1
   and (sid is not null and sid<> 0x0)
   and (len(sid)<= 16)
   and suser_sname(sid) is null
order by name

 … which, when you run, you will find provides a list of ALTER USER statements. 

If you’re running the SELECT to a grid, select the FixUserStatement column, copy and paste the statements into another query window, execute what you’ve pasted, and you’re done. 

If there are a lot of such identities, you’ve saved yourself at least a tedious half hour over using the dialogs to drop and re-add.

From an SSIS package

What if you are doing the restore from an SSIS package as I was, in the environment of that suggested that earlier post?  In that case, I didn’t even want to fix all orphaned users indiscriminately. I wanted to stipulate which users got de-orphaned.

This is also pretty simple to do.  All it takes is a configurable package value with a delimited list of SQL identities you want fixed.  (If it’s ever all of them, you can derive the list of users within the package using the code you saw above, and put a “*” in the package value to let your package know.  If it’s always all of them, in your environment, don’t even bother with the package value.)

Write some VB or C# script in a script task to generate the ALTER USER statements based on the content of this variable, put the resulting statements in another package variable, execute the script in that value in a sql task later in the flow. 

Here’s my code for it, although I don’t think it’s any great shakes, in case you find it handy:

‘ This function is one of many that set up script statements
‘ in ReadWrite variables, during the initial steps of my package.
‘ The configuration string SQLUserLoginsToFix variable
‘ (holding the users we want to re-enable, configured outside the pkg)
‘ has a list that can be pipe (|), comma, or semi-colon-delimited,
‘ per our convention.  So there’s a little extra work for that here.

Private Function ConstructFixLoginsSQL() As String
   Dim s As System.Text.StringBuilder = New System.Text.StringBuilder()
   Dim l As String, d As String() = {“,”}, u As String, tu As String
   Dim n As String
   n = ReadWriteVariable(“TargetDbName”, Nothing)
   l = Trim(ReadWriteVariable(“SQLUserLoginsToFix”, Nothing))
   If l.Length > 0 Then
      l = l.Replace(“;”, “,”).Replace(“|”, “,”) ‘ allow different separators
      For Each u In l.Split(d, System.StringSplitOptions.RemoveEmptyEntries)
         tu = Trim(u) ‘ Just in case
         If Len(tu) > 0 Then
            s.Append(“ALTER USER [“ & tu & “] WITH LOGIN = “)
            s.Append(“[“ & tu & “] ;”)
         End If
      Next
   End If
   If s.Length = 0 Then
      s.Append(“SELECT 1 AS Dummy;”)
   End If
   Return s.ToString() ‘ this result is placed in a variable during this script setup task,
   ‘ and it is later executed in a SQL task after the database backup has been
   ‘ successfully copied to the target server and restored there.
End Function

‘ My function to handle the variable writing is as follows, although
‘ it’s nothing special and not germane:
Private Function ReadWriteVariable(ByVal varName As String, ByVal value As Object) As Object
   Dim rtnValue As Object = Nothing
   ‘Create a variables collection
   Dim var As Variables = Nothing
   Try
      If value Is Nothing Then
         Dts.VariableDispenser.LockOneForRead(varName, var)
         rtnValue = var(varName).Value
      Else
         Dts.VariableDispenser.LockOneForWrite(varName, var)
         var(varName).Value = value
      End If
   Catch ex As Exception
      Throw ex
      ‘ handle however appropriate in your env here
   Finally
      If Not var Is Nothing Then
         var.Unlock()
      End If
   End Try
   Return rtnValue
End Function 

… that’s all she wrote. 

Have fun!  I’ve got to go back to a huge amount of learning of my own, right now…