Reverse engineering just SQL Server User and User Group data


I have a question regarding a situation I often find myself in. Often I will need to add a user login to my analytics DWH , this generates a user and adds the user to the appropriate user role according to the employee type.

As the ability to add logins to a database and cascade creation of said user and user role data does not appear to exist to my knowledge in TOAD I typically add the login through SQL server management studio (SMS) and leave it at that. Problem is my data models user and user groups gets out of date. I have no way to update it from the DWH. So a couple questions:

Question 1: Does this functionality exist in TOAD. E.G. I create logins, toad automatically creates users and adds them to appropriate user groups in toad automatically. Then I can generate DDL for said logins and run in SMS. The DDL should create the login, user and assign the user to the appropriate user Role.

Question 2: If the answer to question one is no (and even if it is not it would be interesting to know if this is possible) is there any way to essentially “reverse engineer” my new Logins, users and their respective roles back into my model after having created the login/user/user role allocaitons in SMS. I know its really easy to do this with tables, functions, procedures (you can just drag and drop with object explorer) however nowhere can I see an option to do this JUST for users and user groups in TOAD. E.G. I want to drag and drop my new users into my model, and have toad if possible automatically add them to the appropriate user group.

I could just create another physical data model,reverse engineer the entire database into the new model and I know this will bring in everything including said users and user groups into my model. Then I can probably find a way to copy and paste from one physical model to the other. I was hoping however that there was a more eloquent solution.

thanks in advance



in version 5.4 that is going to be released soon, it will be possible to specify and geneate code for Users (In version 5.3 and earlier this functionality is missing.)

Answer 1: see the following screenshots to find out how to define code for users in version 5.4.
Statements for Login creations must be defined manually - on tab Before Script.

Answer 2: reverse engineering of "selected" users and/or logins is not supported and this feature will not appear in version 5.4.
I created new change request: TDM-1337.


Vaclav & Mario