Set Admin Rights in empower® sheets

With this script, you can set Admin Rights for empower® sheets to a user in the database. With these permissions, the user can edit style elements (cell, table and chart styles) in empower® sheets.

-- Insert the PrincipalId for the user that will become an admin

DECLARE @AdminUserPrincipalId NVARCHAR(50) = N'[ADMIN_USER_PRINCIPAL_ID]'

-- Or try executing this script by inserting first and last name for user that will become an admin

DECLARE @FirstName NVARCHAR(50) = '[ADMIN_USER_FIRST_NAME]'

DECLARE @LastName NVARCHAR(50) = '[ADMIN_USER_LAST_NAME]'


-- Insert '1' for every Admin-Permission this user will get

DECLARE @StyleAdminPermission BIT = 0  -- Can add and administer customizing items (table, cell & chart styles)

   
-- PLEASE DO NOT TOUCH ANYTHING FROM HERE

DECLARE @PrincipalId UNIQUEIDENTIFIER

DECLARE @UserFound BIT = 0

BEGIN TRY

    SET @PrincipalId = CONVERT(uniqueidentifier, @AdminUserPrincipalId)

    IF EXISTS

    (

        SELECT TOP(1) [PrincipalID]

        FROM [core].[PrincipalUsers]

        WHERE [PrincipalID] = @PrincipalId

    )

        SET @UserFound = 1

    ELSE

        PRINT CONCAT('The user with the ID"', @AdminUserPrincipalId, '" could not be found!');

END TRY

BEGIN CATCH

    PRINT 'AdminUserPrincipal ID not inserted or invalid.'

END CATCH

IF @UserFound = 0 BEGIN

    IF NOT EXISTS

    (

        SELECT TOP(1) [PrincipalID]

        FROM [core].[PrincipalUsers]

        WHERE [FirstName] = @FirstName

        AND [LastName] = @LastName

    )

        PRINT CONCAT('The user "', @FirstName, ' ', @LastName, '" could not be found!')

    ELSE IF

    (

        SELECT COUNT(*) FROM [core].[PrincipalUsers]

        WHERE [FirstName] = @FirstName

        AND [LastName] = @LastName

    ) > 1

    BEGIN

        PRINT CONCAT('There is more than one user with named ', @FirstName, ' ', @LastName, '.')

        PRINT 'Please execute this script using the user''s prinipal ID.'

    END

    ELSE BEGIN

        -- Fetch user's principal Id

        SET @PrincipalId =

        (

            SELECT TOP(1) [PrincipalID]

            FROM [core].[PrincipalUsers]

            WHERE [FirstName] = @FirstName

            AND [LastName] = @LastName

            ORDER By [LastLoginOn] DESC

        )

        SET @UserFound = 1

    END

END

IF @UserFound = 1 BEGIN

       

    DECLARE @RootFolderId UNIQUEIDENTIFIER = N'00000000-0000-0000-5000-000000000013'

    DECLARE @AdminPermissionRoleId UNIQUEIDENTIFIER = N'00000000-0000-1000-2000-000000000003'

     

    -- Add sheets customizing style admin permission

    IF @StyleAdminPermission = 1 BEGIN

       

        DECLARE @SheetsStyleRootFolderName NVARCHAR(50) = 'sheets.CorporateDesignStyles'

        DECLARE @SheetsStyleRootFolderId UNIQUEIDENTIFIER =

        (

            SELECT TOP(1) [ID]

            FROM [core].[TreeFolders]

            WHERE [Name] = @SheetsStyleRootFolderName

            AND [ParentId] =  @RootFolderId

        )

        IF EXISTS

        (

            SELECT *

            FROM [core].[PermissionRoleTreeFolderAssignments]

            WHERE [PrincipalID] = @PrincipalId

            AND [FolderID] = @SheetsStyleRootFolderId

            AND [PermissionRoleID] = @AdminPermissionRoleId

        )

            PRINT 'This user already has admin permissions for sheets style elements!'

        ELSE

            INSERT INTO [core].[PermissionRoleTreeFolderAssignments]

                    ([ID]

                    ,[FolderID]

                    ,[PrincipalID]

                    ,[PermissionRoleID]

                    ,[LastModifiedOn])

                VALUES

                    (NEWID()

                    ,@SheetsStyleRootFolderId

                    ,@PrincipalId

                    ,@AdminPermissionRoleId

                    ,GETUTCDATE())

    END;

END