Microsoft has published a dll called RETRIEVEGLOBALS.DLL that can be used to access user data from inside of Dynamics GP. I'm posting the file here, along with their documentation, so that I don't have to go looking for it. You'll find a link to the file at the very bottom of this post.
Database Connections for Modifier with VBA or Integration Manager in Microsoft Dynamics GP
Summary
The RetrieveGlobals9.dll is an ActiveX dll that will return the current
User ID, the current company logged in to, the current SQL data source as well as the current user date in Microsoft Dynamics GP. The RetrieveGlobals9.dll will also return an ADO connection object that will allow you to connect to Microsoft Dynamics GP Data. The RetrieveGlobals9.dll will work only with version 9.0 Microsoft Dynamics GP and will only work if one session of Microsoft Dynamics GP is running and logged into. The RetrieveGlobals9.dll is only for use in Modifier with VBA or Integration Manager that also require Microsoft Dynamics GP to be open and running.
Installation Instructions
1. Copy the RetrieveGlobals9.dll to a folder on your machine.
2. Register the RetrieveGlobals9.dll using regsvr32.exe. Go to Start-Run and enter the path to the regsvr32.exe, followed by the path and name of the DLL. For example:
C:\WINDOWS\system32\regsvr32.exe “c:\GP 90\RetrieveGlobals9.dll”
If the DLL registers successfully, you should get a message box indicating so.
3. Write your application code to call the RetrieveGlobals9.dll
Using the RetrieveGlobals9.dll
The retrieveuserinfo class of RetrieveGlobals9 contains the following properties and functions:
intercompany_id
The intercompany_id method retrieves the Company ID that is currently logged into Microsoft Dynamics GP. This will match with the database name in MSSQL.
Example: “TWO”.
Syntax: intercompany_id () as String
retrieve_user
The retrieve_user method retrieves the User ID that is currently logged into Microsoft Dynamics GP.
Syntax: retrieve_user() as String
sql_datasourcename
The sql_datasourcename method retrieves the current SQL Datasource name that is being utilized by Microsoft Dynamics GP.
Syntax: sql_datasourcename () as String
user_date
The user_date method retrieves the User Date from the Toolbar in Microsoft Dynamics GP.
Syntax: user_date () as Date
status
The status property indicates whether or not a valid connection object can be obtained from the connection property.
Syntax: status As Long
The status property could contain the possible values:
Value Description
0 A Microsoft Dynamics session is not open
1 A connection can be created
2 A connection could not be created
3 The current Microsoft Dynamics session is not Version 9
131072 (&H20000) A COM exception occurred during the connection attempt
262144 (&H40000) The account is locked
65536 (&H10000) The user’s password has expired
connection
The connection property contains an ADODB.Connection to the Microsoft Dynamics GP SQL Server. This connection uses the SQL Datasource, User ID and Password credentials of the user currently logged into Microsoft Dynamics GP. This connection does not have a default database specified.
Syntax: connection As Object
* The connection property will only contain a valid connection object if a Microsoft Dynamics GP 9.0 session is open and running. It should be verified that the status property has a value of 1 prior to attempting to use the connection property.
Example
Dim cn
Dim cmd As ADODB.Command
Dim rst As adodb.Recordset
dim prm as adodb.parameter
Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
If userinfo.Status = 1 Then
Set cn = userinfo.connection
luserid = userinfo.retrieve_user()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()
ldate = userinfo.user_date()
'Use the connection property to get a connection object.
Set cn = userinfo.connection
'set the database to the currently logged in db.
cn.DefaultDatabase = lintercompanyid
cmd.CommandType = 4 'stored proc
cmd.ActiveConnection = cn
cmd.commantText = "myStoredProc"
'add params
cmd.parameters.add cmd.CreateParameter ("@paramname",adVarchar,adInput,21,"")
Set rst = cmd.Execute
while not rst.eof
'do something
rst.movenext
wend
end if
cn.close
set cn = nothing