Back to Top

Manage connections to enterprise geodatabases in SQL Server

In this topic

  1. Identify and remove connections in ArcGIS AllSource
  2. Identify and remove connections using Python

Available with Standard or Advanced license.

As the geodatabase administrator, you occasionally need to identify who is connected to the geodatabase and which connections are locking resources. You also may need to remove certain connections if people are not present to disconnect themselves. For example, an editor may have left ArcGIS AllSource open while editing, locking the data in the edit session and preventing other editors from accessing it and preventing the data owner from altering the schema of the tables. Or you may require everyone to disconnect so you can apply a patch to the database, restore the database, or compress the geodatabase.

You can connect to the geodatabase as the geodatabase administrator from ArcGIS AllSource to view and, if necessary, drop connections. Or you can use ArcPy functions to complete the same workflow.

To disconnect users from an enterprise geodatabase in Microsoft SQL Server, the geodatabase administrator may need additional privileges.

  • The geodatabase administrator in an sde-schema geodatabase (sde) requires VIEW DEFINITION privileges on the database to see the list of connected users and must be a member of the processadmin fixed server role to disconnect users.
  • If the geodatabase administrator of a dbo-schema geodatabase is the database owner, the geodatabase administrator must be added to the processadmin fixed server role to disconnect users.
No additional privileges are required for the geodatabase administrator in a dbo-schema geodatabase if the geodatabase administrator is a member of the sysadmin fixed server role.

Caution:

Use caution when disconnecting users. Avoid disconnecting active sessions, especially if the connected user is in the process of editing. Only disconnect sessions that were left open but are not being used (for example, if someone left the session open over the weekend), orphaned sessions that are locking an object, or sessions involved in a deadlock situation.

First, determine who is connected to the geodatabase. If necessary, you can then remove connections.

Identify and remove connections in ArcGIS AllSource

  1. Start ArcGIS AllSource.
  2. Right-click the database connection and click Administration > Connections.

    A list is displayed showing all sessions that are currently connected to the geodatabase. Your session is displayed in italic text.

  3. Contact individuals and ask them to close their sessions. If you are unable to contact them and still need to disconnect them from the geodatabase, proceed with the next step.
  4. Right-click the specific session you want to remove from the geodatabase and click Disconnect.

    The session is immediately dropped from the geodatabase.

  5. Close the Geodatabase Administration dialog box.

Identify and remove connections using Python

You can run ArcPy functions to create a geodatabase connection file for the geodatabase administrator user, use that connection file to connect to the geodatabase and get a list of all current geodatabase connections, and remove a connection from the geodatabase if necessary.

  1. Create a database connection file by running the CreateDatabaseConnection_management function in a Python window.

    In this example, a connection file (ssgdb.sde) is created in the temp folder. The connection is made to the gisprod database on the sdept4 SQL Server instance, logging in as sa.

    import arcpy
arcpy.CreateDatabaseConnection_management ("c:\\temp","ssgdb.sde","SQL_SERVER","sdept4","DATABASE_AUTH","sa","esayP@ss","SAVE_USERNAME","gisprod")

  2. Run the ListUsers function to get a list of all current connections to the gisprod geodatabase.

    Provide the connection file you created in the previous step.

    ##No need to import arcpy if you are running functions in the same Python window.
import arcpy

arcpy.ListUsers("c:\\temp\ssgdb.sde")

    A list of user connections is returned.

    [user(ClientName=u'PC4', ConnectionTime=datetime.datetime(2018, 10, 18, 8, 30, 19),
 ID=18, IsDirecConnection=True, Name=u'PUBLISHER1')]
[user(ClientName=u'PC25', ConnectionTime=datetime.datetime(2018, 10, 21, 14, 10, 43),
 ID=33, IsDirecConnection=True, Name=u'EDITOR2')]
[user(ClientName=u'PC11', ConnectionTime=datetime.datetime(2018, 10, 22, 9, 18, 26),
 ID=39, IsDirecConnection=True, Name=u'READER5')]
[user(ClientName=u'PCA2', ConnectionTime=datetime.datetime(2018, 10, 22, 11, 21, 2),
 ID=41, IsDirecConnection=True, Name=u'DBO')]

  3. Contact individuals and ask them to close their sessions. If you are unable to contact them and still need to disconnect them from the geodatabase, proceed with the next step.
  4. Use the information obtained from the ListUsers function to identify which connection to remove using the DisconnectUser function.

    Specify the ID of the connection to remove. Here, the connection with ID 33 is removed:

    ##No need to import arcpy if you are running functions in the same Python window.
import arcpy

arcpy.DisconnectUser("c:\\temp\ssgdb.sde",33)

    The session is immediately dropped from the geodatabase.

Feedback on this topic?

In this topic
  1. Identify and remove connections in ArcGIS AllSource
  2. Identify and remove connections using Python