This issue can also manifest as an SQL error being signaled each midnight, since CyReport Engine is set to run this stored procedure each midnight.
To resolve, permission needs to be granted to the user that CyReport Engine uses to access the database. In a non-Azure non-legacy installation, this is often the user that installed the database, which will have the required access already, hence this issue will mostly be seen in either Azure or legacy database systems.
On Azure, the user ‘CyTrackUser’ will need to be part of the sp_owner role on the database level. This can be checked by running:
| Code Block |
|---|
select
UserName.name
from
sys.database_role_members AS DRM1
inner join sys.database_principals AS DP1 on DRM1.role_principal_id = DP1.principal_id
inner join sys.database_principals as UserName on UserName.principal_id = DRM1.member_principal_id
WHERE
DP1.name = 'db_owner'
and UserName.name = 'CyTrackUser' |
If this gives no results, the CyTrackUser user is not a member of the db_owner role.
In that case, run
| Code Block |
|---|
EXEC sp_addrolemember 'db_owner', CyTrackUser |
To add them.
On a non Azure SQL Server, the CyTrackUser will need to be a member of the server level sysadmin role. To check if this is the case, run
| Code Block |
|---|
SELECT roles.principal_id AS RolePrincipalID
, roles.name AS RolePrincipalName
, server_role_members.member_principal_id AS MemberPrincipalID
, members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS members
ON server_role_members.member_principal_id = members.principal_id |
And add the user with
| Code Block |
|---|
ALTER SERVER ROLE sysadmin ADD MEMBER CyTrackUser |