replace data user

If you make use of "guest users" you might want to replace a data user by another data user without users noticing the exchange.

There is no feature in SecurityAdministrator to replace a data user automatically. We have had the need to replace an existing data user with another (newer) data user a couple of times. First I created a few SQL statements which produced the required SQL statements for performing the switch. But it was quite a lot of manual labour involved and as I had to alter 10 guest users it became clear that a slightly more automated procedure would be helpful. I put Batch and SQL files together and semi-automated the first part of the process. Replacing a data user now doesn't take longer than 5 minutes. If one wanted to the process could be automated further.

First batch file
- it only gives you the name of the second role associated with the guest user - you need to put the name into the second batch file
- it also creates the GRANT statement for the guest user in order to be able to create synonyms (and the corresponding REVOKE statement as well).

Second batch file
- it requires to fill in the names / password of the existing data user, the new data user and the guest user and the name of the two roles as well
- it creates all sql statements you need to run to perform the switch

SQL scripts
- you need to run 5 resulting sql scripts manually
- the scripts need to be executed with different users

script 1
The guest user gets direct grants from the data user.
These grants need to be replaced.

script 2
The grants from the "old"  data user (the one which is replaced) to the guest user need to be revoked.

script 3
The guest user has synonyms pointing to the data user.
These synonyms need to be replaced.

script 4
There are two roles associated with a guest user. Grants to these roles need to be replaced.

script 5
The grants from the "old"  data user (the one which is replaced) to the guest user roles need to be revoked.


So far we haven't seen any issues replacing the data user (neither in AutoCAD Map nor in AIMS - both only operate with guest users). But there is no guarantee that the scripts above do all things required for a complete switch.

Download: https://drive.google.com/file/d/0B-MpwgWFuw57aktZbUdYcTA1eXM/edit?usp=sharing

Map 2103, SP2