Finding all SQL Server databases for a login

Posted March 5th 2014 by Matt Berther

Earlier today, I had a need to deactivate a SQL Server login. Before I did that, I wanted to find out which databases the user was allowed to access. Rather than opening each of the 35 databases on the SQL Server in SSMS and looking to see whether or not the login was a user in the database, I wanted to create a query that would do this all in one fell swoop for me.

I learned a little more about sp_MSforeachdb, which is a stored procedure that executes the parameter against every database on the SQL Server. There are several documented problems with this stored procedure, but for what I needed to do, it was a great way to get the job done.

The command I used was:

exec sp_MSforeachdb 'if (select count(*) from [?].sys.sysusers where
  name = "usernametosearch") > 0 select "?"'

In the above command, you’ll notice the ‘?’, which is substituted for the database name on every iteration of the loop.

Again, not the right tool for every job… but it served well here.

Pushing large git repos with SSH

Posted December 29th 2013 by Matt Berther

For various reasons, we have a MASSIVE (14gb) git repository that we work with. We have a clone of this repository out in the cloud behind an SSH server. Recently, when I would attempt to push the repository, I would end up with failures while compressing the objects.

$ git push aws master
Counting objects: 4456610, done.
Read from remote host my.gitserver: Connection reset by peer
fatal: The remote end hung up unexpectedly
Compressing objects: 100% (1984267/1984267), done.
fatal: sha1 file '' write error: Invalid argument
error: failed to push some refs to 'git@my.gitserver:repo.git'

On a hunch, I thought that the connection to the SSH server was timing out. I didnt know why the SSH connection would be opened before it was needed. However, the message seemed to indicate that some connection was being dropped by the server. To keep the SSH session alive so that the object compression could complete, I needed to update my ~/.ssh/config file and add an entry for my git remote.

Host my.gitserver
  ServerAliveInterval 60

When no data has been received from the server, the setting specified in ServerAliveInterval will determine the number of seconds after which a null packet will be sent to the server. The default setting is 0 which means that no keep alive packets are sent. This setting can be combined with ServerAliveCountMax which is the maximum number of ServerAlive messages that will be sent without response before the connection is terminated. The default value for ServerAliveCountMax is 3, which is good enough for what I need.

Removing duplicate messages from Outlook

Posted September 20th 2012 by Matt Berther

I recently learned that Outlook for Mac had been uploading multiple copies of the same message to the Exchange server. At final count, I had approximately 280,000 email messages sitting in my “Archive” folder on the server. As you can imagine, this caused tremendous download times for resynchronizing my folders.

I looked for tools that could purge the duplicates for me, but had a tough time getting most of them to work on Microsoft Outlook 2010. I set out to try and solve this problem by creating a simple C# app that would iterate through my archive folder and identify and remove duplicate items.

I chose to parse the messages in two different ways to make sure that I was able to remove as many duplicates as possible. The first scan removed every message that had a duplicate message id. The second scan removed every message that had the same sender email, subject, and sent time.

This technique worked remarkably well. My archive folder now has less than 70,000 messages in it, which means that approximately 75% of the messages in that folder were deleted as duplicates.

I’ve made my source code available at github for anyone that is interested in using and/or forking the project.

Please keep in mind that there are no warranties with the code. It worked well for me; your mileage may vary.

Chrome extension for instapaper

Posted September 14th 2012 by Matt Berther

I use instapaper.com as my read later service. I have installed the Chrome add-on to allow me to quickly tag an article to read later. Also, I have configured it as my read later service in Tweetbot, which allows me to quickly send articles to it for later reading.

The one thing that has always bugged me about the instapaper website is that it does not open links in a new tab/window. To get around this, I set out to create a Chrome extension. This is what I came up with.

// ==UserScript==
// @name        Instapaper New Windows
// @namespace   http://mattberther.com
// @description Open Instapaper links in a new window
// @include     http://www.instapaper.com/*
// @require     http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js
// ==/UserScript==

(function() {
    function loadJQuery(callback) {
        var script = document.createElement("script");
        script.setAttribute("src", "http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js");
        script.addEventListener('load', function() {
                var script = document.createElement("script");
                script.textContent = "(" + callback.toString() + ")();";
                document.body.appendChild(script);
            }, false);
        document.body.appendChild(script);
    }

    function main() {
        $("a.tableViewCellTitleLink").attr('target', '_blank');
    }

    loadJQuery(main);
})();

Copy the code above and save it to a location on your computer; I called mine instapaper.js. The latest versions of Google Chrome no longer allow you to add extensions from a third party source (like your own computer) by simply clicking on the javascript file. To install the extension, open the extensions window in Chrome and then dragging the file you created onto the window.

Once the extension is activated, any links from your unread list in instapaper.com will open in a new window.

Validating HABTM relationships with Rails 3.x

Posted September 9th 2012 by Matt Berther

There comes a time as you build up a rails application that you end up using the has_and_belongs_to_many (HABTM) macro. This macro is an easy way to create a many-to-many relationship between two of your ActiveRecord models.

In some cases you may want to validate that association. However, the traditional methods for validating rails models do not work.

The unit tests below described how I wanted the relationship to function.

class ProjectTest < ActiveSupport::TestCase
  setup do
    @project = Project.new()
  end

  test "may have many developers" do
    4.times { @project.developers << FactoryGirl.create(:developer) }
    assert @project.save
  end

  test "must have at least one developer" do
    @project.save

    assert_equal 1, @project.errors.count
    assert_not_nil @project.errors[:developers]
  end
end

In my case, I was hoping to validate that each project had at least one developer associated to it. Initially, I coded my models to make the first test pass.

class Developer < ActiveRecord::Base
end

class Project < ActiveRecord::Base
  has_and_belongs_to_many :developers
end

To make the second test pass, I tried to implement a custom active record validator.

class Project < ActiveRecord::Base
  has_and_belongs_to_many :developers

  validate :minimum_number_of_developers

private
  def minimum_number_of_developers
    errors.add(:developers, "must have at least on developer") if developers.count < 1
  end
end

This, however, does NOT work with HABTM relationships. The way that these relationships work is that the associated property is not available until after the record is saved.

To get around this, we can validate as part of the after_save callback. Validating here and returning false from the callback will rollback the entire transaction.

class Project < ActiveRecord::Base
  has_and_belongs_to_many :developers

  after_save :validate_minimum_number_of_developers

private
  def validate_minimum_number_of_developers
    if developers.count < 1
      errors.add(:developers, "must have at least on developer")
      return false
    end
  end
end

The test passes with the code above.