Assign a Microsoft SQL Server Role to a User in All Microsoft Dynamics GP Databases

Microsoft SQL ServerI 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

Can ISC Software help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.

Your Name

Your Email

Subject

Your Enquiry

What should we write about next?

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

Your email address will not be published. Required fields are marked *