Microsoft Dynamics GP 10 Poweruser role vs SQL Server sysadmin role

 

Microsoft Dynamics GP 10 Poweruser role vs 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/

Posted by Mariano Gomez at 8:00 AM

The Dynamics GP Blogster: Microsoft Dynamics GP 10 Poweruser role vs SQL Server sysadmin role