Simplify Migration of Permissions

November 6, 2020 — William Craft

If you’re a database person like me, and you often stand up new geodatabases for whatever the reason, you already know that migrating permissions can be challenging, depending on your requirements.  Let’s say you’re tasked with creating a new copy of a geodatabase but, for various reasons, you aren’t able to use traditional DBMS tools to clone or replicate the source geodatabase.  Instead, maybe you are left with performing a copy/paste of the data using ArcCatalog.  Alternatively, perhaps you’re organization is migrating from one DBMS platform to another, such as moving from Oracle to SQL Server or vice versa.  Whatever the reason may be, the method in which you migrate your data will more than likely not include the original permissions within the target geodatabase output.  If only you had an easy way to extract the geodatabase’s spatial data permissions and then subsequently apply those permissions to your new geodatabase.  Otherwise, you’ll find yourself manually setting permissions for each dataset while trying to map which roles should be granted read versus read/write permissions.  Well, with a little bit of SQL and the use of ArcPy, you can bridge this gap quite easily.

For the two most popular DBMS platforms (Oracle and SQL Server), I’ve included custom SQL that will interrogate your database and extract its spatial data permissions in Python format.  It doesn’t matter if your data is versioned or not, or if your data exists in feature datasets or at the root of the geodatabase.  Even geometric networks are covered with this SQL.  Run these scripts with an elevated account in your source geodatabase, capture the results, and you’ll be up and running in no time with permissions properly set in your target geodatabase.

General Workflow

  1. With an elevated account, run the appropriate script depending on your DBMS platform within your source geodatabase.
  2. Save the resulting Python code to a .PY script file.
  3. In the target geodatabase, run the script file to apply the original source geodatabase permissions.

SQL Server Script Download

Click to Download 

Oracle Script Download

Click to Download 

A Framework For Understanding & Keeping Pace With

The Future of GIS

Download It for Free

William Craft

What do you think?

Leave a comment, and share your thoughts

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.