Restricting Access to Metrics on Microsoft Dynamics GP 10 Homepage

When it comes to securing metrics on GP 10’s homepage, very little can be found outside of KB articles 918313 and 914898. But a few of us have decided to put this issue to rest — I say a few of us because this ‘how to’ guide could not be possible without the assistance of FlieHigh and Tim Foster of Trudell Medical Limited in London, Ontario, Canada, both regular contributors in the Microsoft Dynamics GP community — by combining our different approaches on the subject.

Lets get started! There are two approaches to lock down the metrics for a particular user in GP.

Method 1: T-SQL with SQL Security

1) Open Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). Execute the following statement against the DYNAMICS database.


UPDATE SY08100 set Visible = 0
WHERE(DictID = 0) and (SectionID = 3) and (UserID = 'userid')

The following is a list of Section ID values:

1 — To Do’s
2 — Outlook
3 — Metrics
4 — My Reports
5 — Quick Links

2) Open Enterprise Manager (SQL 2000) or SQL Server Management Studio (SQL 2005). Open the Tables folder of the DYNAMICS database and locate the SY08100 (syHomePageLayout) table. Click on Permissions.

3) In Enterprise Manager, hightlight the DYNGRP and click on the Columns button. Double-click on the Visible column under the Update heading to make sure that this option is set to Deny.

In SQL Server Management Studio, highlight DYNGRP in the Permissions window, select Update from the Explicit Permissions window, and click on the Column Permissions… button. Highlight the Visible field and change click on the checkmark under the Deny column

Method 2: SQL Server Trigger

Yes, folks! Thanks to Tim Foster you can now deny access to changing the visibility of the metrics section with a trigger as follows:


USE [DYNAMICS]
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tim Foster
-- Create date: 2008-07-04
-- Description: Prevent users from adding Metrics content on the Home Page
-- =============================================
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[dbo.X_SY8100_Prevent_Metrics]'))
DROP TRIGGER [dbo].[dbo.X_SY8100_Prevent_Metrics]
GO

CREATE TRIGGER [dbo.X_SY8100_Prevent_Metrics] ON dbo.SY08100 AFTER INSERT,UPDATE
AS
BEGIN

print 'X_SY8100_Prevent_Metrics trigger active'
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

IF USER_NAME()'dbo'
BEGIN
DECLARE @SECTIONID INTEGER, @VISIBLE INTEGER

SELECT @SECTIONID = SECTIONID, @VISIBLE = VISIBLE FROM INSERTED
IF @SECTIONID = 3 AND UPDATE(VISIBLE)
BEGIN
UPDATE dbo.SY08100 SET dbo.SY08100.VISIBLE = 0
FROM dbo.SY08100 INNER JOIN INSERTED ON dbo.SY08100.USERID = INSERTED.USERID
AND dbo.SY08100.SECTIONID = INSERTED.SECTIONID
AND dbo.SY08100.DICTID = INSERTED.DICTID
WHERE dbo.SY08100.USERID = INSERTED.USERID
AND dbo.SY08100.SECTIONID = INSERTED.SECTIONID
AND dbo.SY08100.DICTID = INSERTED.DICTID

PRINT 'You are not allowed to turn that content on'
END
END

PRINT 'X_SY8100_Prevent_Metrics trigger Complete'
END
GO

Maintaining metrics and other homepage elements safe is key to information security. Hope these two approaches ease the pain of dealing with metrics.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

About these ads

3 Responses to Restricting Access to Metrics on Microsoft Dynamics GP 10 Homepage

  1. MikeLupro says:

    I’m not that well versed in SQL code or triggers to understand exactly what this code does but we do have a situation where the client wants ‘some’ metrics available (Like RM and PM aging) but not other metrics (like Gross Profit graphs).Is this ‘selective turn-off’ possible using either or both of these methods?Mike Lupromikelupro@gobtnw.com

  2. Mariano Gomez says:

    Mike,Thanks for your inquiry. Metrics are stored in the syHomePageMetrics table (dbo.SY08130) in the Dynamics database. Your client could create a ‘template’ user ID that gets assigned the metrics he wants, then use the following query to rolldown to the users he wants to, as follows:– clear any previous metric settings for the users you are interested in assigning the template — * replace userid1, userid2,.., useridn for the actual user IDsdelete from sy08130 where userid in (‘userid1′, ‘userid2′, ‘useridn’);– insert the new metrics settings based on the template userswith TemplateUserMetrics (userid, seqnumbr, dictid, metricid) as ( select userid, seqnumbr, dictid, metricid from sy08130 where userid = ‘template’)insert sy08130 (userid, seqnumbr, dicid, metricid) select userid, seqnumbr, dicid, metricid from TemplateUserMetrics;You will want to test this query in a test environment first.MG.-Mariano Gomez, MVPMaximum Global Business, LLChttp://www.maximumglobalbusiness.com

  3. Ron Brown says:

    the new Connect section in GP 2010 is SectionID = 6.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: