Krichie – That SharePoint Guy

In my previous post Wherefore Art Thou Author? I discussed a problem whereby removing a user from a site collection will cause a “User not found exception” when trying to access the Author property of any web they may have created.

In this post, I’ll provide a supportable solution to resolving this without having to revert to modifying the SharePoint content databases directly.

The following code can be used to reset the author of a SharePoint site:

// BEGIN SPResetAuthor

using System;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace SPResetAuthor
{
  class Program
  {
    static void Main(string[] args)
    {
      if (args.Length < 2)
      {
        string sFormat = "{0,5}{1,-20}{2}\n";
        StringBuilder sb = new StringBuilder();
        sb.Append("\nSYNTAX: SPResetAuthor.exe   \r\n");
        sb.Append("\nwhere:\n\n");
        sb.AppendFormat(sFormat, " ", "", "The Web URL where"
            + " the author needs to be reset");
        sb.AppendFormat(sFormat, " ", "", "The Login Account"
            + " for the new author, i.e. DOMAIN\\USER");
        Console.WriteLine(sb.ToString());
        return;
      }

      // Open the site collection
      Console.WriteLine("Opening site collection for: {0}",args[0]);
      SPSite site = new SPSite(args[0]);

      Console.WriteLine("Opening site (web)...");
      // Open the web via the URL passed in
      SPWeb web = site.OpenWeb();
      Console.WriteLine("Site (web) title: " + web.Title);

      Console.WriteLine("Resetting Author to: {0}",args[1]);
      SPUser user = web.EnsureUser(args[1]);
      web.Author = user;
      web.Update();

      Console.WriteLine("Author for site {0}, reset to {1}", args[0], args[1]);
      Console.WriteLine("Author ID: " + web.Author.ID.ToString());
      Console.WriteLine("Author Name: " + web.Author.Name);
    }
  }
}

// END SPResetAuthor

To build this, simple execute the following at the same location where you save the source.

%WINDIR%\Microsoft.NET\Framework\v2.0.50727\csc /target:exe /out:SPResetAuthor.exe SPResetAuthor.cs
/reference:"%COMMONPROGRAMFILES%\Microsoft Shared\web server extensions\12\ISAPI\Microsoft.SharePoint.dll"

The syntax is as follows:

SYNTAX: SPResetAuthor.exe  

where:

                 The Web URL where the author needs to be reset
              The Login Account for the new author, i.e. DOMAIN\USER

When executed, the results should resemble the following:

C:\Dev\SPResetAuthor>SPResetAuthor.exe http://krichiedev/deadauthorsite krichie\user1
Opening site collection for: http://krichiedev/deadauthorsite
Opening site (web)...
Site (web) title: DeadAuthorSite
Resetting Author to: krichie\user1
Author for site http://krichiedev/deadauthorsite, reset to krichie\user1
Author ID: 13
Author Name: Test User One

Hope this helps!

– Keith

I’ve been trying to find the definitive Microsoft document/KB on this for a while.  The information is spread out across various Microsoft documentation, and personal blogs, but the personal blogs don’t show any official content to back it up….So, I thought I’d share along with the associated official documents links to back it up.

Depending on the configuration, I’ve seen instances where my apps result in the following error message in the Application Logs of the SharePoint server from which they are run.

SQL database login failed. Additional error information from SQL Server is included below.

Login failed for user DOMAIN\appaccount'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Initial rights necessary for Console Applications accessing the SharePoint Object Model

The SharePoint SDK has the following statement in regards to creating Console Applications:

“Users must be administrators on the computer where the script is executed in order to run a console application in the context of Windows SharePoint Services.“

See “How to: Create a Console Application” from the Windows SharePoint Services 3.0 SDK.

Additional rights necessary for Console Applications accessing the SharePoint Object Model

Along with that, you also need to ensure the account has appropriate rights on the sites you want to access.  The easiest way to accomplish this, is to ensure the account is granted a Full Control policy for all web applications you want to access.  This alleviates the need to individually grant rights to each site.  Also, if you want to access any farm level objects/properties, you also need to ensure the account is in the Farm Administrators Group.  That’s missing from the above link.

What’s also missing, and the most important part, is that the account ALSO has to have read/write privileges on the content databases themselves in SQL.

If your account was running under the Server Farm Account (See  Plan for administrative and service accounts (Office SharePoint Server) http://technet.microsoft.com/en-us/library/cc263445.aspx ), you would not have this issue, as each time a new content database is created, SharePoint automatically grants this account the appropriate permissions on the content database. 

Then why don’t I follow this approach?

Allot of my work requires that my account not only has appropriate SharePoint rights, but also other rights within the organization.  It’s not always right to grant the Server Farm Account additional access to resources, to ensure Least-Privilege administration requirements.

In a situation where you need a completely separate account, you have to take extra steps to ensure your application will work, and then repeat this process when a new content database is added.  This is noted in KB 935751 as follows:

Error message when you try to iterate through Windows SharePoint Services site objects or SharePoint Server 2007 site objects by using the SharePoint object model: “Unhandled Exception: System.IO.FileNotFoundException”

Note: In my particular scenario, I was not getting this exception, but rather a SqlException with the following details:

Exception occured: Cannot open database “WSS_Content” requested by the login.
The login failed.
Login failed for user ‘DOMAIN\cappaccount’.

Test Scenario

To see this in practice, lets start with the following server setup, code, and directions from the original “How to: Create a Console Application” from the Windows SharePoint Services 3.0 SDK.

The test environment consists of the following 2 machines:

CDBPROBDC1

• This machine is configured to be a domain controller for a new forest called cdbprob.dom
• SQL 2005 is configured on this machine as well.
• The following accounts were configured for this setup:
  • CDBPROB\SQLService
    • This account was used as the service account for all SQL Services
  • CDBPROB\SPService
    • This account was used during the SharePoint Configuration Wizard, and thus is the Application Pool identity for the Central Administration Site
  • CDBPROB\SPAppPool1
    • This account was used as the Application Pool identity on the first content web application
  • CDBPROB\SPAppPool2
    • This account was used as the Application Pool identity on the second content web application
  • CDBPROB\CAppAccount
    • This account was used for the context of running the console application, and was added to the CDBPROBMOSS\Administrators local group
  • CDBPROB\SCAdmin1
    • This account was used for the site collection created within the first content web application
    • The Content Database was named WSS_Content_80
  • CDBPROB\SCAdmin2
    • This account was used for the site collection created within the second content web application
    • The Content Database was named WSS_Content_2

CDBPROBMOSS

• This machine is configured to be part of the cdbprob.dom domain, and has MOSS+SP1 integrated installed on it.
• CDPROB\SPService used for the account during the SharePoint Configuration Wizard and thus is the Application Pool identity for the Central Administration Site)
• CDBPROB\CAppAccount is added to the CDBPROBMOSS\Administrators local group

A review of the Logins in SQL will show the following User Mappings:

• CDBPROB\SPService
  • db_owner for WSS_Content_80 and WSS_Content_2 (This is automatically set by SharePoint)
• CDBPROB\SPAppPool1
  • db_owner for WSS_Content_80 (This is automatically set by SharePoint)
  • No Mapping for WSS_Content_2
• CDBPROB\SPAppPool2
  • db_owner for WSS_Content_2 (This is automatically set by SharePoint)
  • No Mapping for WSS_Content_80

The Source for console application is as follows:

// BEGIN EnumSiteCollections
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace EnumSiteCollections
{
  class Program
  {
    static void Main(string[] args)
    {
      foreach (SPWebApplication wa in SPWebService.ContentService.WebApplications)
      {
        foreach (SPSite sc in wa.Sites)
        {
          SPWeb web = null;
          try
          {
            Console.WriteLine("Get sc.Url...");
            string scUrl = sc.Url;
            Console.WriteLine("Do something with site at: {0}", scUrl);
            Console.WriteLine("Opening root web for site collection...");
            web = sc.RootWeb;
            Console.WriteLine("Get web.Title...");
            string webTitle = web.Title;
            Console.WriteLine("Get web.Url...");
            string webUrl = web.Url;
            Console.WriteLine("Web title: {0} url: {1}", webTitle, webUrl);
          }
          catch (Exception e)
          {
            Console.WriteLine("Exception occured: {0}\r\n{1}", e.Message, e.StackTrace);
          }
          finally
          {
            sc.Dispose();
            if (web != null)
              web.Dispose();
          }
        }
      }
    }
  }
}
// END EnumSiteCollections

Test Results

Running the application under the context of the account CDBPROB\SPService results in no errors

This is because this is the Server Farm account, and all appropriate rights are automatically set by SharePoint.

Running the application under the context of the account CDBPROB\CAppAccount results in a System.NullReferenceException. 

The reason being, the WebApplications collection cannot be accessed unless the account is a member of the Farm Administrators group.  Therefore, you need to add the account to the Farm Administrators group as noted in “Additional rights necessary for Console Applications accessing the SharePoint Object Model” above.  (Note: The Server Farm account (CDBPROB\SPService in this example) is automatically added to the farm administrators group when the configuration wizard is run.)

When the account is added to the Farm Adminstrators group, SharePoint adds the login to SQL, and grants it db_owner rights on the Configuration database.

Now running the application under the context of the account CDBPROB\CAppAccount results in the SQLException I note above when trying to access the Title property from the rootweb.

At this point, if you granted db_owner rights for CDBPROB\CAppAccount to the content databases, it’s still not enough as a Microsoft.SharePoint.SPGlobal.HandleUnauthorizedAccessException will be raised, because the account does not have any rights to the sites in the web application.  You have to allow the account sufficient rights on the sites you want to access (thus my recommendation for a Full Control policy)

If you add, for instance, a Full Control policy to the web application for the users, but don’t grant db_owner rights on the content database, you will still recieve the SQLException as noted above.

Proper configuration

In order to properly have Unrestricted access via SharePoint Object Model from Console Applications you have to ensure you do one of the following:

Use the Server Farm Account

Using this account, SharePoint takes care of the details to ensure the account has proper access.

Ensure proper rights are granted for your custom account

If you need a custom account for your applications to run under, then the following four steps must be followed to allow the account appropriate access.

• The account must be part of the local administrators group on the server you are running from
  • Per “How to: Create a Console Application” from the Windows SharePoint Services 3.0 SDK
• The account must be a member of the Farm Administrators Group to access Farm Level objects
  • If you don’t need to access farm level objects, this isn’t necessary
• The account must be granted appropriate rights for the sites you wish to access
  • Granting the account a full control policy on each web application is the easiest if you need to access all data
• The account must be granted read/write permissions on each content database
  • The easiest thing to do, is grant the db_owner role to the account for each content database, just like the Server Farm account is.

If your scenario does not allow you to have your account granted db_owner rights to the content databases, you’ll have to rely on the Server Farm account..but otherwise, this is my recommendation until later debunked, corrected, etc

I’ll be following up to this article, with a script/app that will enumerate all existing SharePoint content databases, and ensure that a specified account has appropriate rights…to eliminate the manual step process.

Hope you find this information useful!

- Keith

It’s funny, but I get asked this question allot, and thought a quick post on the subject explicitly calling it out would be helpful.

For various different reasons, you may have the need to enumerate over every site collection within your SharePoint Farm (Whether it be WSS 3.0 or MOSS 2007)

The easiest way to do this is to simply over each SPWebApplication found in the SPWebService.ContentService.WebApplications collection, and from there enumerate over each SPSite found in the SPWebApplication.Sites collection as follows:

// BEGIN EnumSiteCollections.cs
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace EnumSiteCollections
{
  class Program
  {
    static void Main(string[] args)
    {
      foreach (SPWebApplication wa in SPWebService.ContentService.WebApplications)
      {
        foreach (SPSite sc in wa.Sites)
        {
          try
          {
            Console.WriteLine("Do something with site at: {0}", sc.Url);
          }
          catch (Exception e)
          {
            Console.WriteLine("Exception occured: {0}\r\n{1}", e.Message, e.StackTrace);
          }
          finally
          {
            sc.Dispose();
          }
        }
      }
    }
  }
}
// END EnumSiteCollections.cs

The ContentService property of the SPWebService class allows you to get at the web applications running on the server.  Then from each one, it’s just a simple matter of enumerating the site collections contained in the Sites collection of the SPWebApplication.

This gives you a reference to all the site collections running on the farm (Less the Central Administration Web Application..Only “Content” web applications).

For console applications, ensure the user is a member of the local computers Administrators group.

Also, ensure that the running account has a full control policy for each web app you wish to enumerate.

See: How To: Create a Console Application from the WSS 3.0 SDK for more information.

HTH!

- Keith

I’ve had quite a few people ask me why I haven’t posted any SharePoint related content in a while, or why it’s been very limited.  The truth of the matter is, my primary focus over the past few months has had little to do with the actual SharePoint OM.  My focus has been on DirectoryServices and SQL.  It’s a little saddening, but I hope to venture back into more SharePoint specific areas soon.

- Keith

Just discovered this and thought I would share

Ever site (web) within a site collection has a property called Author which is set to the account that created the site (Wether it be via the SharePoint UI, or the OM).  To the best of my knowledge, this is not exposed in the UI.  So for example, if I’m logged in a “John Doe” to a site, and create a subsite, the Author property of the new subsite will be set to “John Doe”.

The problem, is that if “John Doe” is ever removed from the site collection, accessing this property will result in a User not found exception such as the following:

Unhandled Exception: Microsoft.SharePoint.SPException: User cannot be found.
at Microsoft.SharePoint.SPUserCollection.GetByID(Int32 id)
at Microsoft.SharePoint.SPWeb.get_Author()

This doesn’t present itself as a problem by just using the SharePoint UI, but if you have custom code that inspects this property, be sure to add some additional exception handling.The problem, is that if “John Doe” is ever removed from the site collection, accessing this property will result in an exception.

To see an example of this problem, do the following:

1. Create a new site collection
2. Add a test account with Full Control to the root site (Web) of this site collection
3. Login to that site as the test account, and create a subsite (Subweb in OM talk)
4. Run the following sample code that dumps out the fields of the Author property
5. As a site collection admin, or another account with rights to manage permissions, remove the test account from the site collection via  “People and Groups: All People”
6. Run the following sample code again, and you’ll see it throw the exception when you try to access any of the fields of the property.

Sample Code

using System;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace WhereforeArtThou
{
    class Program
    {
        static void Main(string[] args)
        {
            // Open the site collection
            Console.WriteLine("Opening site");
            SPSite site = new SPSite(args[0]);

            Console.WriteLine("Access properties from the web");
            // Open the web via the URL passed in
            SPWeb web = site.OpenWeb();
            Console.WriteLine("web title: " + web.Title);
            Console.WriteLine("Author ID: " + web.Author.ID.ToString());
            Console.WriteLine("Author Name: " + web.Author.Name);
        }
    }
}

Is it a bug?  No, not really IMHO, just something to watch out for.  Besides, if the user is removed from the site collection, who is the author supposed to be replaced with?  Regardless of who it was replaced with, the property will no longer hold a valid value of the original author of the site.

Hope this helps!

- Keith

My good friend Todd Klindt pointed me to this posting written by Mark Jen which showed some sample code to export site content from a SharePoint Content Database directly.

!!! DISCLAIMER !!!!

Everyone who knows me, knows that I am TOTALLY AGAINST doing direct database calls to the SharePoint databases UNLESS it is for complete disaster recovery, or complete offline reporting.   This is one of the reasons DeliverPoint will NEVER have any direct SharePoint database calls in it.

For additional information, see the following Microsoft articles:

“Support for changes to the databases that are used by Office Server products and by Windows SharePoint Services”

http://support.microsoft.com/kb/841057/en-us

“SharePoint Database Access”

http://msdn.microsoft.com/en-us/library/bb861829.aspx

Do NOT run this code against Live SharePoint databases.

Ok, now with that out the way…The problem with Mark’s original code, is that it was written for V2 (WSS 2.0/SPS2003), not V3 (WSS 3.0/MOSS 2007).  Also, his query is targeted at specific files (such as .doc/.ppt/.etc).

With a couple of slight changes, I updated the query and code to work with a V3 content database, and also just have it export everything.

Since his code was also just a snippit, I’m placing an updated version  with complete source here.

// BEGIN SPDBEX.CS

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace spdbex
{
    class Program
    {
        static void Main(string[] args)
        {
            // replace this string with your 

            // Sharepoint content DB connection string
            string DBConnString =
             "Server=DATABASESERVER;" +
             "Database=CONTENTDBNAME;Trusted_Connection=True;";

            // create a DB connection
            SqlConnection con = new SqlConnection(DBConnString);
            con.Open();

            // the query to grab all the files.
            SqlCommand com = con.CreateCommand();
            com.CommandText = "SELECT ad.SiteId, ad.Id, ad.DirName," +
                " ad.LeafName, ads.Content" +
                " FROM AllDocs ad, AllDocStreams ads" +
                " WHERE ad.SiteId = ads.SiteId" +
                " AND ad.Id = ads.Id" +
                " AND ads.Content IS NOT NULL" +
                " Order by DirName";

            // execute query
            SqlDataReader reader = com.ExecuteReader();

            while (reader.Read())
            {
                // grab the file’s directory and name
                string DirName = (string)reader["DirName"];
                string LeafName = (string)reader["LeafName"];

                // create directory for the file if it doesn’t yet exist
                if (!Directory.Exists(DirName))
                {
                    Directory.CreateDirectory(DirName);
                    Console.WriteLine("Creating directory: " + DirName);
                }

                // create a filestream to spit out the file
                FileStream fs = new FileStream(DirName + "/" + LeafName,
                    FileMode.Create, FileAccess.Write);
                BinaryWriter writer = new BinaryWriter(fs);

                // depending on the speed of your network,
                // you may want to change the buffer size (it’s in bytes)
                int bufferSize = 1000000;
                long startIndex = 0;
                long retval = 0;
                byte[] outByte = new byte[bufferSize];

                // grab the file out of the db one chunk
                // (of size bufferSize) at a time
                do
                {
                    retval = reader.GetBytes(4, startIndex, outByte, 0,
                        bufferSize);
                    startIndex += bufferSize;

                    writer.Write(outByte, 0, (int)retval);
                    writer.Flush();
                } while (retval == bufferSize);

                // finish writing the file
                writer.Close();
                fs.Close();

                Console.WriteLine("Finished writing file: " + LeafName);
            }

            // close the DB connection and whatnots
            reader.Close();
            con.Close();
        }
    }
}

// END SPDBEX.CS

Be sure to change the DATABASESERVER and CONTENTDBNAME for the connection string, then simply execute the C# compiler on your server as follows:

%WINDIR%\Microsoft.NET\Framework\v2.0.50727\csc /target:exe /out:spdbex.exe spdbex.cs

The code in Marks’ original form, does not allow you to specify any target directory etc, and just begins exporting in the CURRENT DIRECTORY you’re in for any root site, so be sure to run this from a directory/folder you create to hold the data from the root.

I’ll ping Mark and see if he’ll allow me to take that code and polish it up with other options (Such as site selection, specific folder targeting etc) if he doesn’t want to update it.

HTH

- Keith