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/