As Windows Server and SQL Server evolve, Microsoft has hardened the products in terms of security. With Windows Server 2008, just being a member of the local administrators group is no longer enough. In the old days, that would give you administrator rights across SQL Server and all its components on that server. Those days have gone and rightly so.
Windows Server 2008 now limits the overuse of elevated permissions by actually removing administrator permissions when accessing applications. Of course, this does not apply to THE administrator. This gives the effect of a “super” administrator when using the actual administrator account. Other members of the local administrators group, for instance, will not have access to SQL Server Reporting Services until the administrator grants access explicitly within the application.
This is as it should be, but we had become complacent with the past ability of simply giving membership to the administrators group. This had serious implications as SQL Server became more diversified. Just because you are a system administrator for Reporting Services does not mean you should also be sysadmin for the SQL Server itself, or for that matter Analysis Services too.
Of course, this makes our administration of SQL Server more difficult, but appropriately so. For instance, to give a user administrator rights in SSRS, we need to explicitly assign the role of system administrator within Report Manager. We then need to add the user to the Content Manager role for the Home folder, assuming we want to give the user that role across all folders and reports.
So this is a big wake-up call for SQL Server DBAs. It’s forcing us to apply security accurately. We need to focus on giving minimum permissions to do the job – nothing more, nothing less.
For more information, check out this how to article: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008: http://msdn.microsoft.com/en-us/library/bb630430.aspx