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

Answer 1

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)