For now, I’ll put any useful short SQL scripts related to Dynamics GP on this page. If it evolves, I’ll put it “somewhere else” 🙂
Getting a user’s last login to Dynamics GP (if Activity Tracking is enabled):
SELECT TOP 1 * FROM DYNAMICS..SY05000
WHERE userid = ” AND SECDESC = ‘Successful Attempts to Log In’
ORDER BY DEX_ROW_ID DESC
Resetting the Dynamics GP system password to :
UPDATE DYNAMICS..SY02400 SET PASSWORD = 0x00202020202020202020202020202020
Listing all security granting of a specific task sorted by company, role, user:
SELECT T2.CMPANYID,T3.CMPNYNAM, T1.SECURITYROLEID,T1.SECURITYTASKID, T2.USERID FROM DYNAMICS..SY10600 T1 WITH (NOLOCK)
INNER JOIN DYNAMICS..SY10500 T2 WITH (NOLOCK) ON T1.SECURITYROLEID = T2.SECURITYROLEID
INNER JOIN DYNAMICS..SY01500 T3 WITH (NOLOCK) ON T2.CMPANYID = T3.CMPANYID
WHERE T1.SECURITYTASKID = ”
ORDER BY T2.CMPANYID,T1.SECURITYROLEID, T1.SECURITYTASKID, T2.USERID
Listing all security granting of a specific window or report:
Thanks to Mr David Musgrave, an oldie but goodie blog post that has this info: http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/11/12/how-to-identify-the-security-tasks-and-security-roles-associated-with-a-specific-window-or-report.aspx
SELECT ISNULL(A.SECURITYROLEID,”) AS SECURITYROLEID, ISNULL(M.SECURITYROLENAME,”) AS SECURITYROLENAME, ISNULL(M.SECURITYROLEDESC,”) AS SECURITYROLEDESC,
ISNULL(O.SECURITYTASKID,”) AS SECURITYTASKID, ISNULL(T.SECURITYTASKNAME,”) AS SECURITYTASKNAME, ISNULL(T.SECURITYTASKDESC,”) AS SECURITYTASKDESC,
R.PRODNAME, R.TYPESTR, R.DSPLNAME, R.RESTECHNAME, R.DICTID, R.SECRESTYPE, R.SECURITYID
FROM DYNAMICS.dbo.SY09400 R
FULL JOIN DYNAMICS.dbo.SY10700 O ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID
FULL JOIN DYNAMICS.dbo.SY09000 T ON T.SECURITYTASKID = O.SECURITYTASKID
FULL JOIN DYNAMICS.dbo.SY10600 A ON A.SECURITYTASKID = T.SECURITYTASKID
FULL JOIN DYNAMICS.dbo.SY09100 M ON M.SECURITYROLEID = A.SECURITYROLEID
WHERE R.DSPLNAME = ‘Multicurrency Exchange Rate Maintenance’
greg's wordpress... 