How to duplicate privileges from one SDE feature class to another with arcpy

[StefanOffermann]

Is there a simple way to duplicate privileges from a SDE feature class to another one? The szenario is a nightly update process which builds an updated featureclass FC_new, deletes FC and renames FC_new to FC. Before deleting FC, the privileges for different SDE roles/users should be duplicated from FC to FC_new. Otherwise, they are lost.

If there are any database-related solutions, they are also welcome 🙂 I am using SQL Server and ArcSDE 10.2.2.

[JakeSkinner · Accepted solution]

Hi Stefan,

You can include the Change Privileges function in your script.

[StefanOffermann]

Hi Jake, thanks for this function, I can use it to write the privileges. But how can I read the existing privileges from the existing feature class? I havent found a function for that. I hoped the Describe-function can help me, but I could not find a Describe object containing the privileges of SDE tables / feature classes.

[JakeSkinner]

You can use the ArcSDESQLExecute function to execute the below SQL query to find the privileges for each user:

select permission_name, pr.name
from sys.database_permissions pe
join sys.database_principals pr on pe.grantee_principal_id = pr.principal_id
where pe.class = 1 
    and pe.major_id = object_id('<owner>.<feature class name')
    and pe.minor_id = 0;

You will want to replace '<owner>.<feature class name>'.

Here is an example:

import arcpy

sde_conn = arcpy.ArcSDESQLExecute(r"Database Connections\DBO@SQLSERVER.sde")

sql_statement = "select permission_name, pr.name from sys.database_permissions pe join sys.database_principals pr on pe.grantee_principal_id = pr.principal_id where pe.class = 1 and pe.major_id = object_id('vector.AIRPORTS') and pe.minor_id = 0;"

sde_return = sde_conn.execute(sql_statement)
for row in sde_return:
    print "User:  " + row[1] + "\t Privilege:  " + row[0]

[StefanOffermann]

Thanks for the code! I will try it. Although it would be quite nice if the geodatabase administration toolbox would also contain a GP tool to read privileges from a dataset, accordingly to Change Privileges tool.

[JakeSkinner]

Any enhancements you would like to see, you can post the idea on ESRI's ArcGIS Ideas page.

[Polesig]

I Tried your requests, but with no success...

On SQL Server requests written in the article https://sqlserverguides.com/sql-server-user-permissions/ worked for me.

[StefanOffermann]

My solution for our customer is to administer the privileges in a CSV-file. When updating the feature classes, the privileges a read from the file (not from the existing feature classes) and set via Change Privilege using a python script.

[AndyOmmen]

I think the easiest way would be to place the updated FC into a feature dataset where the user/role privileges are constant and apply to all FC's it contains. If using a feature dataset isn't possible then I would look into the Change Privilege GP tool. In this GP tool you can configure the privileges you want to assign to the user/roles on the new FC. Since its a GP tool you can run it in batch if there are multiple user/roles you need to configure. Scripting this in ArcPy would be an option as well.

[StefanOffermann]

Good hint to use feature datasets! Unfortunately, feature datasets are not used in our case.