ArcGIS Server 10.2.2
SQL Server 2008 R2
DBO owned schema
I noticed that our ArcGIS Server account is in the sysadmin server role in our SQL Server 2008. Does ArcGIS Server place the server account in that role by default? Seems like too high level of permissions for the server account....
I think a problem I am having is stemming from this. When I block connections to the database from ArcCatalog, the ArcGIS Server account is still able to connect while I'm doing database management tasks and it causes them to fail.
Solved! Go to Solution.
I mapped the ArcGIS Server account to all of it's associated databases with read/write privileges, then removed it from the sysadmin server role, and then had to re-boot the server for the changes to take effect.
Step 1: Grant read/write
Using SQL server management studio
expand the security tab, then the logins tab
open the properties for the AGS server account login (by default the username is "arcgis" and it could be a domain or local user), then click User mapping
in the top pane, select the db in question
now in the bottom pane check the boxes nect to datareader and datawriter roles
Step 2: Remove AGS account from sysadmin server role
Expand the security tab, expand the Server Roles tab
open the properties for the sysadmin role
select your AGS account user and click "remove"
Step 3: reboot the server.
I had our IT department do this for me.
Rebooting the server isn't required and shouldn't be done. I you reboot the server (or restart SQLServer) then all the stored execution plans it has will be cleared which will have a negative impact on query execution performance (you could say it 'learns' by experience which of different alternatives of making a query is the best to use).
Don't reboot the server...
What to do if the permission changes wont take effect? Tried multiple times to make the changes and they wouldn't take. Called ESRI support and they said to re-boot; only after re-boot did the changes take effect.