This question of how to add users without being logged in as ‘sa’ comes up way more than it should. I’m tired of explaining it to people so I’m just going to show how to do it here.
The idea that a user has to be logged in as the ‘sa’ user to create new users in GP is just wrong. This isn’t an opinion, it’s a fact. Also, this isn’t a new feature, it goes back to at least Great Plains Dynamics C/S+ 5.0 and presumably goes back to the first Dynamics C/S+ for SQL 3.15. The ability to create users without being ‘sa’ also isn’t some kind of hack. It’s detailed in the GP help document Dynamics GP Planning for Security (Page 35, Creating User Records). Read on, or watch the video below for a quick walk through of the process.
In Dynamics GP Planning for Security there are four options listed for adding new users. Option 1 is use ‘sa’, which you already understand. Options 3 and 4 are more complicated, but still closely related to option 2. Because of that we’ll illustrate option 2, namely:
Assign the specific Microsoft Dynamics GP Administrator(s) SQL Login account to the SysAdmin Fixed Server Role. With this option, the Microsoft Dynamics GP administrator can be any user account within the Microsoft Dynamics GP application.
Let’s get started:
1. Identify a GP user that will be allowed to create users. This must be a full licensed, SQL-based GP user, not a web-client only user, not a lim
ited user, and not a self-serve user.
2. Login to SQL Server Management Studio (SSMS) with SysAdmin rights and connect to the server where GP is installed. The ‘sa’ user can be used here or another user with SysAdmin rights. A GP user login cannot be used to login to SQL.
**Tip**: If you’re confused at this step, stop now and consult your dba, partner, or other GP resource.
3. In SSMS, navigate to Security on the main tree (not security under one of the databases).
4. Use the + buttons to expand Security and Logins
5. Find the user that will be allowed to create GP users. For this example I’m selecting the user mpolino.
6. Right-Click on the user that will be allowed to create GP users and select Properties to open the Login Properties Window.
7. In the Login Properties window select Server Roles
8. In the Server Roles page, check the box next to sysadmin and click Ok.
The user is now ready to add users in GP.
To test this:
1. Have the user login to GP.
2. Select Administration on the left.
3. Pick User under Setup > System.
4. Enter a new user and password, then click Save.
DBA’s hate the idea of assigning a user to the SysAdmin role because designating a user as a SysAdmin typically gives them total control over a database, but that is not the case with a GP user. The GP user’s password is encrypted prior to sending it to SQL. This means that the password that SQL sees is not the same as their GP password. When a user logs into GP, their password is unencrypted to allow GP to interact with SQL. Another example of this encryption is the fact that changing a GP user’s password at the SQL level breaks their access to GP.
Consequently, GP users cannot directly access SQL with their GP login credentials. It can’t be done. I’ve bolded that line as a reference for DBAs.
A GP user’s access to the power of SysAdmin is limited to those tasks in GP that require SysAdmin rights. The user is constrained in that they can only interact with SQL within the tasks available in GP. The user is also still bound by GP security. A GP user could be given this access to create users, but not given access to assign or change permission. Additionally, a GP user without rights to the User Setup window can’t assign users just because they have the SysAdmin fixed server role.
Using the ‘sa’ user is dangerous, and not just because it offers total control. It also offers a level of anonymity. If multiple users have the ‘sa’ password, figuring out who used ‘sa’ to perform a task becomes extremely difficult, if not impossible. Once a DBA becomes comfortable with the fact that setting a few GP users as SysAdmins won’t give them unfettered access to SQL, this option makes it much easier to limit the use of ‘sa’.