Saturday, April 18, 2009

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Dynamics GP system administrators and Microsoft SQL Server DBAs often ask, "Why can't I setup users if I am a member of the POWERUSER role in Dynamics GP?". The question can sometimes be paraphrased as "Why the SQL Server system administrator (sa) user is the only one that can setup users?". The answer is simple: the Dynamics GP POWERUSER role is application specific, while the SQL Server sysadmin role is database specific.

Since sa is a member of both the POWERUSER role in Dynamics GP and the sysadmin role in SQL Server, it can perform all maintenance operations of users in Dynamics GP, along with the setup of additional users. This allows the sa user login, in turn, to create the necessary logins in SQL Server. This is good if you are a DBA, but what happens when you are out and more users need to be added to the system.

So you may now be asking, "How do I make other Dynamics GP users have the same abilities to setup users like the sa user?". You will have to make the Dynamics GP user a member of a role in Dynamics GP with ability to create users -- perhaps, the POWERUSER role or the ADMIN_SYSTEM_001* security role -- and a member of the sysadmin role in SQL Server. To do this follow these steps (assuming you want the user to have full access to all Dynamics GP options throughout the system):

1. Log into Microsoft Dynamics GP as sa.

2. Assign the Dynamics GP user to the POWERUSER role. Go to Microsoft Dynamics GP > Tools > Setup > System > User Security. Choose the user login and mark the POWERUSER role in the access list.





3. The system will warn about the user access to all application functionality. Click on OK to continue.




NOTE: Depending on your security requirements, you may not want to grant access to the POWERUSER role. You can always create a custom role with access to the User Setup window or use the built-in ADMIN_SYSTEM_001* role.

4. Now, proceed to assign the user login to the sysadmin role in SQL Server. Open Microsoft SQL Server Management Studio, open the Security folder, open the Logins subfolder.




5. Double-click on the corresponding user login to open the Login Properties window. Select the Server Roles page and mark the sysadmin role.




6. Click the OK button to finalize the configuration.

Now your Dynamics GP user should be able to setup new users and maintain existing ones, along with performing other SQL Server maintenance activities within the application.

Related Articles
  • The Microsoft Dynamics GP Application Level Security Series. David Musgrave at Developing for Dynamics GP. Click here.

  • Microsoft Dynamics GP Password Implementation. Click here.


Until next post!

MG.-
Mariano Gomez, MIS, PMP, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

4 comments:

Doug Pitcher said...

Hi Mariano,

Happen to know of a way to modify the poweruser security role. I want to assign users poweruser access but don't want them to see reg keys or edit launch file.

Doug Pitcher

Mariano Gomez said...

POWERUSER is not a real role like the others provided with the system or manually configured. Your only option is to create a new role with access to all the tasks, but duplicate and modify the tasks involved with those objects.

Hope this helps.

Devo said...

Pages 37-39 of the Microsoft Dynamics GP document "Planning for Security" describe SQL Server database roles (more restrictive than sysadmin) that can be added to a MSGP SQL Server user to allow the creation of users, deletion of users, and granting user access.

John Z. said...

Hello Mariano,

I have followed the steps you indicated and I can get the Power User with SYSADMIN role in SQL to create new GP Users in our DEV environment. However following the same steps in our production environment produces no results. The Power User still is unable to create ne GP Users. Any Suggestions?