My account Book a demo

Upgrading repository

Piotr Tokarski - Dataedo Team Piotr Tokarski
Applies to: Dataedo 23.x (current) versions, Article available also for: 10.x

Things to keep in mind when upgrading

For Dataedo to work, all components must be in the same major version. So the Desktop in version 10.2 will work with Web Catalog 10.2.2 and Repository in 10.2 but will not connect to a repository in version 10.3.

We recommend creating a backup of the repository database before upgrading in case of any issues - these most commonly occur if a network connection is lost during the upgrade.

You can also create a copy of the folder Dataedo Web is running from. If Dataedo Web is running from a virtual machine, it may be easier to create a snapshot of it before the upgrade process.

Dataedo Web- Folder copy

Upgrading Dataedo Web

To upgrade Dataedo Web, connect to the server running Dataedo Web and run the installer.

Dataedo Web installer- Run as administrator

When asked for a location/instance, choose the same option you've selected during installation. The default instance is C:\Dataedo Web.

Dataedo Web- Choose location instance

If unsure where your Dataedo Web is running from, open the IIS Manager, navigate to and select the Dataedo site, then click "Explore" in the top right corner to open the folder in File Explorer.

Dataedo Web - IIS Manager

If you've correctly chosen the instance, the installer will show an option to update Dataedo Web.

Dataedo Web- Run upgrade

If you don't see this option, restart the installer and choose the path correctly. Do not choose the "Install" option! Selecting this option will create another instance of Dataedo, which may cause issues later.

After installation, Dataedo Web will warn about the repository being in an older version. The "Upgrading repository" step below will resolve this.

Upgrading the Desktop application

To upgrade Dataedo Desktop, install the new version on each user's PC.

Dataedo Desktop- New version message

Dataedo Desktop- Download desktop installer

Upgrading Repository

To upgrade your repository, you'll need a user with db_owner role in the database and the default schema set to 'dbo'. We recommend you backup the database before continuing.

Dataedo Desktop- Upgrade repository message

Upgrading the repository from the app

The easiest way to upgrade your repository is through the Dataedo Desktop app. After connecting from Dataedo Desktop, you will be shown a warning cautioning about possible issues with Dataedo caused by upgrading - you can safely ignore these if you've already installed Dataedo Web in the current version.

When asked, choose the "Detach Web Catalog" option twice.

Dataedo- Detach Web Catalog pop up message

Dataedo- Detach Web Catalog confirmation

After the upgrade process finishes, both Dataedo Web and Desktop should be able to connect to the repository.

Upgrading the repository manually

To upgrade the repository manually, first, find out the previous version of Dataedo you used until now. Then, install the new version of Dataedo, and find the Scripts folder in the installation directory (by default, it's C:\Program Files (x86)\Dataedo\Scripts).

To upgrade the repository, you'll need to connect to the repository using SSMS or an equivalent tool, then run all scripts from folders with names higher than your previous version.

For example, to upgrade from 10.2 to 10.3.2, you'll need to run the scripts in folders 10_3 and 10_3_2. First, open the 10.3 folder, and run scripts one by one, ordered by name: Dataedo 10.3 upgrade scripts

Then, go to the 10.3.2 folder, and do the same: Dataedo 10.3.2 upgrade scripts

Some scripts might show warnings - you can safely ignore these. However, if you see an error during any operation, we recommend restoring the repository from the backup and contacting support@dataedo.com to retry the upgrade.

Permissions in Dataedo Web (when upgrading from a version older than 10.2)

From version 10.2, Dataedo includes a feature to control each user's access with roles and permissions within the app. To manage this, open Dataedo Web and click the gear icon. Select "User Management":

Dataedo Web- New Users settings

Some users reported that they couldn't access the tab after upgrading from Dataedo < 10.2. You can resolve this by granting the permissions directly from the repository database. To do this, connect to the repository database, and paste the script from the bottom of the article into a new query window.

Dataedo- Grant the access to Users tab query

Change the "YOUR_LOGIN_HERE" value to the login you use to access Dataedo Web - if you're using AD accounts, include the domain name. Run the script, then refresh the page in Dataedo Web.

Dataedo- Changing the login and executing query

You should now be able to access the "Users" tab.

Grant Admin role script (for Dataedo 23.2 and newer)

--Script for creating a new group named Admins, granting it user management privilege
--  and adding YOUR_LOGIN_HERE to the group
--Before running, change YOUR_LOGIN_HERE to your login below
DECLARE @custom_login NVARCHAR(1024);
SET @custom_login = 'YOUR_LOGIN_HERE';
INSERT INTO [dbo].[user_groups] (
    [name]
    ,[default]
    ,creation_date
    ,last_modification_date )
SELECT 'Admins'
    ,0
    ,getdate()
    ,getdate()
WHERE NOT EXISTS (
        SELECT 1
        FROM [dbo].[user_groups]
        WHERE [name] = 'Admins');

INSERT INTO [dbo].[permissions] (
    user_type
    ,user_group_id
    ,object_type
    ,role_id
    ,creation_date
    ,last_modification_date )
VALUES (
    'GROUP'
    ,(SELECT user_group_id
        FROM [dbo].[user_groups]
        WHERE [name] = 'Admins' )
    ,'REPOSITORY'
    ,4
    ,getdate()
    ,getdate());

INSERT INTO [dbo].[permissions] (
    user_type
    ,user_group_id
    ,object_type
    ,role_id
    ,creation_date
    ,last_modification_date)
VALUES (
    'GROUP'
    ,(SELECT user_group_id
        FROM [dbo].[user_groups]
        WHERE [name] = 'Admins')
    ,'REPOSITORY'
    ,5
    ,getdate()
    ,getdate());

INSERT INTO [dbo].[users_user_groups] (
    [user_id]
    ,[user_group_id]
    ,[creation_date]
    ,[last_modification_date])
VALUES (
    (SELECT license_id
        FROM [dbo].[licenses]
        WHERE [login] = @custom_login)
    ,(SELECT user_group_id
        FROM [dbo].[user_groups]
        WHERE [name] = 'Admins' )
    ,getdate()
    ,getdate());

truncate table [dbo].[cache_area_ancestors];

insert into cache_area_ancestors (area_id, ancestor_id, domain_id)
select area_id, parent_area_id, domain_id
from areas
where parent_area_id is not null;

insert into cache_area_ancestors (area_id, ancestor_id, domain_id)
select a1.area_id, a2.ancestor_id, a1.domain_id
from cache_area_ancestors a1 
    inner join cache_area_ancestors a2
        on a1.ancestor_id = a2.area_id;

TRUNCATE TABLE cache_permissions_repository;

TRUNCATE TABLE cache_permissions_repository_full;

TRUNCATE TABLE cache_permissions_databases;

TRUNCATE TABLE cache_permissions_databases_full;

TRUNCATE TABLE cache_permissions_areas;

TRUNCATE TABLE cache_permissions_areas_full;

TRUNCATE TABLE cache_permissions_domains;

TRUNCATE TABLE cache_permissions_domains_full;

-- REPOSITORY
INSERT INTO cache_permissions_repository (user_id)
SELECT user_id
FROM (
    -- permissions for users
    SELECT p.user_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type = 'REPOSITORY'
        AND action_code = 'DOCUMENTATION_VIEW'
        AND p.user_id IS NOT NULL

    UNION ALL

    -- permissions for user groups
    SELECT uug.user_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type = 'REPOSITORY'
        AND action_code = 'DOCUMENTATION_VIEW'
    ) AS t
GROUP BY user_id;

INSERT INTO cache_permissions_repository_full (
    user_id
    ,WEB_ACCESS
    ,DOCUMENTATION_VIEW
    ,DOCUMENTATION_EDIT
    ,COMMUNITY_VIEW
    ,COMMUNITY_EDIT
    ,SOURCE_CONNECTION_VIEW
    ,PROFILING_VIEW_DISTRIBUTION
    ,PROFILING_VIEW_DATA
    ,SCRIPTS_VIEW
    ,SCHEMA_CHANGES_VIEW
    ,AI_DESCRIPTION_GENERATE
    ,ADMIN
    ,HISTORY_VIEW
    )
SELECT user_id
    ,MAX(WEB_ACCESS) AS WEB_ACCESS
    ,MAX(DOCUMENTATION_VIEW) AS DOCUMENTATION_VIEW
    ,MAX(DOCUMENTATION_EDIT) AS DOCUMENTATION_EDIT
    ,MAX(COMMUNITY_VIEW) AS COMMUNITY_VIEW
    ,MAX(COMMUNITY_EDIT) AS COMMUNITY_EDIT
    ,MAX(SOURCE_CONNECTION_VIEW) AS SOURCE_CONNECTION_VIEW
    ,MAX(PROFILING_VIEW_DISTRIBUTION) AS PROFILING_VIEW_DISTRIBUTION
    ,MAX(PROFILING_VIEW_DATA) AS PROFILING_VIEW_DATA
    ,MAX(SCRIPTS_VIEW) AS SCRIPTS_VIEW
    ,MAX(SCHEMA_CHANGES_VIEW) AS SCHEMA_CHANGES_VIEW
    ,MAX(AI_DESCRIPTION_GENERATE) AS AI_DESCRIPTION_GENERATE
    ,MAX(ADMIN) AS ADMIN
    ,MAX(HISTORY_VIEW) AS HISTORY_VIEW
FROM (
    -- permissions for users
    SELECT p.user_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type = 'REPOSITORY'
        AND p.user_id IS NOT NULL

    UNION ALL

    -- permissions for user groups
    SELECT uug.user_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type = 'REPOSITORY'
    ) AS t
GROUP BY user_id;

-- DATABASES
INSERT INTO cache_permissions_databases (
    user_id
    ,object_id
    )
SELECT user_id
    ,database_id
FROM (
    -- permissions for users
    SELECT p.user_id
        ,p.database_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type IN (
            'DATABASE'
            ,'GLOSSARY'
            ,'REPORTING'
            )
        AND action_code = 'DOCUMENTATION_VIEW'
        AND p.user_id IS NOT NULL

    UNION ALL

    -- permissions for user groups
    SELECT uug.user_id
        ,p.database_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type IN (
            'DATABASE'
            ,'GLOSSARY'
            ,'REPORTING'
            )
        AND database_id IS NOT NULL
        AND action_code = 'DOCUMENTATION_VIEW'
    ) AS t
GROUP BY user_id
    ,database_id;

INSERT INTO cache_permissions_databases_full (
    user_id
    ,object_id
    ,WEB_ACCESS
    ,DOCUMENTATION_VIEW
    ,DOCUMENTATION_EDIT
    ,COMMUNITY_VIEW
    ,COMMUNITY_EDIT
    ,SOURCE_CONNECTION_VIEW
    ,PROFILING_VIEW_DISTRIBUTION
    ,PROFILING_VIEW_DATA
    ,SCRIPTS_VIEW
    ,SCHEMA_CHANGES_VIEW
    ,AI_DESCRIPTION_GENERATE
    ,ADMIN
    ,HISTORY_VIEW
    )
SELECT user_id
    ,database_id
    ,MAX(WEB_ACCESS) AS WEB_ACCESS
    ,MAX(DOCUMENTATION_VIEW) AS DOCUMENTATION_VIEW
    ,MAX(DOCUMENTATION_EDIT) AS DOCUMENTATION_EDIT
    ,MAX(COMMUNITY_VIEW) AS COMMUNITY_VIEW
    ,MAX(COMMUNITY_EDIT) AS COMMUNITY_EDIT
    ,MAX(SOURCE_CONNECTION_VIEW) AS SOURCE_CONNECTION_VIEW
    ,MAX(PROFILING_VIEW_DISTRIBUTION) AS PROFILING_VIEW_DISTRIBUTION
    ,MAX(PROFILING_VIEW_DATA) AS PROFILING_VIEW_DATA
    ,MAX(SCRIPTS_VIEW) AS SCRIPTS_VIEW
    ,MAX(SCHEMA_CHANGES_VIEW) AS SCHEMA_CHANGES_VIEW
    ,MAX(AI_DESCRIPTION_GENERATE) AS AI_DESCRIPTION_GENERATE
    ,MAX(ADMIN) AS ADMIN
    ,MAX(HISTORY_VIEW) AS HISTORY_VIEW
FROM (
    -- permissions for users
    SELECT p.user_id
        ,p.database_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type IN (
            'DATABASE'
            ,'GLOSSARY'
            ,'REPORTING'
            )
        AND database_id IS NOT NULL
        AND p.user_id IS NOT NULL

    UNION ALL

    -- permissions for user groups
    SELECT uug.user_id
        ,p.database_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type IN (
            'DATABASE'
            ,'GLOSSARY'
            ,'REPORTING'
            )
        AND database_id IS NOT NULL
    ) AS t
GROUP BY user_id
    ,database_id;

-- DOMAINS
INSERT INTO cache_permissions_domains (
    user_id
    ,object_id
    )
SELECT user_id
    ,database_id
FROM (
    -- permissions for users
    SELECT p.user_id
        ,p.database_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type = 'DOMAIN'
        AND database_id IS NOT NULL
        AND action_code = 'DOCUMENTATION_VIEW'
        AND p.user_id IS NOT NULL

    UNION ALL

    -- permissions for user groups
    SELECT uug.user_id
        ,p.database_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type = 'DOMAIN'
        AND database_id IS NOT NULL
        AND action_code = 'DOCUMENTATION_VIEW'
    ) AS t
GROUP BY user_id
    ,database_id;

INSERT INTO cache_permissions_domains_full (
    user_id
    ,object_id
    ,WEB_ACCESS
    ,DOCUMENTATION_VIEW
    ,DOCUMENTATION_EDIT
    ,COMMUNITY_VIEW
    ,COMMUNITY_EDIT
    ,SOURCE_CONNECTION_VIEW
    ,PROFILING_VIEW_DISTRIBUTION
    ,PROFILING_VIEW_DATA
    ,SCRIPTS_VIEW
    ,SCHEMA_CHANGES_VIEW
    ,AI_DESCRIPTION_GENERATE
    ,ADMIN
    ,HISTORY_VIEW
    )
SELECT user_id
    ,database_id
    ,MAX(WEB_ACCESS) AS WEB_ACCESS
    ,MAX(DOCUMENTATION_VIEW) AS DOCUMENTATION_VIEW
    ,MAX(DOCUMENTATION_EDIT) AS DOCUMENTATION_EDIT
    ,MAX(COMMUNITY_VIEW) AS COMMUNITY_VIEW
    ,MAX(COMMUNITY_EDIT) AS COMMUNITY_EDIT
    ,MAX(SOURCE_CONNECTION_VIEW) AS SOURCE_CONNECTION_VIEW
    ,MAX(PROFILING_VIEW_DISTRIBUTION) AS PROFILING_VIEW_DISTRIBUTION
    ,MAX(PROFILING_VIEW_DATA) AS PROFILING_VIEW_DATA
    ,MAX(SCRIPTS_VIEW) AS SCRIPTS_VIEW
    ,MAX(SCHEMA_CHANGES_VIEW) AS SCHEMA_CHANGES_VIEW
    ,MAX(AI_DESCRIPTION_GENERATE) AS AI_DESCRIPTION_GENERATE
    ,MAX(ADMIN) AS ADMIN
    ,MAX(HISTORY_VIEW) AS HISTORY_VIEW
FROM (
    SELECT p.user_id
        ,p.database_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type = 'DOMAIN'
        AND database_id IS NOT NULL
        AND p.user_id IS NOT NULL

    UNION ALL

    -- permissions for user groups
    SELECT uug.user_id
        ,p.database_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type = 'DOMAIN'
        AND database_id IS NOT NULL
    ) AS t
GROUP BY user_id
    ,database_id;

-- AREAS
INSERT INTO cache_permissions_areas (
    user_id
    ,object_id
    )
SELECT user_id
    ,database_id
FROM (
    -- permissions for users
    SELECT p.user_id
        ,p.database_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type = 'AREA'
        AND database_id IS NOT NULL
        AND action_code = 'DOCUMENTATION_VIEW'
        AND p.user_id IS NOT NULL

    UNION ALL

    -- permissions for user groups
    SELECT uug.user_id
        ,p.database_id
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type = 'AREA'
        AND database_id IS NOT NULL
        AND action_code = 'DOCUMENTATION_VIEW'
    ) AS t
GROUP BY user_id
    ,database_id;

INSERT INTO cache_permissions_areas_full (
    user_id
    ,object_id
    ,WEB_ACCESS
    ,DOCUMENTATION_VIEW
    ,DOCUMENTATION_EDIT
    ,COMMUNITY_VIEW
    ,COMMUNITY_EDIT
    ,SOURCE_CONNECTION_VIEW
    ,PROFILING_VIEW_DISTRIBUTION
    ,PROFILING_VIEW_DATA
    ,SCRIPTS_VIEW
    ,SCHEMA_CHANGES_VIEW
    ,AI_DESCRIPTION_GENERATE
    ,ADMIN
    ,HISTORY_VIEW
    )
SELECT user_id
    ,database_id
    ,MAX(WEB_ACCESS) AS WEB_ACCESS
    ,MAX(DOCUMENTATION_VIEW) AS DOCUMENTATION_VIEW
    ,MAX(DOCUMENTATION_EDIT) AS DOCUMENTATION_EDIT
    ,MAX(COMMUNITY_VIEW) AS COMMUNITY_VIEW
    ,MAX(COMMUNITY_EDIT) AS COMMUNITY_EDIT
    ,MAX(SOURCE_CONNECTION_VIEW) AS SOURCE_CONNECTION_VIEW
    ,MAX(PROFILING_VIEW_DISTRIBUTION) AS PROFILING_VIEW_DISTRIBUTION
    ,MAX(PROFILING_VIEW_DATA) AS PROFILING_VIEW_DATA
    ,MAX(SCRIPTS_VIEW) AS SCRIPTS_VIEW
    ,MAX(SCHEMA_CHANGES_VIEW) AS SCHEMA_CHANGES_VIEW
    ,MAX(AI_DESCRIPTION_GENERATE) AS AI_DESCRIPTION_GENERATE
    ,MAX(ADMIN) AS ADMIN
    ,MAX(HISTORY_VIEW) AS HISTORY_VIEW
FROM (
    SELECT p.user_id
        ,p.database_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    WHERE object_type = 'AREA'
        AND database_id IS NOT NULL
        AND p.user_id IS NOT NULL

    UNION ALL

    SELECT uug.user_id
        ,p.database_id
        ,CASE 
            WHEN action_code = 'WEB_ACCESS'
                THEN 1
            ELSE 0
            END AS WEB_ACCESS
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_VIEW'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_VIEW
        ,CASE 
            WHEN action_code = 'DOCUMENTATION_EDIT'
                THEN 1
            ELSE 0
            END AS DOCUMENTATION_EDIT
        ,CASE 
            WHEN action_code = 'COMMUNITY_VIEW'
                THEN 1
            ELSE 0
            END AS COMMUNITY_VIEW
        ,CASE 
            WHEN action_code = 'COMMUNITY_EDIT'
                THEN 1
            ELSE 0
            END AS COMMUNITY_EDIT
        ,CASE 
            WHEN action_code = 'SOURCE_CONNECTION_VIEW'
                THEN 1
            ELSE 0
            END AS SOURCE_CONNECTION_VIEW
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DISTRIBUTION'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DISTRIBUTION
        ,CASE 
            WHEN action_code = 'PROFILING_VIEW_DATA'
                THEN 1
            ELSE 0
            END AS PROFILING_VIEW_DATA
        ,CASE 
            WHEN action_code = 'SCRIPTS_VIEW'
                THEN 1
            ELSE 0
            END AS SCRIPTS_VIEW
        ,CASE 
            WHEN action_code = 'SCHEMA_CHANGES_VIEW'
                THEN 1
            ELSE 0
            END AS SCHEMA_CHANGES_VIEW
        ,CASE 
            WHEN action_code = 'AI_DESCRIPTION_GENERATE'
                THEN 1
            ELSE 0
            END AS AI_DESCRIPTION_GENERATE
        ,CASE 
            WHEN action_code = 'ADMIN'
                THEN 1
            ELSE 0
            END AS ADMIN
        ,CASE 
            WHEN action_code = 'HISTORY_VIEW'
                THEN 1
            ELSE 0
            END AS HISTORY_VIEW
    FROM dbo.permissions AS p
    INNER JOIN dbo.roles AS r ON p.role_id = r.role_id
    INNER JOIN dbo.role_actions AS ra ON r.role_id = ra.role_id
    INNER JOIN dbo.users_user_groups AS uug ON p.user_group_id = uug.user_group_id
    WHERE object_type = 'AREA'
        AND database_id IS NOT NULL
    ) AS t
GROUP BY user_id
    ,database_id;
Contact support Report issue
Found issue with this article? Comment below
Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.