I am interested in creating an alternate Farm Administrator account so I don’t have to continually log in using the original farm admin account. I visited and used Todd Klindt’s blog post “How to
create a SharePoint 2010 admin account and stop using sp_farm“, however, it doesn’t talk about database roles or permissions. How should my new, alternate farm admin account be configured relating to all the databases: ie. SharePoint system database, content databases, and service databases, etc..
For instance, when I log into SQL Server and view the “server roles” for the original farm admin account it lists the following: dbcreator, public, securityadmin, and sysadmin. However, when I check the “server roles” for my alternate account it only shows the role of “public”. Also, when I check the “User Mapping” for the original farm admin account, there are a wide variety of roles to checkmark depending on the role of the database.
What roles and permissions should I give my new alternate farm admin account so that it has equal authority and permissions with all the different databases? Is there a PowerShell script I can run to apply particular roles to different database types (SharePoint system database, content databases, and service databases, etc.) ?
Plus, if I create a second, alternate farm admin account dedicated to backup and restore operations, what additional (if any) setup should I be focused on? We run a 3rd party backup/restore application that uses the farm administrator account. My goal is to give this 3rd party application the second, alternate farm admin account and stop using the original farm account for backup and restore operations. This way we can track all operations by the new account.
Thank you
I added the role (as shown in the first or two attached screenshots), but it did not add the same roles to the individual databases. If I were to go under “User Mappings” for my new admin account and select a database, the db_owner role is not also selected. Should I not be using the “Server Roles” screen to checkmark the db_owner and securityadmin roles and manually check these fields individually in each database (as shown in the second screenshot)?
Alright, thank you. I will set up a new, alternate farm admin & a backup account in our development farm and test it out. I will post back on the results.
Thanks for the assistance Trevor.
The only exception I’m aware of is with the PowerPivot Config Tool. That tool must be run with the account that created the farm (ran the initial Config Wizard).
As far as a backup account, it will need the same rights as a Farm Admin and of course access to the backup sources.
Ok, so I will add the dbcreator and securityadmin roles to my new alternate farm admin accounts and run the Add-SPShellAdmin cmdlet again. I already ran it once, but I had not added the database roles for these accounts yet.
I noticed on a few databases there were roles checkmarked for the original farm admin account, such as: SPDataAccess and db_accessadmin. One account in particular, the Farm Config DB, had these roles and also the WSS_Content_Application_Pools role. Should I also check these roles for my new alternate accounts?
Once that is done then my alternate farm admin accounts will have the same power/authority as the original farm admin account? Are there any gotchas under certain circumstances where the new accounts will not have the same authority as the original farm admin account? Also, following this setup, it will allow the new backup account access to backup and restore all databases regardless of type (config db, farm admin database, content database, service database, etc.)? If so, that would be great.
Thanks
So the Farm Account doesn’t need sysadmin, just dbcreator and securityadmin. Once you add an account to those roles, simply use Add-SPShellAdmin domain\username and it will grant access to the SharePoint dbs.
