6

I am writing an arcpy script where an SDE feature class is removed then recreated. I need to set permissions on the recreated feature class that match the permissions before it was removed.

Is there a way to identify existing explicit permissions using arcpy? I've taken a look through arcpy.Describe() and in the Geodatabase Administration toolbox, but can't see anything that will do this.

I'm going to use arcpy.ChangePrivileges_management() to set the new permissions once the feature class has been recreated.

The only way I've come across to get the permissions is to use use arcpy to pass a SQL query to find permissions:

myDB = "database.sde"
myFC = "FCName"
permissionsQuery = permissionsQuery = """
    SELECT permission_name, state, 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('{}')
    AND pe.minor_id = 0
    ORDER BY pe.permission_name""".format(myFC)

dbConn = arcpy.ArcSDESQLExecute(myDB)

permissions = dbConn.execute(permissionsQuery)
print permissions

which will output something like

[[u'DELETE', u'G', u'midavalo'], [u'INSERT', u'G', u'midavalo'], [u'SELECT', u'G', u'midavalo'], [u'SELECT', u'G', u'some_role'], [u'UPDATE', u'G', u'midavalo']]

which I can then use to pass back to arcpy.ChangePrivileges_management() but would like to make use of an arcpy tool to do this properly if available.

  • ArcGIS (desktop and server) 10.3.1
  • SQL Server 2012
4

I didn't find a pre-built tool to do this, but ended up using the following code which was easy enough to then query the dictionary to pass to arcpy.ChangePrivileges_management()

import arcpy

def retrieve_permissions(db, fc):
    sdeDBConn = arcpy.ArcSDESQLExecute(db)

    permissionsQuery = """SELECT permission_name, state, 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('{}')
        AND pe.minor_id = 0
        ORDER BY pe.permission_name""".format(fc)

    exPermissions = sdeDBConn.execute(permissionsQuery)

    viewSet = set()
    editSet = set()

    if exPermissions != True:
        for exPermission in exPermissions:
            if exPermission[0] in ['DELETE', 'UPDATE', 'INSERT']:
                viewSet.add(exPermission[2])
                editSet.add(exPermission[2])
            else:
                viewSet.add(exPermission[2])

    permissionDict = {'VIEW': viewSet, 'EDIT': editSet}

    return permissionDict

sdePath = r"PathToConnectionFile.sde"
layerToQuery = 'MyFeatureClass'

print retrieve_permissions(sdePath, layerToQuery)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.