Bamboo throws SQL exception "Cannot find the object because it does not exist or you do not have permissions" during startup
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article covers a scenario where Bamboo server was not able to start and complained about missing objects or missing permissions on the Database objects.
Environment
The issue and solution was tested on Bamboo 9.2.7 and Microsoft SQL Server 2019.
Diagnosis
Looking at <bamboo-home>logs>atlassian-bamboo.log file we can see the below error.
2024-02-06 22:43:42,474 ERROR [active-objects-init-compatibility-tenant-0] [DatabaseProvider] Exception executing SQL update <ALTER TABLE AO_F36021_SONAR_SERVER_ENTITY ALTER COLUMN ADD_SONAR_PROJECT_LINK BIT>
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object "AO_F36021_SONAR_SERVER_ENTITY" because it does not exist or you do not have permissions.The error is not just limited to AO_F36021_SONAR_SERVER_ENTITY table but there are multiple tables starting with AO_ having this problem.
If we look at <bamboo-install>logs>catalina.out file, we can see Bamboo is not coming up because of error related to AO_4789DD_TASK_MONITOR table.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object "AO_4789DD_TASK_MONITOR" because it does not exist or you do not have permissions.Cause
There can be below possible causes for this problem.
Cause 1 : Missing table in Bamboo database.
Cause 2 : Missing tables for any particular plugin.
Cause 3: Missing correct permissions for the DB user used in bamboo.cfg.xml file.
Solution
Solution 1
This is highly unlikely that few Database table goes missing, but to be 100% sure, you can check your DB and confirm that the tables which are said to be missing in the logs are present. If they are missing you'll need to use DB backup to bring those tables back.
Solution 2
In case any particular plugins are causing issues with AO_ tables, you can look to disable that particular plugin, refer How to start Bamboo with all or few external apps/plugins disabled for more details.
Solution 3
You need to check the Bamboo DB user which is used to connect to DB is having all the correct privileges, you can refer the below 2 parameters in <bamboo-home>bamboo.cfg.xml to get the DB details.
<property name="hibernate.connection.url"></property>
<property name="hibernate.connection.username"></property>Assign the 'db-owner' role on the database for the user that will access the Bamboo database — the 'db_owner' fixed database role allows the user to perform all configuration and maintenance activities on the database. You need to add this role to the Bamboo user used to access your database by updating the login properties for your database user in SQL Server. Read more about login properties for SQL Server.
Screenshot: Adding the 'db_owner' database role to a database user in SQL Server
You can read more about it Connect Bamboo to a Microsoft SQL Server database
You can use the below query to check the permissions state of the user
SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G') AND
-- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')The results should look like below, if you see in the example below Permissiontype should be CONNECT and PermissionsState should be GRANT
