Checking SQL permissions on many SQL Server objects

By Steve Endow

I am working with a Dynamics GP client that is having an odd issue with an ISV solution related to SQL server permissions.  The ISV solution is designed to only allow one Dynamics GP login to access its tables and stored procedures--so that only one instance of the application is running at a time.

Normally this works fine and I've worked with dozens of clients without issue.  But one client is having an issue where the application is reporting that a second user has activated and de-activated the application.

We've checked the settings and checked the SQL Server database role used by the application and everything appears to be setup correctly.  But before I contacted the developer, I wanted to check one last thing--the permission on the individual SQL objects.  Even though the application uses a database role to manage its permissions, we have seen at least one third party GP product perform permission updates on every object in the GP databases, causing problems with this application.

So how do you check the permissions on a SQL object?  If you only need to check one or two objects, you can use SQL Server Management Studio.  Just right click on an object, such as a table, and select Properties.  Then click on the Permissions page.

Here is an example of a table used by Post Master for Dynamics GP.  It does not use the DYNGRP role, and you can see that only the rPostMaster database role is assigned to the table.

This approach of checking permissions works, but it is very tedious to do this for multiple objects.  I needed the GP client to check permissions on dozens of tables and stored procedures, and I didn't want to have to send instructions and a list of every object.

So after some Googling, I found some SQL queries that allow you to query the permissions on ranges of objects based on name.

So for tables, you can run this query:

sp_table_privileges 'ESS%'

This displays all of the permissions for tables that begin with ESS, which are the tables used by Post Master.

For stored procedures, it is a little bit more involved, but fortunately someone on Stack Overflow had posted the script:

SELECT OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
FROM sys.database_permissions p
WHERE OBJECT_NAME(major_id) LIKE 'zDP_ESS%'


You can modify the queries to look for objects starting with RM, PM, SOP, GL, etc., or all objects.

When you run these queries you get a nice list of all permissions that you can quickly scan to identify anomalies.

I was able to send both queries to the client, and a few minutes later, I had the results and was able to verify the permissions on all Post Master database objects.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

NetSuite vs. Dynamics GP: Navigation: Browser Tabs vs. Application Windows

By Steve Endow

For background, please see my introduction to this series, NetSuite vs. Dynamics GP: A Series

If you are familiar with the Dynamics GP client application, you know that you can navigate throughout it much like any other Windows application.  You have the main application window, and you use menus or shortcuts or other "navigation" methods within the application to open new windows and sub-windows or child windows.

Here I have four different Dynamics GP client application windows open.

In Dynamics GP, it is possible to open a bunch of different windows as you perform different tasks, but in my experience, most users tend to have a handful of windows that they work with regularly.  If you have more than 2 or 3 windows open at a time, it usually gets confusing and switching between them becomes tedious.  Some users keep a few of those key windows open all the time, while others, myself included, like to use one window at a time and close extra windows as soon as they are done with them, always keeping the windows to a minimum.

In NetSuite, things are a little different.  First, NetSuite is a native web application, so you only access it with a web browser.  You can use a single web browser tab, and always navigate within that one window, or you can right click on menu items or application "links" and select Open in New Tab.  Just as this feature is very handy when doing normal web browser, the Open in New Tab feature is very convenient within NetSuite.  If you are working on a transaction and want to open another window to lookup a value or perform some other action, it's simple to do in a new browser window, not too different than opening another window in Dynamics GP.

Here I have six tabs open, each with a different NetSuite "window".

What is different about the NetSuite browser tabs is that if you start clicking on links in those tabs, you travel down a path and can go just about anywhere within the NetSuite application.  So the tab that was a customer list now displays a customer invoice.  Or the tab that displayed a financial report now shows a journal entry.  When I have multiple tabs open and start clicking links in each of them, I quickly got lost in the different tabs.  I often click a link rather than right click, and before I know it, my tabs have completely different content than a minute ago.  Eventually, I have to close several tabs and start over, resetting the tabs to specific windows.

After using NetSuite for a while, I found that the navigation model between the Dynamics GP client application and the NetSuite browser-based navigation each had pros and cons.  In Dynamics GP, when you open windows, you eventually reach a dead end.  For instance, you might open a Sales Transaction window, then open the Customer window, then open the Customer Accounts window.  All of those windows stay in place until you close them, and eventually you reach an end point.  And you can only open one Customer window at a time--so if you try and open it again, it pops to the foreground.  This simplifies the user experience somewhat, since each window has a single purpose and unique name, so you can have multiple windows open and pretty easily know which is which.

With NetSuite, you have the nice feature of being able to right click on a menu item or link and open it in a new browser tab.  You can have multiple customers open in multiple tabs, or multiple invoices, or multiple reports or inquiries.  The navigation is very flexible and fluid, allowing you to go wherever you want from anywhere.  This is pretty appealing, but as I noted, you can quickly get lost in a pile of browser tabs.

And one downside of the NetSuite browser based approach.  If you encounter an error when submitting information, it tends to mess up the application "state" in that window.  So if you attempt to click on the Back button on the error message web page, or use your browser's back button, the page may not be current or valid, and may not work properly until you refresh or navigate to a new page.  When I was testing and debugging a custom SuiteScript solution and encountering errors, such "back" problems became very frustrating, since it required me to navigate back a few pages and start my testing over earlier in the process than I would have liked.  But, despite this, there was the plus that I can login to NetSuite from any computer with a web browser.

Obviously, GP 2013 now offers a web-based client, so for now I have only compared the original GP client application to the NetSuite browser based solution--obviously not a perfect comparison.  I haven't yet set it up on my test servers, so I'll have to revisit this navigation comparison once I get the GP web client setup.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Importing Payroll Posting Accounts

Payroll posting accounts can be fairly straightforward, but for some users the combination of code/department/position can create a seemingly neverending matrix of possibilities- particularly when factoring in the use of ALL for some of the components. Although the payroll posting account setup window is not terribly difficult to use, it is a little unwieldy when trying to enter and proof a long list of variations. Table import can help with this, by allowing users to enter and review the information in Excel.

Now, I am not going to go in to detail about using table import, Tools-Import-Table Import, except to say that you should always test in a test database first. In the case of importing payroll posting accounts, I would go so far as to build (which should flush out any true errors), calculate, print, and post a payroll (which will flush out anything entered incorrectly).

So, to import payroll posting accounts, you will be importing to the Payroll Accounts Setup table (UPR40500). And you will need the following fields in your upload file (which can be in Excel, and then saved as a text tab-delimited or CSV file).

1. Department (Must match department code exactly in GP)
2. Job Title (Must match position code exactly in GP)

And then we have the three more complicated ones...

3. Payroll Code In a lot of cases, this would be the Pay Code, Deduction Code, or Benefit Code (based on the UPR transaction type which is explained next).

But in some cases, this would also be...
Tax Code (State)- When used with the transaction type, State Tax Withholding, SUTA Payable or FUTA Payable.
EFIC/M, EFIC/S, FED, FICA/M, FICA/S - When used with Federal Tax Withholding (the codes that start with E are the employer side)
FIC/ME, FIC/SE, FUTA, SUTA - When used with Employer Tax Expense.

So that brings us to..
4. UPR Transaction Type This has different values that correspond to the dropdown list in the Payroll Posting Accounts Setup window. You will use the number noted in your file.

Gross Pay (DR)- 1
Federal Tax Withholding (CR)- 2
State Tax Withholding (CR)- 3
Local Tax Withholding (CR)- 4
Deduction Withholding (CR)- 5
Employer's Tax Expense (DR)- 6
Benefits Expense (DR)- 7
Benefits Payable (CR)- 8
Taxable Benefits Expense (DR)- 9
Taxable Benefits Payable (CR)- 10
SUTA Payable (CR)- 11
FUTA Payable (DR)- 12
Workers Comp Tax Expense (DR)- 13
Workers Comp Tax Payable (CR)- 14

The last field in the file would be...

5. Account Index. This is easily retrieved from the GL00100 or GL00105 table, or you can just add Account Index to the Accounts Smartlist.

It is important to note this is NOT the account number, but the index that is a incremental unique number assigned to each account in the database. When in doubt, go set a few up manually and then do a select * from UPR40500 to check out the values and how they populate. This can go a long way to making your table import successful.

One additional side note, make sure you include all of the required payroll posting account combinations in your upload (refer to help from the Payroll Posting Accounts Setup window for a list of them).

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Two potential Dynamics GP design limitations for larger or growing companies

By Steve Endow

I recently spoke with a Lawson consulting firm.  One of their Lawson customers acquired a company that uses Microsoft Dynamics GP.  The Lawson client manages 3 companies in their system, while the GP client manages 9 companies.

In Lawson, the 3 companies are managed in a single database.  But in Dynamics GP, the 9 companies are managed in 9 separate company databases.  The client was debating whether to continue to use Lawson, or to transition to Dynamics GP.  The Lawson partner's concern was whether the client would be willing to transition to a system that kept the companies in separate databases and required them to switch companies all the time.

Obviously, there are thousands of Dynamics GP customers that have multiple company databases and manage them without issue.  And I know of several companies that have over 200 active company databases in Dynamics GP.  I am not sure how they handle that many companies and retain their sanity, but they apparently manage.

Anyway, the Dynamics GP design where each company uses a separate SQL Server database is often considered a limitation or weakness for multi-company entities that are evaluating ERP systems.  Some companies don't mind multiple databases, whereas others consider it a significant weakness and hindrance, perhaps due to administration, reporting, data entry, etc.

Sheldon Gitzel recently discussed this issue and a few possible solutions in a blog post on the Etelligent ERP Blog.

So if any GP competitor claims that GP can't effectively manage multiple companies, such a claim is clearly not true--the solution is just different than some larger ERP systems.  Systems that manage multiple companies in the same database may have some advantages, but such a design also has potential disadvantages, such as having to deal with a single very large database.

So that is one potential concern for a large company.

A second Dynamics GP design limitation is lack of global support for "effective dates".  If an employee's pay or benefits or deductions are going to change next month, there is no option to enter those changes in Dynamics GP with a future effective date.  Many "higher end" systems have native global support for effective dating.  With effective dating, an inventory item can be discontinued on a future date, future pay raises can be entered and put into effect automatically, or special prices and discounts can be date-based.  I have only worked with one GP customer that really needed effective dating--they had hundreds of employees and effective dating was critical for their HR and Payroll processes--but as companies grow, such a feature often becomes more important.  If anyone knows of an add-on that allows Dynamics GP to have effective dating, please post a comment and let me know.

Related to the lack of effective dating is that record changes are not tracked in Dynamics GP.  So if an employee or customer or vendor address record is updated, there is no record of the prior value--only the current value is saved.  The customer could purchase an add-on such as Rockton Auditor to track the changes made in GP, but that is an additional $250 per concurrent user and still doesn't provide effective dating.

This isn't intended to be a criticism of Dynamics GP, as these features are typically fundamental design choices and add complexity.  I just wanted to explore two general areas where larger companies might indicate that they need a "larger" or "tier 1" ERP system and claim that Dynamics GP doesn't meet their needs.

Have you observed any situations where a customer felt they couldn't use Dynamics GP because it didn't have a feature of a "larger" ERP system?

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

eConnectException: The stored procedure 'ta______' doesn't exist

By Steve Endow

Tonight a customer e-mailed me saying he was receiving this eConnect error message:

eConnectException: The stored procedure 'taPMManualCheck' doesn't exist

He was trying to use my AP Payment and Apply Import utility to bring in historical AP payments and applications, but the eConnect error occurred as soon as the import attempted to submit the transaction to eConnect.

We verified that the taPM stored procedures existed, and then we verified that the import was sending the data to the correct database name.

We then used SQL Profiler to trace the database activity, and saw that other queries were being successfully executed, but the taPMManualCheck procedure never got called.

I then checked the eConnect service, and it was assigned to a domain user.  We then opened SQL Server Management Studio and checked the permissions for that domain user.

It was a member of DYNGRP in the DYNAMICS database and TWO database, but lo and behold, it was not a member of DYNGRP in the company database for some reason.

Once we checked the box for the DYNGRP role in the company database for the eConnect domain user, the import worked fine.

A similar issue can occur if the eConnect user is using a different schema, as Patrick Roth discusses in this post on the Developing for Dynamics GP blog.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Undocumented eConnect 2010 and 2013 Serialization Flags

By Steve Endow

I just fielded an eConnect question on the incredibly busy Microsoft Dynamics GP Community Forum about an issue with an eConnect vendor import.

The user was trying to set the Vendor Status field on imported vendors, but was unable to set the Status to Temporary.

I opened a .NET eConnect project and checked the properties of taUpdateCreateVendor object and I quickly found a likely suspect for the problem.

In eConnect 2010, the GP documentation and help team did a great job of restructuring the eConnect Help file to make it much easier to navigate.  However, for some reason, they also eliminated one small part of the help file:  the lists of the "serialization flags".

If you open the eConnect 10 help file and pull up the taUpdateCreateVendor record, you will see that some fields have little cross symbols next to them.  Those marks indicate that those fields have separate serialization flags.

At the bottom of the eConnect help page, there was a list of all of the serialization flags.

Once you knew about the serialization flags and knew to be on the lookout for them, this information wasn't terribly important.  Which might be why the serialization flag list was dropped in the eConnect 2010 help file.  But for those poor souls who were creating their first eConnect project in GP 2010 or 2013, this detail would not be at all obvious.

So what is a serialization flag and how to you use it?

I don't know the back story for why serialization flags were used in eConnect, but my guess is that it was to help reduce the complexity of the eConnect serialization code.  By having flags to indicate which less-common fields were being sent in, the code didn't have to evaluate certain values and could skip certain chunks of serialization by default.

In short, it's just a boolean property that you must include if you are setting the value of a corresponding eConnect field.

In this example, if you send in a value for Vendor Status, you must also send in the corresponding vendor status serialization flag.

With the serialization flag set to true, eConnect serialization will include the VNDSTTS field value in the XML that it generates.

Whenever you develop eConnect integrations, you need to pay attention to the intellisense to see if a property has a corresponding "Specified" property.  Since those properties are no longer indicated or listed in the eConnect help file, Intellisense is the only way that I know of to be aware of such properties in eConnect 2010 and 2013.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com