topic Re: How to duplicate privileges from one SDE feature class to another with arcpy in Python Questions

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

StefanOffermann — Mon, 04 Aug 2014 13:11:07 GMT

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.

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

JakeSkinner — Mon, 04 Aug 2014 13:15:47 GMT

Hi Stefan,

You can include the Change Privileges function in your script.

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

StefanOffermann — Mon, 04 Aug 2014 15:44:41 GMT

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.

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

JakeSkinner — Mon, 04 Aug 2014 17:05:01 GMT

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]

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

StefanOffermann — Tue, 05 Aug 2014 05:18:15 GMT

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.

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

JakeSkinner — Tue, 05 Aug 2014 11:32:52 GMT

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

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

AndyOmmen — Tue, 05 Aug 2014 16:22:34 GMT

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.

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

StefanOffermann — Wed, 06 Aug 2014 09:21:50 GMT

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.

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

StefanOffermann — Wed, 06 Aug 2014 09:22:36 GMT

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

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

Polesig — Fri, 29 Jul 2022 09:39:35 GMT

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.