Finding SDE permissions on feature class using ArcPy?

Question

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

Midavalo · Accepted Answer · 2016-07-21 00:25:20Z

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)

Stack Exchange Network

Finding SDE permissions on feature class using ArcPy?

1 Answer 1

Your Answer

Hot Network Questions

Finding SDE permissions on feature class using ArcPy?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions