As a DBA I have all kinds of elevated access that most people don't. I guess when I was younger this was a novelty but it really isn't anymore. I have access to the master list of usernames and passwords for all kinds of stuff, but would never use them for anything unless it was appropriate to do so. So it really aggravates me when I find or catch someone using an application's username/password to log into SQL with SSMS for the simple fact that I would never do this, not to mention that it is a violation of security policies. It seems to happen however, so how do we go about preventing it.
Well with 2008 I have been told a that it should be possible, but it really is possible to manage this with SQL 2005 as well:
CREATE TRIGGER CheckLoginFirst
ON ALL SERVER WITH EXECUTE AS CALLER
IF Is_Srvrolemember('sysadmin') = 0 AND (APP_NAME() LIKE '%Microsoft SQL Server Management Studio%')
RAISERROR('You have violated the Security Policy by using a Service Account to Access SQL. This has been logged and will be reported.', 16, 1) WITH LOG
This is a really simple DDL Trigger that will first, prevent the login for any non-sysadmin, your application users aren't sysadmins are they, that tries to login with Management Studio, and then log the event to the SQL ErrorLog, you do monitor your error log right?
The above code is founded on a blog by Jens Suessmeyer.