Many people have often asked, do I need to provide the "sa" account to my GP administrator to run GP Utilities? In the past, it has always been thought for that to be the case, but fortunately, with some testing, I have discovered it is not true at all!However, it does involve giving your GP admin "sysadmin" permissions in the fixed server role of SQL. So, while they are not the actual "sa" user, they do need to have "sysadmin" permissions as a fixed server role.Q: What can I do without sa if I create a user and give them sysadmin?A: Why everything of course! That user can create a company, they can upgrade or apply a service pack, pretty much anything the "sa" user can do in utilities.Q: Should that user I create be a GP user or a SQL User?
A: Good question! If you just want them to run utilities to upgrade GP or create a company, they DO NOT need to be a GP User! (Shocked me when I tested!). However, if you want them to be able to login to GP and be an admin INSIDE of GP, they would need to first be created as a user in GP, and then granted the "sysadmin" fixed server role.Q: Why do they need "sysadmin" as a fixed server role
A: If you want them to be able to create a company using GP Utilities, this requires the "sysadmin" role in SQL server to be able to create a database in SQLQ: What if I just make them "dbo" in DYNAMICS and all the GP databases?
A: They would not be able to create a new company, sysadmin is required for that. They MAY be able to upgrade or apply a service pack, since DBO technically has permissions to drop and re-create objects in a database - which is what GP needs when it's doing an upgrade. HOWEVER, when you apply a service pack or upgrade to a new GP version, often times GP Utilities will create, or re-create assemblies (CLR) - and this may not be able to be done without the "sysadmin" fixed server role.Q: What about Management Reporter
A: It is possible to use a non-sa account there as well - however, if you are creating the datamart, the user would need to have the "sysadmin" fixed server role because it needs to be able to create the database for the datamartQ: What about administering GP, i.e. creating new usersA: It is possible to do this without "sa", by giving the user "sysadmin" permissions in the fixed server role. If you do not wish to do this, FastPath has a document outlining specific permissions needed without giving "sysadmin":Fastpath White Paper - Minimizing the use of 'sa' in Microsoft Dynamics GP
If you've found this thread useful, click here to dive deeper into User Group community content by role