As database administrators, one of the challenges we're faced with is limiting SQL Server logins and database users to appropriate permission levels. It's common to find applications that "require" elevated permissions that aren't warranted. I've seen this in simple applications from the smallest of software companies, and I've seen it in enterprise-level applications from huge companies with billions of dollars of annual revenue. Here's an example from an installation guide:
This particular vendor offers up two poor options for SQL Server security. While one of them is obviously worse than the other, neither is a viable option. Picking the lesser of two evils is of little solace. It's disheartening and frustrating that they would present two egregious security configurations. On the plus side, the vendor seems to recognize the need for two sets of permissions: one for installation/upgrades and one for application run time. Still, the permissions are the same for both scenarios.
Are you a software company, or do you work for one? If so, poor security casts your company in an unflattering light. Customers will wonder what other shortcuts or omissions occurred during your application's SDLC. They may assume that your company...
- ... doesn't understand the SQL Server Security Model
- ... doesn't understand the implications of poor security
- ... doesn't care about security
- ... didn't take the time to figure out what permissions are needed by the application
Application Run Time Security
Eventually, you will encounter a dba that won't allow elevated permissions for your application. At the very least, take the time to determine what run time permissions your application needs. It may be a lot less than you realize. In development and QA cycles, refrain from using logins or users that have elevated permissions. Scaling back the permissions sooner rather than later will help you in the long run. Avoid the temptation to configure your application with the same SQL Server login that is used to install the application.
Although still important, this is less of an issue than the application run time permissions. Many dba's will allow a temporary login with elevated permissions for installations and upgrades that can be dropped or disabled afterwards. Don't expect that will always be the case, though. Probably the best thing you could do would be to have an option to create a tsql script of all the commands your installation would normally attempt to execute. This script could be vetted and executed by the customer's dba prior to installation. This will require some defensive script coding within your installation, though. For instance, checking for the existence of an object before trying to create it, checking for a user's membership in a database role before adding the user to the role, etc. This can have the added benefit of making your installation less susceptible to errors.
Choosing and Buying Your Software
Software development isn't easy. In spite of all the challenges and difficulties, you continue to create some amazing stuff. Some of us can't even consider your product because we are bound by compliance standards (HIPAA, PCI, SOX) and/or strict organizational security standards. The rest of us may rule you out simply because we want to do the right thing. Tightening up SQL Server security makes it easier for us to choose and buy your product.