It is 2009, and I am shocked to find that applications today still show up with a requirement to be a sysadmin on SQL Server, and in the case of one particular application I am helping troubleshoot today, a Local Administrator on the SQL Server machine. As an application developer myself, I understand the due diligence required to properly design an application to follow proper security best practices, and it isn't hard to do if you do it up front. Since I am not one to roll over on security I am willing to spend the hours/days to figure out the appropriate rights necessary for an application to work in SQL without being a sysadmin, but I don't know many people who can/will do that much leg work.
So why does the app need to be a sysadmin in the first place? All to often it is just because that is how it was written, and it makes it easiest. Often there is absolutely no need to be a sysadmin. SecurityAdmin and DBCreator roles are initially needed to do the install so that the login can create the database for the app and setup security properly for it. After this, DBO rights to the database is all that it actually needs, and you can drop the server level roles from the login.
Another place I see that apps need elevated rights is where the application has builtin administrative functions. One app I have seen has a console that will allow the user to change max server memory for SQL Server from the application. Thank you no, that is my job, and I don't want/need some application admin playing with my server configuration. Why do application developers think that this kind of thing is a good idea? We already have applications to manage SQL Server with. I think that this is one of the reasons that the quality of applications that are out there has decreased in recent years. Rather than create a focused application that solves one problem, it seems like application vendors want to do it all, which generally only means one thing, they won't do any of it very good.
So that leaves a question for the community of readers: What do you do with applications like this? I know in past discussions on SQL Server Central that some people use virtualized SQL Servers to solve this problem, and isolate the database on its own VM, but is that really a solution? Now you get server sprawl and added licensing costs because the application, for lack of a better word, sucks?
