Insufficient SQL Server database permissions - Event 5214 (SharePoint 2010 Products)

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

Alert Name:   Insufficient SQL Server database permissions

Event ID:   5214

Summary:   Microsoft SharePoint Foundation uses Microsoft SQL Server 2008 databases to store most of the content for the Web site and configuration settings. For example, all pages in the site, files in document libraries, files attached to lists, and information in lists are stored in the content database, and security and permission settings along with other configuration settings are stored in the configuration database in SQL Server.

SharePoint Foundation uses a service account to communicate with the database on behalf of a user request. This service account can be either a specific user name and password (domain name and password) or a predefined system account, such as Local System or Network Service. This error occurs when the service account specified in SharePoint Foundation has not been granted sufficient permissions in the SQL Server database.

Symptoms:   The following symptom might appear:

• This event appears in the event log: Event ID: 5214 Insufficient SQL database permissions for user '<username>' in database <database name> on SQL Server instance <instance name>. Additional error information from SQL Server is included below.

Cause:   One or more of the following might be the cause:

• The service account to which SharePoint Foundation is set does not have sufficient permissions to the database to which it is trying to connect.

• The service account is not set up properly in SharePoint Foundation.

Resolution:   Grant correct permissions to the database access account

• To resolve this issue, assign the database access account and then verify the account has correct permission in SQL Server.

  To assign the database access account:

  1. On the Central Administration Web site, click Security and in the General Security section click Configure Service Accounts.

  2. On the Configure Service Accounts page, in the Credential Management section, select the correct Web application pool for your Web application.

  3. In the Select an account for this component section, select the domain account that you want to associate with this Web application pool, or click Register new managed account to associate a new domain account with this application pool.

     Note

     You can use a local account for the Web application pool only if SharePoint Foundation and SQL Server are running on the same computer.

  4. Click OK to save changes.

  To verify that account has correct permissions in SQL Server:

  1. Connect to the computer that runs SQL Server by using an account with SQL Server administrator permissions.

  2. In SQL Server Management Studio, in the Object Explorer navigation pane, expand the Security node, and then click the Logins node. The name of the database access account indicates that it is a SQL login.

  3. If the account exists, open the database node, open the Security node, and then click Roles.

  4. Expand the Database Roles node, right-click db_owner, and select Properties.

  5. In the Database Roles Properties dialog box, check whether the database access account is in the Members of this role list. If the account is not listed, click Add.

  To verify that any database issues have been resolved:

  1. In the SharePoint Management Shell, run the Windows PowerShell command Get-SPSite | Format-Table -Property ID,WebApplication,ContentDatabase to obtain a list of the sites for each Web application to list all sites in the various databases, and locate one site in each database.

  2. Browse to the site.