I did some work for a client recently which involved creating a report which could be run against any database; we therefore needed to add the user to a database role in all databases which gave access to the relevant SQL objects.
The below script generates a SQL script which can be used to alter the role to assign it to the specified user in all of the Microsoft Dynamics GP company databases.
The script assumes the user already has a server login and that the role exists in all databases:
DECLARE @DatabaseRole VARCHAR(140) = 'db_reports'
DECLARE @Username VARCHAR(140) = 'AZRCRV\iang'
SELECT 'USE [' + RTRIM(INTERID) + ']
GO
ALTER ROLE [' + @DatabaseRole + '] ADD MEMBER [' + @Username + ']
GO'
FROM
SY01500 AS ['Company Master']
GO
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.