With SharePoint in many cases the SharePoint administrators also are the Windows of the SQL server and/or SQL administrator. This means that those persons also having loads of permissions in SQL Server, which means that everything will work without issues. They can do anything they want, using any tool they want.
But what about a situation where you don't have permissions on SQL. You will see actions via the Central Administration site work fine, however certain things like console applications (custom like SharePoint Manage or default applications like stsadm), scripts via PowerShell and other tooling (basically everything that uses the object model) won't work.
This is caused by the fact that SharePoint requires the account to have certain permissions on the databases in order for it to work. When using the Central Administration, this is done via the application pool account which has sufficient permissions. When using the object model outside of the Central Administration, this is done under the account the command is executed with.
[SOLUTION]
Then which permissions are required? Here is where it becomes a little tricky!
PowerShell scripts:
- When you are running SharePoint 2010 and you are trying to use PowerShell, Microsoft has created a SharePoint internal group called ShellAdmins. By adding your account to this group (Add-SPShellAdmins) your account has sufficient permissions to run PowerShell scripts. Other activities via the object model still don't work.
Other tools:
- To enable all object model activities, grant the account the following permissions:
- SharePoint (Depending on the activities you are going to do)
- Farm Administrator
- Permissions in the site collection(s), for example site collection administrator or contributor to a list
- Database:
- Configuration database: WSS_Content_Application_Pools
- Content database:
- Db_datareader
- Db_datawriter
- GRANT EXECUTE
- NOTE: An alternative would be db_owner permissions on the content database, however from SQL administration perspective this might be unwanted/against policy
More info: SharePoint 2010 PowerShell Permissions Explainedhttp://sharepoint.microsoft.com/Blogs/zach/Lists/Posts/Post.aspx?ID=56
