Fixing “Insufficient Database Permissions” in SharePoint – Step-by-Step Guide

The “Insufficient Database Permissions” error in SharePoint usually occurs when the SharePoint service account lacks the required permissions to access or modify the SQL Server database. This can lead to issues such as:

• Unable to mount a SharePoint database.
• Errors while upgrading SharePoint databases.
• Failure in backup or restore operations.
• Restricted access to content databases.

This guide provides a detailed step-by-step troubleshooting approach to resolving the issue and ensuring that SharePoint has the proper permissions to function correctly.

---

Common Error Messages:

• “Cannot connect to the database. The database might not exist, or the current user does not have permission to connect.”
• “The database upgrade failed due to insufficient permissions.”
• “Access denied when trying to attach the SharePoint content database.”
• “SharePoint service account lacks necessary permissions on SQL Server.”

---

Step 1: Identify the SharePoint Service Account

Before fixing the issue, you need to determine which service account is being used to connect to SQL Server.

Steps to Find the SharePoint Service Account:

1. Open SharePoint Central Administration.
2. Go to Security → Configure Service Accounts.
3. Identify the account listed under “Farm Account” or “Database Access Account” (e.g., SPFarmAdmin or DOMAIN\SPService).

✔ Once identified, note the account name for later steps.

---

Step 2: Check SQL Server Permissions for the SharePoint Account

Now, verify that the SharePoint service account has the necessary permissions in SQL Server.

How to Check Permissions in SQL Server Management Studio (SSMS):

1. Open SQL Server Management Studio (SSMS) → Connect to the SQL Server instance.
2. Click on Security → Logins.
3. Locate the SharePoint service account (DOMAIN\SPService).
4. Right-click the account → Properties → Select Server Roles.
5. Ensure the following permissions are assigned:
   • ✅ db_owner on all SharePoint databases
   • ✅ SecurityAdmin (for managing security)
   • ✅ dbcreator (for creating databases)
   • ✅ sysadmin (for full SQL access)

✔ If these permissions are missing, proceed to Step 3 to grant them.

---

Step 3: Grant Necessary Permissions in SQL Server

If the SharePoint service account lacks the required permissions, manually assign them using SSMS or T-SQL commands.

Method 1: Assign Permissions Using SSMS

1. In SSMS, go to Security → Logins.
2. Right-click the SharePoint service account → Properties.
3. Click User Mapping → Select the SharePoint databases.
4. Check the following roles:
   • ✅ db_owner
   • ✅ db_securityadmin
   • ✅ db_accessadmin
5. Click OK to apply changes.

✔ If using T-SQL commands, follow Method 2.

---

Method 2: Assign Permissions Using T-SQL Commands

Run the following SQL commands in SSMS to grant the required permissions:

USE master;
CREATE LOGIN [DOMAIN\SPService] FROM WINDOWS;
ALTER SERVER ROLE dbcreator ADD MEMBER [DOMAIN\SPService];
ALTER SERVER ROLE securityadmin ADD MEMBER [DOMAIN\SPService];
ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\SPService];

USE SharePoint_Config;
CREATE USER [DOMAIN\SPService] FOR LOGIN [DOMAIN\SPService];
ALTER ROLE db_owner ADD MEMBER [DOMAIN\SPService];

USE SharePoint_Content;
ALTER ROLE db_owner ADD MEMBER [DOMAIN\SPService];

✔ Once executed, restart SQL Server to apply changes.

---

Step 4: Restart SQL Server and SharePoint Services

After updating permissions, restart services to apply the changes.

Restart SQL Server:

1. Open SQL Server Configuration Manager.
2. Locate SQL Server (MSSQLSERVER).
3. Right-click → Restart.

Restart SharePoint Services:

1. Open PowerShell as Administrator.
2. Run the following command: Restart-Service SPTimerV4 Restart-Service SPSearch4 Restart-Service OSearch15
3. Restart IIS to refresh SharePoint connections: iisreset /restart

✔ Now, test if the error is resolved.

---

Step 5: Verify SharePoint Database Connection

Now that permissions have been updated, verify that SharePoint can connect to SQL Server.

Check Database Status in SharePoint Central Admin:

1. Open SharePoint Central Administration.
2. Go to Manage Content Databases.
3. Ensure that all databases show “Ready” status.

Check SQL Connection Using PowerShell:

Run the following PowerShell command to test the database connection:

Test-SPContentDatabase -Name SharePoint_Content -WebApplication http://YourSharePointSite

✔ If the test passes, SharePoint now has the correct database permissions.

---

Step 6: Monitor for Future Permission Issues

To prevent permission errors in the future, follow these best practices:

✔ Ensure SharePoint Service Accounts Are Never Removed from SQL Server.
✔ Use SQL Server Auditing to Track Permission Changes:

SELECT * FROM sys.server_permissions WHERE grantee_principal_id = SUSER_ID('DOMAIN\SPService');

✔ Regularly Review Database Permissions in SharePoint Central Admin.
✔ Document All Account Changes for Future Reference.

---