{"id":43,"date":"2012-10-09T02:10:00","date_gmt":"2012-10-09T02:10:00","guid":{"rendered":"\/lisa\/post\/2012\/10\/09\/Fixing-Orphaned-Users-The-SQL.aspx"},"modified":"2012-10-09T02:10:00","modified_gmt":"2012-10-09T02:10:00","slug":"fixing-orphaned-users-the-sql","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2012\/10\/09\/fixing-orphaned-users-the-sql\/","title":{"rendered":"Fixing Orphaned Users: The SQL"},"content":{"rendered":"

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.<\/p>\n

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 <\/strong>happen, in your world too.<\/p>\n

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.<\/p>\n

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.<\/p>\n

There’s a better way.  Actually two.<\/h2>\n

It was<\/strong> a very long time ago, he was<\/strong> a rather under-informed DBA, and I had no idea whether anybody else didn’t know that there was a better way.  So, recently<\/a>, 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.<\/p>\n

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.<\/p>\n

So, I’m going to quickly tell you how I do this.  Just in case there is anybody else.<\/p>\n

When you’re restoring interactively<\/h2>\n

Execute the following system procedure against any database: <\/p>\n

sp_change_users_login <\/span><\/span>@Action<\/span>=<\/span><\/span>‘Report’<\/span><\/span><\/span><\/span><\/span><\/p>\n

… you will find that it gives you a list of orphaned users.<\/p>\n

Upon examining that procedure you will find the code on which I based the following statement:<\/p>\n

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

 … which, when you run, you will find provides a list of ALTER USER statements. <\/p>\n

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. <\/p>\n

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.<\/p>\n

From an SSIS package<\/h2>\n

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

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<\/strong> all of them, in your environment, don’t even bother with the package value.)<\/p>\n

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. <\/p>\n

Here’s my code for it, although I don’t think it’s any great shakes, in case you find it handy:<\/p>\n

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

Private <\/span><\/span>Function<\/span><\/span> ConstructFixLoginsSQL(<\/span>) <\/span>As <\/span><\/span>String
<\/span><\/span>   Dim<\/span><\/span> s <\/span>As<\/span><\/span> System.Text.StringBuilder = <\/span>New<\/span><\/span> System.Text.StringBuilder()
<\/span>   Dim<\/span><\/span> l <\/span>As <\/span><\/span>String<\/span><\/span>, d <\/span>As <\/span><\/span>String<\/span><\/span>() = {<\/span>“,”<\/span><\/span>}, u <\/span>As <\/span><\/span>String<\/span><\/span>, tu <\/span>As <\/span><\/span>String
<\/span><\/span>   Dim<\/span><\/span> n <\/span>As <\/span><\/span>String
<\/span><\/span>   n = ReadWriteVariable(<\/span>“TargetDbName”<\/span><\/span>, <\/span>Nothing<\/span><\/span>)
<\/span>   l = Trim(ReadWriteVariable(<\/span>“SQLUserLoginsToFix”<\/span><\/span>, <\/span>Nothing<\/span><\/span>))
<\/span>   If<\/span><\/span> l.Length > 0 <\/span>Then
<\/span><\/span>      l = l.Replace(<\/span>“;”<\/span><\/span>, <\/span>“,”<\/span><\/span>).Replace(<\/span>“|”<\/span><\/span>, <\/span>“,”<\/span><\/span>) <\/span>‘ allow different separators
<\/span><\/span>      For <\/span><\/span>Each<\/span><\/span> u <\/span>In<\/span><\/span> l.Split(d, System.StringSplitOptions.RemoveEmptyEntries)
         tu = Trim(u) <\/span>‘ Just in case
<\/span><\/span>         If<\/span><\/span> Len(tu) > 0 <\/span>Then
<\/span><\/span>            s.Append(<\/span>“ALTER USER [“<\/span><\/span> & tu & <\/span>“] WITH LOGIN = “<\/span><\/span>)
            s.Append(<\/span>“[“<\/span><\/span> & tu & <\/span>“] ;”<\/span><\/span>)
<\/span>         End <\/span><\/span>If
<\/span><\/span>      Next
<\/span><\/span>   End <\/span><\/span>If
<\/span><\/span>   If<\/span><\/span> s.Length = 0 <\/span>Then
<\/span><\/span>      s.Append(<\/span>“SELECT 1 AS Dummy;”<\/span><\/span>)
<\/span>   End <\/span><\/span>If
<\/span><\/span>   Return<\/span><\/span> 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
<\/span><\/span><\/span>   ‘ successfully copied to the target server and restored there.
<\/span><\/span><\/span>End <\/span><\/span>Function<\/p>\n

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

… that’s all she wrote. <\/p>\n

Have fun!  I’ve got to go back to a huge amount of learning of my own, right now…<\/p>\n","protected":false},"excerpt":{"rendered":"

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,Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-43","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/43","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=43"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/43\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=43"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=43"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=43"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}