Joseph Michael Pesch
VP Programming

Change Owner of SSRS Subscription on SQL Server 2008 R2

by 9. April 2012 10:26

Recently a DBA left the company and before his final day we disabled his login account to make sure that nothing broke (i.e. no jobs were running under his account, etc.).  The next couple days everything seemed fine.  Then after he left we actually deleted his account.  After deleting his account every SSRS report subscription that was originally created by him stopped working.  Some of them gave error messages that indicated some type of problem with the user account while others had very vague error messages.  The bottom line is that they ran fine when the AD account existed (regarless of the state of the account); but, the fail once the account is removed.  The initial fix was simply to create a new version of the subscription under a different user account; however, there were so many that this would be a very tedious and time consuming process. 

As an alternative the attached script can be used to change the subscription owner of all subscriptions belonging to a specific user. To execute the Script, please follow these steps:

1. Open Command line and Change Directory to this location “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn”
2. Execute the following command:

  • rs -i <Path to ChangeSubscriptionOwner.rss Script> -s <ReportServer URL> -e Mgmt2010 -v varOwner="<CurrentSubscriptionOwner>" -v varNewOwner="<NewSubscriptionOwner>"

Example:

  • rs -i D:\ChangeSubscriptionOwner.rss -s http://myserver/reportserver -e Mgmt2010 -v varOwner="mydomain\origuserid" -v varNewOwner="mydomain\newuserid"

This will change all the Subscriptions owned by "mydomain\originaluserid" to "mydomain\newuserid".

Note: Please Execute this script on the Reporting Services Server.

Note 2: This script and should be used only when Good ReportServer and ReportServerTempDB backups are available. As a good practice, you should also take a backup of the Reporting Services EncryptionKey.

Tags:

SQL Server | SSRS

C# LDAP Query - Get User Properties by User Name

by 5. April 2012 08:44

This code allows you to search for an Active Directory user by name and return a dynamic list of properties with optional line feed.  NOTE: There is a hardcoded userid and password that needs to be changed to a valid AD user login.

using System;
using System.DirectoryServices;
using System.Text;
using System.Text.RegularExpressions;

namespace LDAP_Query
{
  class Program
  {
    static void Main(string[] args)
    {
      Console.Write(GetLDAPUserByName("Joseph Pesch"
        , new string[] { "department", "title", "givenName", "displayName" }
        , ((char)10).ToString()));
    }

    private static string GetLDAPUserByName(string userName, string[] properties, string lineFeed)
    {
      StringBuilder sb = new StringBuilder();
      userName = Regex.Replace(userName, @"[\(\)\*\\]", (match) =>
      {
        // escape reserved chars 
        return "\\" + ((int)match.Value[0]).ToString("x");
      }, RegexOptions.Compiled);
      string query = "(&"
        + "(objectCategory=person)" + "(objectClass=user)"
        + "(|" + "(&" + "(sn={0}*)" + "(givenName={1}*)" + ")"
        + "(displayName={2})" + ")" + ")";
      query = String.Format(query, userName.Split(' ')[1]
        , userName.Split(' ')[0], userName);
      using (DirectoryEntry entry =
        new DirectoryEntry("LDAP://" + Environment.UserDomainName
        , "userid", "password", AuthenticationTypes.Secure))
      {
        using (DirectorySearcher ds =
          new DirectorySearcher(entry, query, null, SearchScope.Subtree))
        {
          SearchResultCollection res = ds.FindAll(); // all matches 
          if (res == null) return "CANNOT LOCATE USER";
          foreach (SearchResult r in res)
          {
            foreach (string prop in properties)
            {
              foreach (object property in r.Properties[prop])
              {
                sb.Append(prop).Append("=").Append(property.ToString()).Append(lineFeed);
              }
            }
          }
        }
      }
      return sb.ToString();
    }

  }

}

Tags:

C#