Batch permission change for MSSQL stored procs

I recently ran into a batch of existing stored procs that needed additional execute permissions. After some digging on the web I was able to put together a simple script to make the changes. Since I'll likely need it again in the future, I decided to throw it out here.

Assuming all of the stored procs contain the string 'MyApp' in the name, and all need execute permissions added for 'myuser', the following will make the necessary change

  declare @SQL varchar(8000)

  select @SQL = isnull(@SQL, '') + 

    'grant execute on [' + 

    routine_schema + '].[' + 

    routine_name + '] to [myuser];'

  from information_schema.routines 

  where SPECIFIC_NAME like '%MyApp%' 

  exec (@SQL )

Refactor - Moving duplicate code to a separate method

I've mentioned in previous posts that it pains me to see developers use copy/paste to write code. It's more error prone, as you have to make sure all necessary changes are made to the copy. It's also harder to maintain since finding a bug in one copy of the code means making changes to the others - assuming you can find those copies. Depending on the circumstances, it can also be harder to read the code. As I keep running into less-than-ideal code, I decided a few posts were in order.

Today, let's look at a method that builds an XmlDocument based on values from a database. Here, the info is passed in a DataRow object.

public XmlDocument GenerateMailingLabel(DataRow subscriber)

{

  XmlDocument doc = new XmlDocument();

  doc.LoadXml("<MailingLabel><SendTo /></MailingLabel>");

  XmlNode sendToNode = doc.SelectSingleNode("//SendTo");

  XmlAttribute attribute = doc.CreateAttribute("FirstName");

  attribute.Value = subscriber["FirstName"].ToString();

  sendToNode.Attributes.Append(attribute);

  attribute = doc.CreateAttribute("LastName");

  attribute.Value = subscriber["LastName"].ToString();

  sendToNode.Attributes.Append(attribute);

  attribute = doc.CreateAttribute("StreetAddress");

  attribute.Value = subscriber["StreetAddress"].ToString();

  sendToNode.Attributes.Append(attribute);

  attribute = doc.CreateAttribute("City");

  attribute.Value = subscriber["City"].ToString();

  sendToNode.Attributes.Append(attribute);

  attribute = doc.CreateAttribute("State");

  attribute.Value = subscriber["State"].ToString();

  sendToNode.Attributes.Append(attribute);

  attribute = doc.CreateAttribute("Zip");

  attribute.Value = subscriber["Zip"].ToString();

  sendToNode.Attributes.Append(attribute);

  return doc;

}

There's nothing terribly complex going on here. It starts by creating a new, nearly empty XmlDocument. It then pulls necessary values out of the DataRow and adds them as attributes to the <SendTo> node. Modifying existing attributes appears to be easy, as each one is isolated to a single block of three lines. Adding new attributes is similarly straightforward. Copy an existing block, paste it just before the 'return', and then modify the two string literals. If it's so easy to work with, why change the code?

First, you have to make sure you modify both string literals correctly. Fail to modify one or both of them and you have a bug that may not be caught any time soon.

Second, these types of methods tend to become long. It may be easy to read currently, but what happens when you have twenty attributes to add to the document? Picking out a particular block to modify becomes a bit more challenging. This is especially true if you're currently lacking in sleep and/or caffein.

Third, a structural change to the xml document becomes a slow, tedious task. Maybe halfway through the project someone decides you need to create sub-elements instead of attributes for each value. In the above code, you have six sets of changes to make.

A better solution would be to pull the block of three lines into a separate method:

private void CreateAttribute(string attributeName, string columnName,

  XmlDocument doc, XmlNode sendToNode, DataRow subscriber)

{

  XmlAttribute attribute = doc.CreateAttribute(attributeName);

  attribute.Value = subscriber[columnName].ToString();

  sendToNode.Attributes.Append(attribute);

}

Once we've done this, each block of code in the GenerateMailingLabel method can be reduced to a single line:

public XmlDocument GenerateMailingLabel(DataRow subscriber)

{

  XmlDocument doc = new XmlDocument();

  doc.LoadXml("<MailingLabel><SendTo /></MailingLabel>");

  XmlNode sendToNode = doc.SelectSingleNode("//SendTo");

  CreateAttribute("FirstName", "FirstName", doc,

    sendToNode, subscriber);

  CreateAttribute("LastName", "LastName", doc,

    sendToNode, subscriber);

  CreateAttribute("StreetAddress", "StreetAddress", doc,

    sendToNode, subscriber);

  CreateAttribute("City", "City", doc,

    sendToNode, subscriber);

  CreateAttribute("State", "State", doc,

    sendToNode, subscriber);

  CreateAttribute("Zip", "Zip", doc,

    sendToNode, subscriber);

  return doc;

}

Now adding a new attribute requires one additional line instead of three. Even better, bug fixes or structural changes will be limited to a single section of code. In either case, changes can be made faster and with less risk of introducing bugs.

Verify user membership in an AD group

Occasionally, when working on a WinForms application, I need to validate the current user against an Active Directory group before letting the application run. Though not a complex task, it usually takes me a bit of time to research (Translation: I have to copy code from several web sites and modify to suit my needs.) So I figured I'd post my own implementation here for others to use in their research. That, and maybe save myself a bit of time in the future.

The first step is to get the list of AD groups the current user belongs to. The magic necessary to make this happen is contained in the System.DirectoryServices namespace. The code to return the list looks like so:

internal static List<string> GetADGroupsForCurrentUser()
{
    List<string> groups = new List<string>();
    string domainName = Environment.UserDomainName;
    DirectoryEntry entry = new DirectoryEntry("LDAP://"
          + domainName);
    string filter = "(samAccountName="
          + Environment.UserName + ")";

    DirectorySearcher search = 
          new DirectorySearcher(entry, filter);
    search.PropertiesToLoad.Add("memberOf");

    SearchResult results = search.FindOne();
    int groupsCount = results.Properties.Count;
    for (int i = 0; i < groupsCount; i++)
    {
        string groupString = 
            results.Properties["memberOf"][i].ToString();
        groups.Add(groupString);
    }

    return groups;
}

Once we have the list, we need to loop through it looking for the desired AD group. The strings returned from the above code contain comma-delimited properties (something like "CN=MyDomainGroup,OU=Security Groups,DC=local".) Because of this I'm using the Contains method to search the string for the desired domain name. I'm sure there's another way to do this, but whatever works...

public static bool CurrentUserIsAuthorized(string authorizedGroupName)
{
    List<string> activeDirectoryGroups =
          GetADGroupsForCurrentUser();
    foreach (string group in activeDirectoryGroups)
    {
        if (group.Contains(authorizedGroupName))
            return true;
    }

    return false;
}

Intellisense for WCF config files

I found it slightly irritating that there is no intellisense for WCF config files built into VS2005. Fortunately a quick Google search turned up a blog post with a solution. I figured I'd post it here so I can find it later. That, plus it allows me to meet my self-imposed quota of at least one blog entry per month :)

The solution can be found here.

Don't make me GAC

We discovered an interesting "feature" in VisualStudio 2005 a couple days ago. Let's say I have a solution containing a Utilities project and a web site referencing that project. The Utilities assembly references a third-party dll (log4net in this case.) When the web site is compiled, it automatically pulls any dependent files into its 'bin' folder. Note the presence of log4net.dll


This seems fairly straightforward. But let's say I then load the solution on another computer (a build server perhaps.) The code compiles as before, but this time it doesn't pull in log4net.dll


At this point, I could manually copy the dll into the folder, right? That was my first thought, which of course proved to be wrong. The reason is that we also have an install project for the site. The installer is set to use all output files from the web project. If we add files to the site, the installer will automatically include them in the next compile. Unfortunately, when I manually add the dll to the bin folder, the installer overlooks it.

My next thought was to directly reference the log4net.dll in the web project.



Which still compiles on my box. And still ignores the dll on the build server. At this point, we opened up the references dialog on the build box. Much to our surprise, the dll is being referenced in the GAC


It seems that when a web project loads, it first looks in the GAC for referenced assemblies. If it finds them there, it ignores the files originally pointed to. Because of this, VisStudio doesn't copy the files to the bin folder. Which of course means the installer won't contain the dll.

Ideally, the dll shouldn't have been GAC'd on the build server. Since removing it from the GAC would cause us other issues, our workaround was to hard-code log4net.dll in the install project.

Note: The web site was built using the out-of-the-box project template, which doesn't create an actual .proj file. Microsoft later released a Web Application Project (with VS2005 SP1) that does create a .proj. It's possible that this would force the dll to load from the file reference, but I've not tested that theory.

Wrapping a class in a singleton

My current task at work has me building a new application, using an existing framework as the base. One of the classes in this framework wraps access to the config file (which is actually kept in the database.) As soon as this class is instantiated, it retrieves the config info from the database. I wanted a way to keep a single instance around for the lifetime of the application. Otherwise, I'd be hitting the database multiple times to pull down the same information.

One option would have been to create an instance in the app's constructor. The downside to that approach is that I'd have to pass it into every method that might need it. Instead I chose to use the singleton pattern.

Below is a quick example. Marking the MyConfig class as static means it can't be instantiated. The static constructor will be called once and only once - the first time the class is accessed. This constructor creates an instance of the CommonConfig class, which can be accessed through the Config property. Now, regardless of how many times my code accesses MyConfig.Config, the database is only hit once.

    public static class MyConfig

    {

        static readonly CommonConfig _commonConfig;

        static MyConfig()

        {

            _commonConfig = new CommonConfig();

        }

        public static CommonConfig Config

        {

            get

            {

                return _commonConfig;

            }

        }

    }

Add scroll wheel support to VB6

Yes, you read that right. The client I'm currently assigned to has a significant amount of legacy code in VB6. Not an ideal situation, but at least it isn't COBOL.

Anyway, one of the first things I noticed after loading a project was that I couldn't scroll through the code using the mouse wheel. It turns out the VB6 IDE didn't include wheel support. Fortunately, there's an add-in from Microsoft that fixes this. See KB article 837910 for the download and install instructions.

And yes, I'm aware that this probably doesn't pertain to you (if it does, I feel your pain.) The post is mainly a bookmark for my own reference. Any sympathy it generates is just a bonus :)