Sunday, July 6, 2008

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/

3 comments:

MikeLupro said...

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 Lupro
mikelupro@gobtnw.com

Mariano Gomez said...

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 IDs
delete from sy08130 where userid in ('userid1', 'userid2', 'useridn');

-- insert the new metrics settings based on the template users
with 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, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Ron Brown said...

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