Granting Microsoft Dynamics GP user minimal access at the database level to setup additional users

After the long title of this post, you probably already have the idea of what the article will be about. However, back in April of 2009, I wrote about the POWERUSER role and the Microsoft SQL Server sysadmin server role - see Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role - and explained the key differences between the two. Among other things, I discussed how a GP user login that's assigned to the sysadmin server role on Microsoft SQL Server becomes able to setup new users in GP.

However, those of you who are database administrators have been quite reluctant to add logins to the sysadmin group, and quite understandably so. After all, logins added to the sysadmin server role can do anything on the database server, and we sure don't want that to happen either.

In response to this, and to the many requests lately on the forums, my friend Robert Cavill, with Emeco Group in Australia, has submitted the following script, which gives a specific user ID in Microsoft Dynamics GP, minimal but sufficient permissions at the Microsoft SQL Server level to create new users. In addition, this script allows Robert's first level support staff with access to Microsoft Dynamics GP, the ability to reset passwords for their user base.

GrantUserRights.sql

--Created by Robert Cavill
--This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

DECLARE @sUSERID NVARCHAR(15);
DECLARE @sql NVARCHAR(MAX);
SET @sUSERID = 'LESSONUSER1'

SET NOCOUNT ON;
SELECT @sql = 'EXEC master..sp_addsrvrolemember @loginame = ''' + @sUSERID + ''', @rolename = N''securityadmin'';';
SELECT @sql;

SELECT @Sql =
'USE [DYNAMICS];
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ' + QUOTENAME(@sUSERID, CHAR(39)) + ') 
  CREATE USER '+ QUOTENAME(@sUSERID) + ' FOR LOGIN ' + QUOTENAME( @sUSERID ) + '; 
EXEC sp_addrolemember N''db_accessadmin'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''db_securityadmin'',' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''DYNGRP'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
GO'
FROM DYN001..SY01500;
SELECT @sql;


SELECT @Sql =  
'USE [' + RTRIM(INTERID ) + '];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ' + QUOTENAME(@sUSERID, CHAR(39)) + ') 
  CREATE USER '+ QUOTENAME(@sUSERID) + ' FOR LOGIN ' + QUOTENAME( @sUSERID ) + '; 
EXEC sp_addrolemember N''db_accessadmin'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''db_securityadmin'',' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''DYNGRP'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
GO'
FROM DYN001..SY01500;
SELECT @sql;

When this script is executed against the DYNAMICS database for a specified Microsoft Dynamics GP user (@sUSERID variable), the result is another script granting the correct access to all the Microsoft Dynamics GP company databases.

Result

EXEC master..sp_addsrvrolemember @loginame = 'LESSONUSER1', @rolename = N'securityadmin';
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'LESSONUSER1') 
  CREATE USER [LESSONUSER1] FOR LOGIN [LESSONUSER1]; 
EXEC sp_addrolemember N'db_accessadmin', 'LESSONUSER1';
EXEC sp_addrolemember N'db_securityadmin','LESSONUSER1';
EXEC sp_addrolemember N'DYNGRP', 'LESSONUSER1';
GO

USE [TWO00];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'LESSONUSER1') 
  CREATE USER [LESSONUSER1] FOR LOGIN [LESSONUSER1]; 
EXEC sp_addrolemember N'db_accessadmin', 'LESSONUSER1';
EXEC sp_addrolemember N'db_securityadmin','LESSONUSER1';
EXEC sp_addrolemember N'DYNGRP', 'LESSONUSER1';
GO

Upon running the result script, the new database permissions will enable the Save button on the User Maintenance window, and allow users to be assigned to companies in the User Access window.

Here are a few additional tips:

1. With this approach, the Microsoft Dynamics GP user is not a member of the sysadmin fixed server role.
2. The user ID must already exist in Microsoft Dynamics GP with access to at least one company so they can log on.
3. If, after executing this script, you attempt to delete the user ID from GP, it will fail.

In the following post, I will publish the script that will reverse the outcome to allow deletion of the user ID from Microsoft Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MVP
Intelligent Partnerships, LLC
http://www.intelligentpartnerships.com/


Updated 03/10/2015 - Changed script to declare an @SQL variable to hold the output of the SELECT statement to prevent script truncation.