Sunday, November 21, 2010

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.

7 comments:

Mike Muscarella said...

Mariano, this is a great tip. Did you post the follow-up on how to be able to delete the user ID?

Mariano Gomez said...

Mike,
Thanks for your kind words. I have not gotten around to publish the second article, but thanks for the follow up. Will get around to it soon.

Please keep up the readership.

MG.-
Mariano Gomez, MVP

Beat BUCHER said...

Hi Mariano,
I don't know if you checked the outcome of the first script, but somehow it get's truncated (maybe output limitation of SQL ?)... thus the second part of the script is not usable as is and must be completed manually... I was too looking for a way to provide non SA user (i.e. IT Help Desk ) a way to reset passwords in GP.
Let me know how you managed to circumvent the limitation.
Thanks and have a great time,
Beat

Mariano Gomez said...

Beat,
Just ran the script again and cannot see a problem with it, could you be more specific as to what you are experiencing?

MG.-

Unknown said...

I had a problem with the script too. It is not completing the script for the individual databases (or in some cases it's over-completing).

In your example, for database TWO, it doesn't complete the username. It shows "'LESSON" as opposed to "'LESSONUSER1';". In mine, it actually continued past this line and started, but did not finish, another EXEC line. This is what mine looked like:

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

The script for each database ended in a slightly different place, but I was able to correct by deleting everything after "EXEC sp_addrolemember N'db_securityadmin','LESSONUSER1';"
for each company (essentially making sure the statements for each company matched the original statement for the DYNAMICS database). After doing this it worked fine.

Cheers!

Who me? said...

I am getting the same issue of the script truncating at the database level

see

---------------------------------------------------------------------------------------------
EXEC master..sp_addsrvrolemember @loginame = 'creeves', @rolename = N'securityadmin';


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


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [GAR48];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'creeves')
CREATE USER [creeves] FOR LOGIN [creeves];
EXEC sp_addrolemember N'db_accessadmin', 'creeves';
EXEC sp_addrolemember N'db_securityadmin','creeves';
EXEC sp_a
USE [GAR51];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'creeves')
CREATE USER [creeves] FOR LOGIN [creeves];
EXEC sp_addrolemember N'db_accessadmin', 'creeves';
EXEC sp_addrolemember N'db_securityadmin','creeves';
EXEC sp_a
USE [GHITP];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'creeves')
CREATE USER [creeves] FOR LOGIN [creeves];
EXEC sp_addrolemember N'db_accessadmin', 'creeves';
EXEC sp_addrolemember N'db_securityadmin','creeves';
EXEC sp_a

Mariano Gomez said...

Cindy and all, the script has been updated to use an NVARCHAR(MAX) variable to hold the content of the output query. This in turn prevents truncation.

Thanks for reporting the issue.

MG.-
Mariano Gomez, MVP