Inside SharepointDecentralizing Site Administration
Pav Cherny
Download the code for this article: SharePoint2008_06.exe (1160KB)
Microsoft Windows SharePoint Services (WSS) 3.0 and Microsoft Office SharePoint Server (MOSS) 2007 support a flexible administration and security model that enables IT organizations to centralize control over infrastructure components, such as Web servers and database servers, and decentralize control over hosted
site collections and sites. Centralizing the SharePoint® infrastructure helps to reduce maintenance overhead, consolidate business logic, provide a single point of access to SharePoint resources, and avoid unnecessary investments in technology.
At the same time, decentralizing site administration lets you ensure that individual departments retain autonomous control over their departmental SharePoint site collections, sites, document libraries, lists, workflow solutions, and other SharePoint resources. However, a side effect of delegating site administration to individual departments is increased maintenance complexity for the IT department. For example, how do you keep track of resource usage, expired SharePoint sites, and outdated content in a centralized infrastructure if you don't have access to the site collections and sites within your environment?
In this article, I discuss the security model of WSS 3.0, including its advantages and implications when delegating administrative control over SharePoint sites to individual departments while maintaining centralized administrative control over the SharePoint infrastructure. I show you how delegated site administration works, how access permissions and security trimming at the site, list, and item level help maintain a secure and user-friendly work environment, and how Web farm administrators can gain access to the necessary site statistics without elevated SharePoint permissions.
If you want to follow my explanations in a test lab, download the companion material for this column at technetmagazine.com. The material includes detailed deployment instructions for a Windows Server® 2008-based Web farm that reflects a production environment for a company with multiple departments reasonably well.
SharePoint Infrastructure and Site Collection Administration
You can build a SharePoint infrastructure based on either a single-server deployment or a Web farm deployment. In fact, companies often have both. The single-server deployment is suitable for individual departments that need a quick and straightforward collaboration solution. Performance and reliability requirements are minimal. Users tend to be understanding if a departmental SharePoint server, often deployed on workstation hardware, is unavailable because system maintenance is the department's own responsibility and the users within that department are, after all, not IT administrators.
The picture changes entirely for server resources maintained by the IT department. Performance and reliability become critical factors, often requiring the deployment of high-availability solutions to ensure service level agreement (SLA) conformity. Load-balanced Web farm deployments, SQL Server® failover clusters, and databases stored on Storage Area Networks (SAN) can help to meet the performance and high-availability needs of your company. See Figure 1 for an illustration of this architecture.
Figure 1** A SharePoint infrastructure with SQL Server failover cluster and SAN **(Click the image for a larger view)
As you can see in Figure 1, several types of administrators must work together to maintain the resources in a centralized SharePoint infrastructure, yet each of these administrators sees the environment from a different perspective. For example, the servers in the infrastructure are transparent to site collection administrators. Site collection administrators work with the standard SharePoint user interface. This interface does not change whether you host the sites on a workstation computer running WSS 3.0 in your office or on a Web farm in a data center.
The SharePoint infrastructure is also transparent to database administrators and SAN storage engineers. For these administrators, SharePoint databases are no different from any other SQL Server databases, and the logical unit numbers (LUNs) for the SQL Server failover cluster are no different from any other LUNs. The administrator who is most directly concerned with the SharePoint infrastructure is the Web farm administrator, yet this person has no control over the site collections, databases, or LUNs.
It is fair to say that departmental site collection administrators are generally unaware of infrastructure details. After all, it is not necessary to know these details to provision sites and delegate access permissions to teams and groups within a department. It's easy to create a new site and start collaborating, and issues, such as the high storage costs associated with SAN technology, seldom come to mind.
With every project and initiative, new team sites appear, expired sites linger, outdated content is not deleted, and the SharePoint environment mushrooms at an impressive pace until it reaches a scale that system admins can no longer ignore. For example, the internal SharePoint environment at Microsoft once included more than 180,000 sites before Microsoft IT started a simplification initiative in 2006.
Educating site collection administrators can help keep matters under control in a centralized SharePoint infrastructure. Applying site collection quotas and locks is also useful, yet these methods are not very effective when dealing with outdated team sites and content that wastes precious storage and backup resources.
Outdated content does not necessarily push a site collection over its quota. Of course, you can configure SharePoint to send e-mail notifications to site collection owners and automatically delete unused site collections after a specified period by using the Site Use Confirmation and Deletion feature in the SharePoint 3.0 Central Administration site, but this feature does not work at the individual site level. A site collection can contain current and obsolete sites, but the Site Use Confirmation and Deletion feature doesn't work at this level of granularity.
Later I'll show you how to identify unused sites and outdated content by checking the last modification date of documents and list items, even without access permissions to the site collection. This reporting feature is not available out of the box, but fortunately it is not difficult to implement.
Decentralized SharePoint Site Administration
Delegating SharePoint site administration privileges is very straightforward. By using the SharePoint 3.0 Central Administration site, the command-line tool Stsadm.exe, or a custom provisioning tool, you can create Web applications, managed paths, and site collections in the infrastructure. As a Web farm administrator, you can specify primary and secondary site collection administrators when creating a site collection. These site collection administrators can be regular domain users. They do not require administrative permissions on the Web farm or on other infrastructure servers. They use their Web browsers to perform site administration tasks directly in the SharePoint user interface.
Figure 2 illustrates delegated site administration according to the step-by-step instructions you'll find in the worksheets in the companion material. In this example, the Web farm administrator provisioned separate site collections for the HR department (https://sharepoint/hr) and the IT department (https://sharepoint/it).
Figure 2** Delegated SharePoint site administration **(Click the image for a larger view)
The HR and IT administrators can then use the Sites and Workspaces feature available under the Site Actions menu in the SharePoint user interface to create sub-level sites and can either apply permissions from the parent site or define new administrative and user permissions for the sub-level sites. Note that unless explicitly configured otherwise, the Web farm administrator can't access any top-level or sub-level sites, the HR administrator can't access the IT sites, and the IT administrator can't access the HR sites, although all sites are hosted in the same SharePoint environment.
The design of the site collections and site hierarchies depends on organizational requirements. In the Figure 2 example, I did not create a master site on top of https://sharepoint/hr and https://sharepoint/it. In other words, there is no site collection at the https://sharepoint path. But you can easily create a site collection at this path or use other managed paths, such as https://sharepoint/sites/hr and https://sharepoint/sites/it.
You can also create separate Web applications for HR and IT to establish more intuitive base URLs, such as https://hr and https://it, provision general portal sites for the departments, and then use roll-up Web Parts to list information from sub-level sites in these master sites. Separate site collections facilitate autonomous administration; help limit access to departmental information; provide individual customization capabilities for Master Pages, page layouts, templates, Web Parts, and navigation controls; and can be used to place sensitive data in dedicated content databases, separate from other site collections and content databases.
Roles, Site Permissions, and Security Trimming
Once the site collections exist and administrative permissions have been delegated, a site collection administrator can open a top-level site and use the Site Settings feature available under the Site Actions menu to manage the site collection. This includes adding (or removing) other site collection administrators with full permissions to all sites in the site collection, creating groups, associating these groups with roles, and adding users to these groups to grant read and contributor permissions. It also includes provisioning sub-level group sites and team sites.
Although you can grant SharePoint permissions to users individually, it's better to create SharePoint groups according to specific roles. For example, SharePoint includes three default groups for the roles Visitors, Contributors, and Owners. Visitors have read-only access; Contributors can add items to lists and document libraries and personalize Web pages; and Owners have full control.
One feature worth noting is that SharePoint performs security trimming based on the user role, which means that users only see those links and items in the user interface that they actually have permissions to access. If you disallow a user access to a resource, such as a list (by configuring explicit permissions under List Settings) or a selected list item (by using the Manage Permissions command in the item's context menu), the list or item will disappear for this user. If the user specifies the URL of the item in the browser directly, the user reaches the Error: Access Denied page.
Figure 3 shows security trimming in action. The HR administrator has full permissions and is able to reach the Site Actions menu to perform administrative tasks. The HR Visitor, on the other hand, only has read-only permissions to the site collection and does not see this menu.
Figure 3** Security trimming based on user roles in the SharePoint interface **(Click the image for a larger view)
Security trimming is useful as it hides inaccessible features, links, and items, but it also implies that Web farm administrators can't use the SharePoint user interface to list sites and site resources without having access permissions. You will not be able to see these resources in Web pages or Web Parts. Furthermore, inaccessible resources do not appear in search results because SharePoint Enterprise Search performs security trimming at query time based on identity of the user submitting the query.
What about the Command Line?
If the graphical user interface does not supply an avenue to the desired information without elevated permissions at the site collection level, perhaps the command-line tool Stsadm.exe provides the solution. For example, you might think you could use the command
Stsadm -o enumsites -url https://sharepoint
to list all site collections and owners within the https://sharepoint Web app. Then you should be able to list all sub-sites by using this command:
Stsadm -o enumsubwebs -url <site collection>
Yet, in a Web farm environment, Stsadm.exe typically greets this with an error message indicating that database access permissions are missing. Stsadm.exe cannot enumerate the site collections even though you are a Web farm administrator and can manage the site collections in SharePoint 3.0 Central Administration. The following is the error message you receive if you use Stsadm.exe on a Web farm without SQL Server access permissions:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN>stsadm -o enumsites -url https://sharepoint
<Sites Count="2">
<Site Error="Cannot open database "WSS_Content" requested by the login. The login failed.
Login failed for user 'CONTOSO\SPAdmin'.">
<Site Error="Cannot open database "WSS_Content" requested by the login. The login failed.
Login failed for user 'CONTOSO\SPAdmin'." />
The Web farm administrator receives this error message because Stsadm.exe runs in the context of the logged on user, such as CONTOSO\SPAdmin, whereas the SharePoint 3.0 Central Administration site accesses the SharePoint databases in the context of the system account associated with the SharePoint Central Administration v3 application pool, such as CONTOSO\WssConfigAdmin. Although granting your account db_owner permissions for the content database in SQL Server enables you to run the Stsadm -o enumsites -url https://sharepoint command, you are still not able to enumerate the sub-level sites within the site collections. The command Stsadm -o enumsubwebs -url <site collection> returns Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) and you are back at square one. However, there is a better, more successful way that doesn't require elevated SQL Server permissions.
Site Information in Content Databases
If you cannot obtain the required information through the standard SharePoint interfaces and tools, you must go directly to the SharePoint content databases. But first a word of caution—this should be a last resort! The content databases rely on proprietary schemas and database structures that Microsoft only partially documented in the WSS 3.0 SDK in an attempt to discourage customers from working with the databases directly. Whenever possible, you should use the SharePoint user interface, command-line tools, object model, or Web services to access the desired information.
If you have no other alternative and must go directly to the databases, you should at least constrain the solution to read-only access. Modifying the database schema or database structures is not supported and can cause serious problems that might require you to restore site collections from backup or reinstall the SharePoint Web farm.
A possible alternative to implementing a reporting solution that directly accesses the content databases is to specify the Web farm administrator account as a secondary owner in the site collection configuration. Web farm administrators can perform this step in the SharePoint 3.0 Central Administration site. You can find the corresponding Site Collection Administrators link under SharePoint Site Management on the Application Management page.
As a secondary site collection administrator, you can open the site collection in your Web browser, display the Site Settings, and then click on the Site Usage Report link under Site Administration, provided that you have enabled Usage Analysis Processing in SharePoint 3.0 Central Administration. Look under Logging and Reporting on the Operations page if you want to track usage data in log files on your SharePoint servers. The Site Usage Report feature is disabled by default.
However, manually creating site usage reports for individual site collections is not practical in environments with a large number of site collections or with strict requirements that prevent you from granting Web farm administrators the required permissions. A more practical approach is to implement a straightforward ASP.NET reporting application that lists all site collections and sites from the content database together with important statistics, such as the number of items, the combined total size of all items, and the last modification date of the items, as illustrated in Figure 4.
Figure 4** Obtaining site statistics drectly from a content database **(Click the image for a larger view)
If you run this application under the identity of the SharePoint Central Administration v3 application pool account, you can get access to the content databases without the need for elevated SQL Server permissions. As mentioned earlier, the Central Administration account already has the required SQL Server permissions.
The companion material includes a simple reporting solution that uses SELECT queries to the content database on the SQL server, and step-by-step instructions for deploying this solution on a Web server. It is important to note that I did not implement any security checks. The solution runs with anonymous authentication.
It is not necessary to authenticate the user because the Central Administration account provides the required security context to access the content databases. I used a custom port for the solution's Web site, blocked through Windows® Firewall on Windows Server 2008, so only a local user can open the Web application. This fulfilled the security requirements in my test environment, but in a production environment, you should tighten security further, such as by enabling Windows authentication and configuring restrictive access permissions to the virtual directory of the ASP.NET application.
Conclusion
SharePoint provides flexible site collection and site administration capabilities that enable you to centralize the SharePoint infrastructure while decentralizing site administration according to your company's specific needs. A centralized infrastructure can be beneficial to companies of any size because a consolidated environment helps lower costs; facilitates information sharing within and across departments; provides the basis to apply expert knowledge in a targeted way to maintain the environment with high security, reliability, and scalability; and gives users a single point of access to sites and SharePoint resources in the company network.
At the same time, individual departments and business units can remain in control of their SharePoint resources. Web farm administrators can designate site collection administrators who then perform site administration tasks within their areas of responsibility without any further involvement of the IT department.
However, with the shift of control to the departments, Web farm administrators also lose the ability to monitor resource utilization in the infrastructure using the standard SharePoint features and tools. Applying quotas to content databases, sending e-mail reminders to site collection administrators, and tracking usage data for reporting purposes can help to mitigate the issue, but in order to manage the SharePoint infrastructure efficiently, Web farm administrators need further tools that extract the required site metadata directly from the content databases in a read-only fashion.
The options available include using SQL Server 2005 Reporting Services or a similar enterprise-level reporting solution. As the example in the companion material shows, you can also use SQL Server SELECT queries to obtain the required information.
Pav Cherny is an IT expert and author specializing in Microsoft technologies for collaboration and unified communication. His publications include white papers, product manuals, and books with a focus on IT operations and system administration. Pav is President of Biblioso Corporation, a company that specializes in managed documentation and localization services.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.