[See an index of all bad habits / best practices posts]
The sa password should be a well-guarded secret, held in the same regard as the domain administrator password. Unfortunately, I come across too many environments where sa is used everywhere – people log in manually because it makes it easier to get things done, and people put it in their connection strings because it makes it much easier to manage the security for their applications.
This needs to stop.
The security holes this introduces are immeasurable. And I don’t really think it matters if you’ve disabled or renamed the sa account and are using a “more secure” (read: just slightly more obscure) account name. Once the sa password is out there – say, in a critical 24×7 application or in your CFO’s spreadsheet – the password is impossible to change, and the account is impossible to disable.
Your developers need sa privileges?
They probably don’t. I’ll confess, back in the day, it was far easier for me to use a privileged account than to granularly apply the right permissions to all the objects we were creating (especially during rapid early development), and all the objects those objects touched. These days, options for procedures like EXECUTE AS OWNER and signing modules with certificates make it much easier to grant rights to the top-level objects, and that’s it. The developers themselves should have Windows auth logins (so that they can’t share credentials, making auditing much easier) and the application(s) they’re developing should have a separate login.
A web site or application (maybe a vendor application) needs to connect as sa?
Well, I would fight even the sysadmin role in general, never mind sa explicitly, depending on the nature of the application. That said, I do understand why certain types of applications may require elevated privileges. If they really do, there is no reason to give them sa explicitly; just add their dedicated login to the sysadmin role. If they don’t, then give them a dedicated login that has precisely the rights that they do need. In either case, when the application goes away, so can the login; and if the login needs to be changed for whatever reason, it only affects that application.
You need to have someone else do some admin work while you’re out on vacation?
Great! Create a dedicated login for them that you enable for that time period, and add it to the sysadmin role. Or better yet, add their Windows login to the sysadmin role temporarily. Better still, only grant their Windows login privileges for the specific tasks they need to accomplish. On SQL Server 2012 and newer, for example, you can use custom server roles for more granular control (as described here by Mike Walsh).
Final Thoughts
Dedicated per-app logins and having users perform their work via Windows logins is the way to go, IMHO, both for security and auditing purposes. If anyone outside the core DBAs have the sa password, it may be time to revisit that policy. Oh, and please make the password – and all SQL auth passwords, for that matter – strong. P@ssw0rd, $w0rdf1$h, and QWERTY are not good options.
