Eric M Russell (3/26/2014)
Steve Jones - SSC Editor (3/26/2014)
Gary Varga (3/25/2014)
I use the connection colour option in SSMS. See 'Use custom color' and set up registered servers using the desired colours. I tend to give red to production, yellow(ish) to UAT etc and green to development instances. It works like traffic lights in that I don't remember checking most of the times when it wasn't an issue but I do notice the colour a lot of times when I need to be sure.
I think this works well for some people, but for others your muscle memory takes over and the colors blur into the background because you see them so often.
For application, user, or service accounts, we can limit operations using role based security. The problem with sysadmin, is that it always has full privilege. However, running ad-hoc DML or DDL operations should not be part of a DBA's daily routine. Something that would be useful, not specific to SSMS but rather a feature of the provider libraries, is a ReadOnly connection option. In the SSMS 'Connect To Server' dialog, the DBA could toggle on or off the ReadOnly option depending on wether they are just managing backups and monitoring that day, or wether they are about to do something like run a deployment script.
I just discovered that, starting with SQL Server 2012 Native Client, we now have the connection property ApplicationIntent = ReadOnly.
However, when I specify that option in the "Additional Connection Parameters" tab of the "Connect to Server" dialog in SSMS, it appears to have no effect, I can still update tables. It would be useful to place a connection in read-only mode without logging in under a special read-only account.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho