Toad World® Forums

Object permission

Hi, please can You help me: how setting Toad Data Modeler for SQL Preview including object permission? I need view SQL script with grants, for examle:

CRATE TABLE xyz (

)

go

GRANT SELECT on xyz to user_a

go

(I have defined users and groups in fold ‘Permissions’, but in SQL Script aren’t), thanks.

Hello Robert,

Permissions are generated in separated section, so it doesn’t possible see it in sql preview of table. You need go to DDL Script Generation (F9) to get sql code for permissions.

There are a few settings for generate permissions.

  • Generate Permissions Only to Object Types Selected on Tab What to generate(Detail Settings Tab)

  • Permission to Object (node in tree on What to Generate Tab)

Regard

Daril

Hallo Daril,

thanks for reply - I know this chances, but it’s not more comfortable :frowning:

In more cases programmer create one new table in existing model and generating all sql include permissions will be better…

Maybe in future

Robert

Hello Robert,

this functionality is not implemented, but never mind I created macro that do it. It is very easy.

How Macro works:

Macro is design for SQL Server Models and TDM 6.3. Now you can right click on Entity and from popup menu choose item Macro, there will be new item “Generate Permission”. If you select this item SQL code for permission will be generated to Message Explorer.

You can modify macro how you want(Output can be to file…). If you need some help don’t hesitate contact me.

How Import Package with Macro:

In attachment is file “PermissionMacro.zip”. You can download this file and unpack it.

In TDM switch on expert mode (Main Menu - Settings- Options-General).

And choose from Menu - Expert Mode - Customization - Import Custom Package (It is only copy this file to package folder).

Best Regards

Daril
PermissionMacro.zip (1.13 KB)

Hallo Daril,

I appreciate Your work and thanks - but macro was not displayed. I have installed TDM 6.3.2.22 (licenced), file was imported (TDM restarted) and nothing happened :frowning:

As You can see, this is detail of my entity with rights:

entity.jpeg

and there is macro menu on this entity:

macros.jpeg

thanks for help

Robert

Hello Robert,

what database are you use? I presumed from “go” terminator that it is Microsoft SQL Server (2016) so macro was enabled only for this platform.
From your screenshot it is look like SAP ASE, but I’m not sure(TDM support a lot of database platforms).

In attachment is the new package and it has enabled SAP ASE. You need switch off TDM and overwrite on disc previous package. Folder where it is located you can find in Main Menu - Settings - Options- Paths-Advanced-Path to Packages. Default is

“C:\Users\XXX\Documents\Toad Data Modeler\Standard Installation\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC}”

If it is not SAP , please let me know your DB platform.

Daril
PermissionMacro.zip (1.16 KB)

Hallo again, Daril,

You are right, it’s SAP ASE - now submenu ‘Generate Permission’ is in Macros menu, but when I click, nothing happened.

Robert

Hi Robert,

I have checked this macro again and it works for me. Open the popup menu of the entity, select Macro | Generate Permission and see the Message explorer. There should be a new message "Entity". If you double click on it to open detail you will see generated GRANT statement.

David

Hallo David,

thank You for this explanation, in this view it was my mistake - generated grants are in Message Explorer.

Thanks all for good sulution.

Robert