Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This model is defined in the following way:
Access to all windows, tables, reports and miscellaneous permissions are classed as Security Operations.
A set of Security Operations required to perform a specific task are assigned to a Security Task.
Multiple Security Tasks required to perform a specific role are assigned to a Security Role.
Each User and Company combination can then have multiple Security Roles assigned to it.
Note: Operations may be assigned to multiple Security Tasks and Security Tasks may be assigned to multiple Security Roles.
In the situation when a system administrator knows which window (or report) they wish to grant access to a user, but does not know what Security Tasks or Security Roles are associated with the window, there is no simple method to obtain this information from within the application. It would be possible to scroll through each Security Task on the Security Task Setup window (Microsoft Dynamics GP >> Tools >> Setup >> System >> Security Tasks) and check if the window is selected, but this is time consuming. The Print Operation Access report which can be printed after selecting the window will show which users have access to the window, but not how that access was obtained based on the Security Roles and Security Tasks.
To obtain the data we will use a new Security Resource Descriptions table (Technical Name: syCurrentResources (SY09400) table) which was added to v10.0 to create a SQL Query to obtain the information. This table is initially empty, but can be populated by running the Clear Data File Maintenance process on it. The system will then rebuild the contents based on the current installed dictionaries.
Below are the steps to populate the Security Resource Descriptions table:
Click Microsoft Dynamics GP, point to Maintenance, and then click Clear Data to open the Clear Data window.
On the Display menu, click Physical.
In the Series list, click System.
In the Tables pane, click the Security Resource Descriptions table, and then click Insert.
Click OK.
Click Yes.
In the Report Destination window, select the Screen check box, and then click OK to send the report to the screen.
Close the report.
Now that the Security Resource Descriptions table has been populated we can use it in a SQL Query from SQL Query Analyzer (SQL Server 2000) or SQL Server Management (SQL Server 2005). The Query below will display the Security Roles and Security Tasks associated with a specific window or report as selected by changing the Display Name on the last line of the query.
SQL Query:
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 = '<Display_Name>'
Note: The <Display_Name> placeholder represents the actual display name. For example, the display name may be "Sales Transaction Entry".
Below are the example results based on a default installation for 'Sales Transaction Entry':
Result Set:
SECURITYROLEID SECURITYROLENAME SECURITYTASKID SECURITYTASKNAME PRODNAME TYPESTR DSPLNAME RESTECHNAME DICTID SECRESTYPE SECURITYID
----------------------- ------------------------------- ----------------------- ----------------------- ----------------------- --------------- ------------------------------- --------------- ------- --------------- ----------
BOOKKEEPER* Bookkeeper TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
CUSTOMER SERVICE REP* Customer Service Representative TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
OPERATIONS MANAGER* Operations Manager TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
SHIPPING AND RECEIVING* Shipping and Receiving TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
If there are no Security Roles assigned to the Security Tasks, they will show as blank. If there are no Security Tasks assigned to the Operation, they will also show as blank.
Security Table Information
Security Operations for a Security Task are stored in table sySecurityAssignTaskOperations (SY10700).
Security Tasks are defined in table sySecurityMSTRTask (SY09000).
Security Tasks for a Security Role are stored in table sySecurityAssignTaskRole (SY10600).
Security Roles are defined in table sySecurityMSTRRole (SY09100).
Security Roles for a User and Company combination are stored in table sySecurityAssignUserRole (SY10500).
Also see the following post for how to use the Support Debugging Tool for Microsoft Dynamics GP to achieve the same results:
How to identify the Security Tasks and Security Roles using the Support Debugging Tool
Edit: Build 10 of the Support Debugging Tool now includes a Security Information window which can be opened from the Security Profiler and Resource Information windows. This window will display the Security Tasks and Security Roles associated with the select resource and provide easy navigation to the security windows to make changes if desired. Just right click and select Security Information to open the window. For more information see Support Debugging Tool Build 10 released.
by David
Copying Microsoft Dynamics GP Navigation Bar shortcuts from one user to another
/******************************************************
*
* by Mariano Gomez, MVP
*
*******************************************************/
use DYNAMICS;
GO
declare @sourceUser char(20); set @sourceUser = 'LESSONUSER2';
declare @destntUser char(20); set @destntUser = 'LESSONUSER1';
if exists(select * from SY01990 where ScbOwnerID = @destntUser)
delete from SY01990 where ScbOwnerID = @destntUser;
insert into SY01990 (
ScbGroupType,
ScbOwnerID,
ScbNodeID,
ScbParentNodeID,
ScbShortcutType,
ScbSubType,
ScbDisplayName,
ScbShortcutKey,
ScbTargetStringOne,
ScbTargetStringTwo,
ScbTargetStringThree,
ScbTargetLongOne,
ScbTargetLongTwo,
ScbTargetLongThree,
ScbTargetLongFour,
ScbTargetLongFive,
ScbCompanyID)
select
ScbGroupType,
@destntUser,
ScbNodeID,
ScbParentNodeID,
ScbShortcutType,
ScbSubType,
ScbDisplayName,
ScbShortcutKey,
ScbTargetStringOne,
ScbTargetStringTwo,
ScbTargetStringThree,
ScbTargetLongOne,
ScbTargetLongTwo,
ScbTargetLongThree,
ScbTargetLongFour,
ScbTargetLongFive,
ScbCompanyID
from SY01990
where ScbOwnerID = @sourceUser
GO
use DYNAMICS;
GO
declare @sourceUser char(20); set @sourceUser = 'LESSONUSER2';
declare @destntUser char(20); set @destntUser = 'LESSONUSER1';
if exists(select * from SY01990 where ScbOwnerID = @destntUser)
delete from SY01990 where ScbOwnerID = @destntUser;
insert into SY01990 (
ScbGroupType,
ScbOwnerID,
ScbNodeID,
ScbParentNodeID,
ScbShortcutType,
ScbSubType,
ScbDisplayName,
ScbShortcutKey,
ScbTargetStringOne,
ScbTargetStringTwo,
ScbTargetStringThree,
ScbTargetLongOne,
ScbTargetLongTwo,
ScbTargetLongThree,
ScbTargetLongFour,
ScbTargetLongFive,
ScbCompanyID)
select
ScbGroupType,
@destntUser,
ScbNodeID,
ScbParentNodeID,
ScbShortcutType,
ScbSubType,
ScbDisplayName,
ScbShortcutKey,
ScbTargetStringOne,
ScbTargetStringTwo,
ScbTargetStringThree,
ScbTargetLongOne,
ScbTargetLongTwo,
ScbTargetLongThree,
ScbTargetLongFour,
ScbTargetLongFive,
ScbCompanyID
from SY01990
where ScbOwnerID = @sourceUser
GO
Management Reporter versions
BY VICTORIA YUDIN
If anyone has other versions, please let me know and I will update this list.
Management Reporter 2.0
SP 1 : 2.0.1663.3
FP 1 : 2.0.1664.19
SP 2 : 2.0.1700.31
SP 2 Update : 2.0.1700.66
Management Reporter 2012
RTM : 2.1.1026.37
RU1 : 2.1.1028.0
RU2 : 2.1.1029.30
RU3 : 2.1.1032.30
RU4 : 2.1.1033.30
RU5* : 2.1.1037.15
CU6 : 2.1.6041.36
CU7 : 2.1.7044.43
CU8 : 2.1.8001.0
CU9 : 2.1.9001.11
CU10 : 2.1.10001.112
CU11 : 2.1.11001.1
CU12 : 2.1.12000.26
CU13 : 2.12.13001.13
* This build number for RU 5 includes a hot fix. From what I have found, the original RU 5 was build number was 2.1.1037.12
Downloads for Management Reporter 2012 can be found here on CustomerSource (you will need a login).
If anyone has other versions, please let me know and I will update this list.
Management Reporter 2.0
SP 1 : 2.0.1663.3
FP 1 : 2.0.1664.19
SP 2 : 2.0.1700.31
SP 2 Update : 2.0.1700.66
Management Reporter 2012
RTM : 2.1.1026.37
RU1 : 2.1.1028.0
RU2 : 2.1.1029.30
RU3 : 2.1.1032.30
RU4 : 2.1.1033.30
RU5* : 2.1.1037.15
CU6 : 2.1.6041.36
CU7 : 2.1.7044.43
CU8 : 2.1.8001.0
CU9 : 2.1.9001.11
CU10 : 2.1.10001.112
CU11 : 2.1.11001.1
CU12 : 2.1.12000.26
CU13 : 2.12.13001.13
* This build number for RU 5 includes a hot fix. From what I have found, the original RU 5 was build number was 2.1.1037.12
Downloads for Management Reporter 2012 can be found here on CustomerSource (you will need a login).
How to identify the Security Tasks and Security Roles associated with a specific window or report
by David Musgrave
Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This model is defined in the following way:
Access to all windows, tables, reports and miscellaneous permissions are classed as Security Operations.
In the situation when a system administrator knows which window (or report) they wish to grant access to a user, but does not know what Security Tasks or Security Roles are associated with the window, there is no simple method to obtain this information from within the application. It would be possible to scroll through each Security Task on the Security Task Setup window (Microsoft Dynamics GP >> Tools >> Setup >> System >> Security Tasks) and check if the window is selected, but this is time consuming. The Print Operation Access report which can be printed after selecting the window will show which users have access to the window, but not how that access was obtained based on the Security Roles and Security Tasks.
To obtain the data we will use a new Security Resource Descriptions table (Technical Name: syCurrentResources (SY09400) table) which was added to v10.0 to create a SQL Query to obtain the information. This table is initially empty, but can be populated by running the Clear Data File Maintenance process on it. The system will then rebuild the contents based on the current installed dictionaries.
Below are the steps to populate the Security Resource Descriptions table:
SQL Query:
SELECT ISNULL(A.SECURITYROLEID,'') AS SECURITYROLEID, ISNULL(M.SECURITYROLENAME,'') AS SECURITYROLENAME, --ISNULL(M.SECURITYROLEDESC,'') AS SECURITYROLEDESC,
Note: The <Display_Name> placeholder represents the actual display name. For example, the display name may be "Sales Transaction Entry".
Below are the example results based on a default installation for 'Sales Transaction Entry':
Result Set
SECURITYROLEID SECURITYROLENAME SECURITYTASKID SECURITYTASKNAME PRODNAME TYPESTR DSPLNAME RESTECHNAME DICTID SECRESTYPE SECURITYID
If there are no Security Roles assigned to the Security Tasks, they will show as blank. If there are no Security Tasks assigned to the Operation, they will also show as blank.
Security Table Information
Security Operations for a Security Task are stored in table sySecurityAssignTaskOperations (SY10700).
Also see the following post for how to use the Support Debugging Tool for Microsoft Dynamics GP to achieve the same results:
How to identify the Security Tasks and Security Roles using the Support Debugging Tool
Edit: Build 10 of the Support Debugging Tool now includes a Security Information window which can be opened from the Security Profiler and Resource Information windows. This window will display the Security Tasks and Security Roles associated with the select resource and provide easy navigation to the security windows to make changes if desired. Just right click and select Security Information to open the window. For more information see Support Debugging Tool Build 10 released.
Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This model is defined in the following way:
Access to all windows, tables, reports and miscellaneous permissions are classed as Security Operations.
A set of Security Operations required to perform a specific task are assigned to a Security Task.
Multiple Security Tasks required to perform a specific role are assigned to a Security Role.
Each User and Company combination can then have multiple Security Roles assigned to it.
Note: Operations may be assigned to multiple Security Tasks and Security Tasks may be assigned to multiple Security Roles.
In the situation when a system administrator knows which window (or report) they wish to grant access to a user, but does not know what Security Tasks or Security Roles are associated with the window, there is no simple method to obtain this information from within the application. It would be possible to scroll through each Security Task on the Security Task Setup window (Microsoft Dynamics GP >> Tools >> Setup >> System >> Security Tasks) and check if the window is selected, but this is time consuming. The Print Operation Access report which can be printed after selecting the window will show which users have access to the window, but not how that access was obtained based on the Security Roles and Security Tasks.
To obtain the data we will use a new Security Resource Descriptions table (Technical Name: syCurrentResources (SY09400) table) which was added to v10.0 to create a SQL Query to obtain the information. This table is initially empty, but can be populated by running the Clear Data File Maintenance process on it. The system will then rebuild the contents based on the current installed dictionaries.
Below are the steps to populate the Security Resource Descriptions table:
- Click Microsoft Dynamics GP, point to Maintenance, and then click Clear Data to open the Clear Data window.
- On the Display menu, click Physical.
- In the Series list, click System.
- In the Tables pane, click the Security Resource Descriptions table, and then click Insert.
- Click OK.
- Click Yes.
- In the Report Destination window, select the Screen check box, and then click OK to send the report to the screen.
- Close the report.
SQL Query:
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 = '<Display_Name>'
Note: The <Display_Name> placeholder represents the actual display name. For example, the display name may be "Sales Transaction Entry".
Below are the example results based on a default installation for 'Sales Transaction Entry':
Result Set
SECURITYROLEID SECURITYROLENAME SECURITYTASKID SECURITYTASKNAME PRODNAME TYPESTR DSPLNAME RESTECHNAME DICTID SECRESTYPE SECURITYID
----------------------- ------------------------------- ----------------------- ----------------------- ----------------------- --------------- ------------------------------- --------------- ------- --------------- ----------
BOOKKEEPER* Bookkeeper TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
CUSTOMER SERVICE REP* Customer Service Representative TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
OPERATIONS MANAGER* Operations Manager TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
SHIPPING AND RECEIVING* Shipping and Receiving TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
If there are no Security Roles assigned to the Security Tasks, they will show as blank. If there are no Security Tasks assigned to the Operation, they will also show as blank.
Security Table Information
Security Operations for a Security Task are stored in table sySecurityAssignTaskOperations (SY10700).
Security Tasks are defined in table sySecurityMSTRTask (SY09000).
Security Tasks for a Security Role are stored in table sySecurityAssignTaskRole (SY10600).
Security Roles are defined in table sySecurityMSTRRole (SY09100).
Security Roles for a User and Company combination are stored in table sySecurityAssignUserRole (SY10500).Also see the following post for how to use the Support Debugging Tool for Microsoft Dynamics GP to achieve the same results:
How to identify the Security Tasks and Security Roles using the Support Debugging Tool
Edit: Build 10 of the Support Debugging Tool now includes a Security Information window which can be opened from the Security Profiler and Resource Information windows. This window will display the Security Tasks and Security Roles associated with the select resource and provide easy navigation to the security windows to make changes if desired. Just right click and select Security Information to open the window. For more information see Support Debugging Tool Build 10 released.
Recurring appointments without counting weekends in Outlook
So here's the deal with the code. You'll want to copy this into a standard module in Outlook, and update the following parts:
-The number of days you wish to increment your appointments goes with "lDaysToAdd". It's currently set to 5
-The last day you'd want an appointment to be created on goes in "dtMaxDay". Spell it out in full format. (ie "December 26, 2006")
-Update the Bank holiday string. Feel free to add as many as you want, but put them in order, and don't forget the "& sDlmtr & " part between each one.
Then save your project. If you've never run code from Outlook before, you may have to set your macro security warnings to a lower level, then exit and restart Outlook to allow the code to run.
Once you have the code in place, open the appointment you wish to copy, then run the code. Just as a note, it does not create them as official "Recurring Appointments", but rather as individual ones.
'--------------------------------------------------------------------------------------------------
Option Explicit
Sub RecurAppointment()
'Macro created 04/22/2006 21:45 by Ken Puls
'Macro Purpose: Copy a currently existing appointment to a certain number
' of days out, ignoring weekends and bank holidays
'
'NOTE: Don't forget to open the instance of the appointment you wish to
' increment before running this code!
Dim iInspct As Inspector
Dim oActiveAppoint As AppointmentItem
Dim oNewAppoint As AppointmentItem
Dim lDaysToAdd As Long
Dim lTempdays As Long
Dim lAryCount As Long
Dim dtCurrentDay As Date
Dim dtMaxDay As Date
Dim aryHolidays() As String
Dim sHolidays As String
Const sDlmtr = "/"
'Enter your repeat cycle, stop date here
lDaysToAdd = 5
dtMaxDay = "May 26, 2006"
'Set your bank holidays here. Don't forget the '& sDlmtr &' between each date,
'make sure to spell out your dates in long hand format, and ensure that all dates
'are in ASCENDING DATE ORDER!
sHolidays = _
"January 2, 2006" & sDlmtr & _
"April 14, 2006" & sDlmtr & _
"April 17 2006" & sDlmtr & _
"May 22, 2006" & sDlmtr & _
"July 3, 2006" & sDlmtr & _
"August 7, 2006" & sDlmtr & _
"September 4, 2006" & sDlmtr & _
"October 9, 2006" & sDlmtr & _
"November 13, 2006" & sDlmtr & _
"December 25, 2006" & sDlmtr & _
"December 26, 2006"
'Send any errors to the error handler to clean up before exit
On Error GoTo ErrHandler
'Split the bank holiday string above into an array for inspection later
aryHolidays = Split(sHolidays, sDlmtr)
'Bind to the active inspector window, or exit routine if an inspector
'window is not active
Set iInspct = ActiveInspector
If iInspct Is Nothing Then GoTo ErrHandler
'Lock in to active appointment and record the start date
Set oActiveAppoint = iInspct.CurrentItem
dtCurrentDay = oActiveAppoint.Start
'Create new appointments
Do Until dtCurrentDay > dtMaxDay
'Find the next day...
For lTempdays = 1 To lDaysToAdd
'Advance other days
Select Case Format((dtCurrentDay), "ddd", vbSunday)
Case Is = "Fri"
'Skip Sat & Sun and go to Monday
dtCurrentDay = dtCurrentDay + 3
Case Is = "Sat"
'This should never happen since Friday will advance
'past weekend days
dtCurrentDay = dtCurrentDay + 2
Case Is = "Sun"
'This should never happen since Friday will advance
'past weekend days
dtCurrentDay = dtCurrentDay + 1
Case Else
'Add one day to move to next day
dtCurrentDay = dtCurrentDay + 1
End Select
'Deal with bank holidays, advancing the date a day if required
For lAryCount = LBound(aryHolidays) To UBound(aryHolidays)
If InStr(1, aryHolidays(lAryCount), _
Format((dtCurrentDay), "mmmm dd, yyyy", vbSunday)) _
Then dtCurrentDay = dtCurrentDay + 1
Next lAryCount
Next lTempdays
'Check that day is not higher than max range
'(Int used to remove fractional days from appointment)
If Int(dtCurrentDay) > Int(dtMaxDay) Then Exit Do
'Create the new appointment
Set oNewAppoint = CreateItem(olAppointmentItem)
With oNewAppoint
.AllDayEvent = oActiveAppoint.AllDayEvent
.Body = oActiveAppoint.Body
.Subject = oActiveAppoint.Subject
.Start = dtCurrentDay
.End = dtCurrentDay + (oActiveAppoint.End - oActiveAppoint.Start)
.Save
End With
Loop
'Clean up and exit
ErrHandler:
Set iInspct = Nothing
End Sub
'-----------------------------------------------------------------------------
by Ken Puls, VBAX
-The number of days you wish to increment your appointments goes with "lDaysToAdd". It's currently set to 5
-The last day you'd want an appointment to be created on goes in "dtMaxDay". Spell it out in full format. (ie "December 26, 2006")
-Update the Bank holiday string. Feel free to add as many as you want, but put them in order, and don't forget the "& sDlmtr & " part between each one.
Then save your project. If you've never run code from Outlook before, you may have to set your macro security warnings to a lower level, then exit and restart Outlook to allow the code to run.
Once you have the code in place, open the appointment you wish to copy, then run the code. Just as a note, it does not create them as official "Recurring Appointments", but rather as individual ones.
'--------------------------------------------------------------------------------------------------
Option Explicit
Sub RecurAppointment()
'Macro created 04/22/2006 21:45 by Ken Puls
'Macro Purpose: Copy a currently existing appointment to a certain number
' of days out, ignoring weekends and bank holidays
'
'NOTE: Don't forget to open the instance of the appointment you wish to
' increment before running this code!
Dim iInspct As Inspector
Dim oActiveAppoint As AppointmentItem
Dim oNewAppoint As AppointmentItem
Dim lDaysToAdd As Long
Dim lTempdays As Long
Dim lAryCount As Long
Dim dtCurrentDay As Date
Dim dtMaxDay As Date
Dim aryHolidays() As String
Dim sHolidays As String
Const sDlmtr = "/"
'Enter your repeat cycle, stop date here
lDaysToAdd = 5
dtMaxDay = "May 26, 2006"
'Set your bank holidays here. Don't forget the '& sDlmtr &' between each date,
'make sure to spell out your dates in long hand format, and ensure that all dates
'are in ASCENDING DATE ORDER!
sHolidays = _
"January 2, 2006" & sDlmtr & _
"April 14, 2006" & sDlmtr & _
"April 17 2006" & sDlmtr & _
"May 22, 2006" & sDlmtr & _
"July 3, 2006" & sDlmtr & _
"August 7, 2006" & sDlmtr & _
"September 4, 2006" & sDlmtr & _
"October 9, 2006" & sDlmtr & _
"November 13, 2006" & sDlmtr & _
"December 25, 2006" & sDlmtr & _
"December 26, 2006"
'Send any errors to the error handler to clean up before exit
On Error GoTo ErrHandler
'Split the bank holiday string above into an array for inspection later
aryHolidays = Split(sHolidays, sDlmtr)
'Bind to the active inspector window, or exit routine if an inspector
'window is not active
Set iInspct = ActiveInspector
If iInspct Is Nothing Then GoTo ErrHandler
'Lock in to active appointment and record the start date
Set oActiveAppoint = iInspct.CurrentItem
dtCurrentDay = oActiveAppoint.Start
'Create new appointments
Do Until dtCurrentDay > dtMaxDay
'Find the next day...
For lTempdays = 1 To lDaysToAdd
'Advance other days
Select Case Format((dtCurrentDay), "ddd", vbSunday)
Case Is = "Fri"
'Skip Sat & Sun and go to Monday
dtCurrentDay = dtCurrentDay + 3
Case Is = "Sat"
'This should never happen since Friday will advance
'past weekend days
dtCurrentDay = dtCurrentDay + 2
Case Is = "Sun"
'This should never happen since Friday will advance
'past weekend days
dtCurrentDay = dtCurrentDay + 1
Case Else
'Add one day to move to next day
dtCurrentDay = dtCurrentDay + 1
End Select
'Deal with bank holidays, advancing the date a day if required
For lAryCount = LBound(aryHolidays) To UBound(aryHolidays)
If InStr(1, aryHolidays(lAryCount), _
Format((dtCurrentDay), "mmmm dd, yyyy", vbSunday)) _
Then dtCurrentDay = dtCurrentDay + 1
Next lAryCount
Next lTempdays
'Check that day is not higher than max range
'(Int used to remove fractional days from appointment)
If Int(dtCurrentDay) > Int(dtMaxDay) Then Exit Do
'Create the new appointment
Set oNewAppoint = CreateItem(olAppointmentItem)
With oNewAppoint
.AllDayEvent = oActiveAppoint.AllDayEvent
.Body = oActiveAppoint.Body
.Subject = oActiveAppoint.Subject
.Start = dtCurrentDay
.End = dtCurrentDay + (oActiveAppoint.End - oActiveAppoint.Start)
.Save
End With
Loop
'Clean up and exit
ErrHandler:
Set iInspct = Nothing
End Sub
'-----------------------------------------------------------------------------
by Ken Puls, VBAX
How to identify the Security Tasks and Security Roles associated with a specific window or report in Microsoft Dynamics GP
by David Musgrave
Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This model is defined in the following way:
In the situation when a system administrator knows which window (or report) they wish to grant access to a user, but does not know what Security Tasks or Security Roles are associated with the window, there is no simple method to obtain this information from within the application. It would be possible to scroll through each Security Task on the Security Task Setup window (Microsoft Dynamics GP >> Tools >> Setup >> System >> Security Tasks) and check if the window is selected, but this is time consuming. The Print Operation Access report which can be printed after selecting the window will show which users have access to the window, but not how that access was obtained based on the Security Roles and Security Tasks.
To obtain the data we will use a new Security Resource Descriptions table (Technical Name: syCurrentResources (SY09400) table) which was added to v10.0 to create a SQL Query to obtain the information. This table is initially empty, but can be populated by running the Clear Data File Maintenance process on it. The system will then rebuild the contents based on the current installed dictionaries.
Below are the steps to populate the Security Resource Descriptions table:
SQL Query:
SELECT ISNULL(A.SECURITYROLEID,'') AS SECURITYROLEID, ISNULL(M.SECURITYROLENAME,'') AS SECURITYROLENAME, --ISNULL(M.SECURITYROLEDESC,'') AS SECURITYROLEDESC,
Note: The <Display_Name> placeholder represents the actual display name. For example, the display name may be "Sales Transaction Entry".
Below are the example results based on a default installation for 'Sales Transaction Entry':
Result Set:
SECURITYROLEID SECURITYROLENAME SECURITYTASKID SECURITYTASKNAME PRODNAME TYPESTR DSPLNAME RESTECHNAME DICTID SECRESTYPE SECURITYID
If there are no Security Roles assigned to the Security Tasks, they will show as blank. If there are no Security Tasks assigned to the Operation, they will also show as blank.
Security Table Information
Security Operations for a Security Task are stored in table sySecurityAssignTaskOperations (SY10700).
Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This model is defined in the following way:
- Access to all windows, tables, reports and miscellaneous permissions are classed as Security Operations.
- A set of Security Operations required to perform a specific task are assigned to a Security Task.
- Multiple Security Tasks required to perform a specific role are assigned to a Security Role.
- Each User and Company combination can then have multiple Security Roles assigned to it.
Note: Operations may be assigned to multiple Security Tasks and Security Tasks may be assigned to multiple Security Roles.
To obtain the data we will use a new Security Resource Descriptions table (Technical Name: syCurrentResources (SY09400) table) which was added to v10.0 to create a SQL Query to obtain the information. This table is initially empty, but can be populated by running the Clear Data File Maintenance process on it. The system will then rebuild the contents based on the current installed dictionaries.
Below are the steps to populate the Security Resource Descriptions table:
- Click Microsoft Dynamics GP, point to Maintenance, and then click Clear Data to open the Clear Data window.
- On the Display menu, click Physical.
- In the Series list, click System.
- In the Tables pane, click the Security Resource Descriptions table, and then click Insert.
- Click OK.
- Click Yes.
- In the Report Destination window, select the Screen check box, and then click OK to send the report to the screen.
- Close the report.
Now that the Security Resource Descriptions table has been populated we can use it in a SQL Query from SQL Query Analyzer (SQL Server 2000) or SQL Server Management (SQL Server 2005). The Query below will display the Security Roles and Security Tasks associated with a specific window or report as selected by changing the Display Name on the last line of the query.
SQL Query:
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 = '<Display_Name>'
Note: The <Display_Name> placeholder represents the actual display name. For example, the display name may be "Sales Transaction Entry".
Below are the example results based on a default installation for 'Sales Transaction Entry':
Result Set:
SECURITYROLEID SECURITYROLENAME SECURITYTASKID SECURITYTASKNAME PRODNAME TYPESTR DSPLNAME RESTECHNAME DICTID SECRESTYPE SECURITYID
----------------------- ------------------------------- ----------------------- ----------------------- ----------------------- --------------- ------------------------------- --------------- ------- --------------- ----------
BOOKKEEPER* Bookkeeper TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
CUSTOMER SERVICE REP* Customer Service Representative TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
OPERATIONS MANAGER* Operations Manager TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
SHIPPING AND RECEIVING* Shipping and Receiving TRX_SALES_001* Enter SOP transactions Microsoft Dynamics GP Windows Sales Transaction Entry SOP_Entry 0 2 619
If there are no Security Roles assigned to the Security Tasks, they will show as blank. If there are no Security Tasks assigned to the Operation, they will also show as blank.
Security Table Information
Security Operations for a Security Task are stored in table sySecurityAssignTaskOperations (SY10700).
Security Tasks are defined in table sySecurityMSTRTask (SY09000).
Security Tasks for a Security Role are stored in table sySecurityAssignTaskRole (SY10600).
Security Roles are defined in table sySecurityMSTRRole (SY09100).
Security Roles for a User and Company combination are stored in table sySecurityAssignUserRole (SY10500).
Removing Internal Excel passwords
' DISCLAIMER: Please note that breaking password protection MAY violate laws or regulations.
' --------------------------------------------------------------------------------------------------------------------
' by McGimpsey & Associates
Public Sub AllInternalPasswords()
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"Adapted from Bob McCormick base code by" & _
"Norman Harker and JE McGimpsey"
Const HEADER As String = "AllInternalPasswords User Message"
Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
Const REPBACK As String = DBLSPACE & "Please report failure " & _
"to the microsoft.public.excel.programming newsgroup."
Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
"now be free of all password protection, so make sure you:" & _
DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
"may be an offense. If in doubt, don't."
Const MSGNOPWORDS1 As String = "There were no passwords on " & _
"sheets, or workbook structure or windows." & AUTHORS & VERSION
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _
"Proceeding to unprotect sheets." & AUTHORS & VERSION
Const MSGTAKETIME As String = "After pressing OK button this " & _
"will take some time." & DBLSPACE & "Amount of time " & _
"depends on how many different passwords, the " & _
"passwords, and your computer's specification." & DBLSPACE & _
"Just be patient! Make me a coffee!" & AUTHORS & VERSION
Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
"Structure or Windows Password set." & DBLSPACE & _
"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
"Note it down for potential future use in other workbooks by " & _
"the same person who set this password." & DBLSPACE & _
"Now to check and clear other passwords." & AUTHORS & VERSION
Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
"password set." & DBLSPACE & "The password found was: " & _
DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
"future use in other workbooks by same person who " & _
"set this password." & DBLSPACE & "Now to check and clear " & _
"other passwords." & AUTHORS & VERSION
Const MSGONLYONE As String = "Only structure / windows " & _
"protected with the password that was just found." & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean
Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
With ActiveWorkbook
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub
' --------------------------------------------------------------------------------------------------------------------
' by McGimpsey & Associates
Public Sub AllInternalPasswords()
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"Adapted from Bob McCormick base code by" & _
"Norman Harker and JE McGimpsey"
Const HEADER As String = "AllInternalPasswords User Message"
Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
Const REPBACK As String = DBLSPACE & "Please report failure " & _
"to the microsoft.public.excel.programming newsgroup."
Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
"now be free of all password protection, so make sure you:" & _
DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
"may be an offense. If in doubt, don't."
Const MSGNOPWORDS1 As String = "There were no passwords on " & _
"sheets, or workbook structure or windows." & AUTHORS & VERSION
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _
"Proceeding to unprotect sheets." & AUTHORS & VERSION
Const MSGTAKETIME As String = "After pressing OK button this " & _
"will take some time." & DBLSPACE & "Amount of time " & _
"depends on how many different passwords, the " & _
"passwords, and your computer's specification." & DBLSPACE & _
"Just be patient! Make me a coffee!" & AUTHORS & VERSION
Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
"Structure or Windows Password set." & DBLSPACE & _
"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
"Note it down for potential future use in other workbooks by " & _
"the same person who set this password." & DBLSPACE & _
"Now to check and clear other passwords." & AUTHORS & VERSION
Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
"password set." & DBLSPACE & "The password found was: " & _
DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
"future use in other workbooks by same person who " & _
"set this password." & DBLSPACE & "Now to check and clear " & _
"other passwords." & AUTHORS & VERSION
Const MSGONLYONE As String = "Only structure / windows " & _
"protected with the password that was just found." & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean
Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
With ActiveWorkbook
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub
Subscribe to:
Posts (Atom)