Security Views For Use In SmartList Designer: Group Based Company Access In Management Reporter

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This, the second Management Reporter security script, shows security for users as granted by their Group membership. the previous post, on Friday, showed the user based company access.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterGroupBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
		,['Security Group Principal'].Name AS 'Group Name'
		,['Security Group Principal'].Description AS 'Group Description'
		,['Control Company'].Code AS 'INTERID'
		,['Control Company'].Name AS 'Company Name'
 FROM 
	Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	Reporting.SecurityGroupUser AS ['Security Group User'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security Group User'].UserID
LEFT JOIN
	Reporting.SecurityPrincipal AS ['Security Group Principal']  WITH (NOLOCK)
		ON
			 ['Security Group User'].GroupID = ['Security Group Principal'].ID
LEFT JOIN
	Reporting.SecurityCompanyPermission AS ['Security Company Group Permission'] WITH (NOLOCK)
		ON
			['Security Group Principal'].ID = ['Security Company Group Permission'].PrincipalID
LEFT JOIN
	Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Group Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterGroupBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

● Categories: Dynamics, GP, Management Reporter, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Security Views For Use In SmartList Designer: User Based Company Access In Management Reporter

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This first script returns the security based on how the user is configured; the view I will post on Monday shows Group based security.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterUserBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
	,['Control Company'].Code AS 'INTERID'
	,['Control Company'].Name AS 'Company Name'
FROM 
	ManagementReporter.Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	ManagementReporter.Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	ManagementReporter.Reporting.SecurityCompanyPermission AS ['Security Company Permission'] WITH (NOLOCK)
		ON
			['Security User Principal'].ID = ['Security Company Permission'].PrincipalID
LEFT JOIN
	ManagementReporter.Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterUserBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

● Categories: Dynamics, GP, Management Reporter, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

The second of the new features I’m going to cover is SmartList Favorite Protection.

This feature allows the administrators to define a password which must be entered before a SmartList favorite can be modified. The password is entered in the SmartList Options window (Administration >> Setup >> System >> SmartList Options):

SmartList Options

The password impacts all SmartList favorites. Please do not use ACCESS as the password; I see far too many clients who have this as a password throughout their system. It is in all of the manuals and lots of blog posts so it is pretty much the least secure password you could use.

Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection

● Categories: Dynamics, GP, Microsoft, SmartList ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

This is the first of the posts where I will be covering the new functionality introduced to Microsoft Dynamics GP 2016 R2. In this post I am going to cover the addition of SmartList Designer favorites in Advanced Lookups new feature.

To use this feature, create a SmartList Designer object:

SmartList Designer

Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups

● Categories: Advanced Lookups, Dynamics, GP, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R2 Feature of the Day: SmartList Favorite Protection

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here

The second Feature of the Day is SmartList Favorite Protection:

SmartList Options

This new feature allows a password to be set which will protect SmartList Favourites from being modified; when a user tries to modify a SmartList Favorite they will be prompted to enter a password.

The downside of this setting is that it is a global setting for all SmartList Favourites and not a password which can be set differently for each Favourite. This point was mentioned to Microsoft at reIMAGINE 2016.

Click to show/hide the MDGP 2016 R2 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, SmartList ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R2 Feature of the Day: SmartList Designer SmartLists in Advanced Lookups

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here

The first Feature of the Day is SmartList Designer SmartLists in Advanced Lookups:

SmartList Favorite

Continue reading → MDGP 2016 R2 Feature of the Day: SmartList Designer SmartLists in Advanced Lookups

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MS Connect Suggestion Sunday: Multiple Items To Vote Upon

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPI occasionally post suggestions from MS Connect and ask people to read the suggestion and cast their votes. Well, today I don’t have a single suggestion, or even two, but three suggestions for you to vote upon.

One Click Access to SmartList in Web Client

In the desktop client you can access SmartList from the area page, from the Microsoft Dynamics GP menu or via a toolbar icon. However, in the web client, only the first of these options are available and most users of Dynamics GP do not generally know of ways othet than the Microsoft Dynamics GP menu to start SmartList. This suggestion is to make SmartList available via one click in the web client.

Chris Dobkins submitted the suggestion and asked if SmartList can be made available by an icon next to the User Date in the web client; I don’t actually mind where the icon is, but agree that a single click method is required to make SmartList more accessible.

Vote here for this suggestion.

Hide Business Analyzer in Navigation Lists for All Users and All Lists

Business Analyzer can be a good way of seeing infromation from Dynamcis GP, but I have never liked BA being added to the navigation lists in Dynamics GP. This is why I added a recipe to the Microsoft Dynamics GP 2013 Cookbook which allowed it to be switched off globally with a SQL trigger. A Chris says, it would be better to have this functionality available via the GP client itself and allow users to switch it on when wanted.

Vote for this suggestion here.

End a corrupted Web Client Session

This is the third suggestion, suggested by Chris.

From time to time, a web client session will become corrupt. When this happens, you can reconnect to the session, but GP does not work (you may just gets lots of errors pop up in the status spinner, for example). When this happens, the user needs the ability to end the session from the screen that lists your active sessions and the tenants to which you have access to create new sessions. Users have the ability to remove their login from the activity table, and have had for many versions. Ending a hung web client session is the web client version of removing your user from the activity table so that you can log back into GP.

Vote for this suggestion here

● Categories: Business Analyzer, Dynamics, GP, Microsoft, Microsoft Connect, SmartList, Web Client ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: SmartLists from Favorites

● Ian Grieve ●  ● 0 Comments   ● 

As mentioned in the previous post, Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer and have been gradually improving it since. This new feature allows a user to create a new SmartList from a favourite, instead of just from a SmartList Object:

SmartList

SmartList Designer

The main features of are:

  • A user can create a new SmartList from a favourite using Designer
  • The user doesn’t have to remove all extra columns from the default SmartList

This new feature will ease the creation of a SmartList Object simialr to an existing favourite instead of a copy of the default SmartList Object to which the favourite belongs.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: Export/Import in SmartList Designer

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer. It was reasonably useful, but nowhere near as useful as SmartList Builder. Little by little Microsoft are making SmartList Designer more useful. Microsoft Dynamics 2016 R1 actually makes it quote a bit more useful by adding the ability to export and import SmartLists:

Export/Import in SmartList Designer

Import in SmartList Designer

Export in SmartList Designer

One f the problems with SmartList Designer was that you had to create the SmartList on the server on which you wanted it; you could not develop it on a test system or development system and if, as a partner, you wanted to give it to another client you had to recreate it. This is why I often created SQL Views and plugged them in rather than creating the SmartList in the Designer.

I’ll need to do some testing to see how good the export/import functionality is, but this feature may allow us to put together SmartLists on our development system and then export them for clients.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Designer ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: Excel Export as Numbers

● Ian Grieve ●  ● 0 Comments   ● 

This feature of the day puzzles me somewhat as I would swear this was a feature introduced within the last couple of versions. It is the export of numbers as numbers from a SmartList:

Excel Export as Numbers

As I mentioned in the introductory paragraph, this is a feature I believe was already introduced, but upon further reflection this may be a slightly different new feature.

The previous feature I am thinking of (but can’t find a specific announcement) exported the numbers to five decimal places and without a currency symbol. The example given in the screenshot, above, has a currency symbol and is to two decimal places.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, SmartList ● Tags: , , , , , ,  ● Permalink ● Shortlink ●