GP User Security Report

I received a request from a client who needed to supply a GP User Security Report to their auditors. Prior to GP version 10, we were at the mercy of the GP Report Writer report that was typically several thousands to several tens of thousands of pages long. We would usually modify the report to print one line per record to save space and so that it was easily exported, but even that produced a very large file. Now with the new Role-based Security in GP 10 and 2010, we have access to this data in SQL. I developed the script below and thought that others in the GP community might find it useful. It provides a nice, simple view of the user security in GP.  It does not drill down to the specific window level, but it has every user, the companies they access, the roles they are assigned to in each company and the task and task descriptions within each role.  Pretty sweet.  I hope you guys can use it someday.

The script:

Use dynamics

select rol.userid, cmp.interid, rol.securityroleid, tsk.securitytaskid, td.securitytaskname, td.securitytaskdesc

from SY10500 rol 

                inner join SY01500 cmp on rol.cmpanyid=cmp.cmpanyid

                inner join SY10600 tsk on rol.securityroleid=tsk.securityroleid

                inner join SY09000 td on tsk.securitytaskid=td.securitytaskid

order by rol.userid, cmp.interid, rol.securityroleid, tsk.securitytaskid


A sample result set:

Tonya Boyce

ACE Microtechnology, Inc.



Microsoft Dynamics CRM 2011 Online Client Case Study


United States
Commercial Furniture Sales

Customer Profile
Corporate Environments (CE) employs more than 40 professionals to serve the office design and furnishing needs of their clients. Headquartered in Atlanta and serving the greater southeast, CE represents furniture lines including Knoll office furniture and DIRTT architectural products.

Business Situation
CE lacked a single repository for customer information. With the majority of the staff interacting with clients on a regular basis, it was critical for them to have quick and easy access to sales and support information to better serve their existing customers and attract new ones.

CE chose Microsoft Dynamics CRM 2011 Online and partnered with ACE Microtechnology to deploy the solution and integrate it to their industry specific back office for greater information access and visibility.


  • Improved Visibility of customer and prospects
  • Improved cash flow
  • Real-time forecasting
  • Workflows and Notifications
    • New account created
    • Lead assignment
    • Sales person notification
    • Client deposit notification

Microsoft Dynamics CRM 2011 Online
Client Case Study

Regional Office Furniture and Architectural Products Dealership deploys cloud based CRM to provide 360 degree view of clients and prospects!

“The ability to meet with my sales team individually and see their sales pipeline in real-time is tremendous.  It gives me a tool to demonstrate where and how they should be spending their time.  Immediate access to close dates and install dates also allows me to provide quick answers to the company owner.”        Tony Nelson
Director of Sales

Operating in the Atlanta area since 1986, Corporate Environments has the experience necessary to assist with all office design needs, from furniture to space planning to relocation and installation. As the only authorized Knoll dealer in the area, Corporate Environments customers are assured that they will be receiving only the most stylish, well-made office furniture on the market. Furniture carried by Corporate Environments includes modular office furniture, cubicles, office chairs, conference furniture, reception furniture, 3form products, healthcare furniture, executive office furniture and filing cabinets. Corporate Environments had no integrated corporate sales tools. Sales representatives used their own individual tools to track accounts, contacts and activities. Opportunities were manually entered into spreadsheets for forecasting. No integrated marketing or customer service systems existed either. Because all of the staff of Corporate Environments interacts with and focuses on the customer, the company was exploring the acquisition of a fully integrated sales, marketing and customer service system. As a Microsoft Gold and Silver certified partner specializing in Microsoft Dynamics GP and CRM, ACE was uniquely positioned to deploy a solution that met the needs of Corporate Environments today. This solution leveraged existing technology investments and will scale with future company growth.

“The Dynamics CRM has allowed me to produce a real-time forecast and customized dashboard that is available at any given moment.   Through the use of this system, we have been able to automate and streamline existing processes resulting in more time for value-added activities.”

    Bridgette Boucha

Solutions deployed

Click on the products below for more information.

Microsoft Dynamics CRM
Office 365


Corporate Environments had no single repository for corporate sales and marketing. Sales information was spread throughout the organization with individual employees. The company was using manual processes and multiple tools for sales forecasting. Only a limited history of sales activities was available and the company had no understanding of why sales were lost. Corporate Environment spent a tremendous amount of time on networking but truly had no clear vision on where leads were coming from and where their networking time was best spent. Reporting and Business Intelligence was either very hard to produce, inaccurate or just not available. In order to achieve their aggressive growth goals, this lack of critical knowledge needed to change.


The solution included Microsoft Dynamics CRM 2011 online integrated to Team Design™ using the Scribe™ integration tools set. The results provided Corporate Environments with the following:

Single Customer List

  • A single customer repository accessible by all employees and identical in Microsoft Dynamics CRM and Team Design. Team Design is the industry specific back office accounting and customer/project solution.
  • Integrating with Team Design achieved a common customer and vendor database and additional information for real-time reporting and automated workflows.

Improved Productivity

  • Automation of manual processes (Forecast, Leads Assignment, New Acct Creation)
  • Accessible from anywhere for mobile workforce
  • Familiar interface – Outlook

Retain Customer Knowledge

  • Retain customer info lost with staff turnover
  • Customer information shared across departments

Market Focus

  • Who are our customers right now? What vertical markets and what mix?
  • Ability to change if this mix is weighted unexpectedly or inefficiently

Sales Metric Visibility

  • Sales activity
  • Sales productivity – new customers versus existing customers
  • Close rate on proposals
  • Jobs lost
  • Success rate versus competition

“Using a hosted-solution for Dynamics CRM combined with hosted email was a no-brainer. Instead of maintaining extra servers and software updates, we can use our limited resources to focus on projects that contribute more to the business. ”

Scott Blanck
Director of IT

About ACE Microtechnology

ACE Microtechnology, Inc. is a professional services firm committed to delivering business solutions to small and medium sized organizations. Our focus is in delivering products and services that improve our clients’ business operations. ACE has developed specialties in serving the distribution, retail and hospitality industries.

Rather than just building technology or implementing software, we deliver real business value through industry knowledge and understanding each client business. We make it our business to know their business.

ACE is a Microsoft Gold Certified Dynamics GP partner and Silver Certified Dynamics CRM partner.

For more information on ACE, please visit our web site at or contact Mark Munson at



  • AR in CRM
    • Improved cash flow
  • Real-time forecasting
    • Less work for the Accounting Department


  • New Customer
    • Entered into Team Design with approval workflow
    • Team Design assigns account numbers- sales notified new account created
  • New Leads
    • Sales Manager enters lead and assigns
    • Sales person notified
    • Reminders and escalation if no activity performed
  • Automated emails when client deposit made

IT Savings

  • Deploying CRM in the cloud was cost effective and easy
  • Based on this, the company also move email to the cloud with Microsoft Office 365

Lessons Learned

  • Phased deployment based on priority
  • Start at the beginning of the sales cycle
    • Did not stress sales out
    • Continue to use current back end process until they can be improved or replaced
  • Involve team early
  • Trained heavily
  • Integration
    • Don’t underestimate integration
    • Integration tool selection critical
    • Exponential benefit based on information sharing
  • Sales embraced better than anticipated
  • Cloud solution requires appropriate network and internet connectivity