At a previous job I had several developers that had passwords for SQL authenticated accounts for systems that would give them more access than they needed when they had Windows accounts that would give them enough to troubleshoot issues. Our trick was to write a login trigger that would block any SQL Authentication accounts that were logged in with SSMS. Many a developer fell pry to our logon trigger and because of the severity of the message did not quite understand why. This made my troubleshooting life easy because I didn’t have to figure out if a developer ran something to fix something or if something legitimate was broke. Of course I made sure to not block my sa account just in case I needed it.
CREATE TRIGGER Developer_No_Logins_W_SQLAuth ON ALL SERVER
IF EXISTS ( SELECT *
FROM sys.dm_exec_sessions AS es
WHERE es.login_name = ORIGINAL_LOGIN()
AND es.program_name LIKE 'Microsoft SQL Server Management Studio%'
AND es.nt_user_name IS NULL
AND es.login_name <> 'sa' )
She also tinkered with databases in middle school to keep her sports card collection organized.
Tracy has volunteered through the NC Guardian ad Litem program since 2003 advocating for abused and neglected foster children in court.This is her passion outside of SQL Server and favorite job.More information about this program in North Carolina can be found at http://volunteerforgal.org or the national organization CASA at http://www.casaforchildren.org.
Latest posts by Tracy Boggiano (see all)
- Creating a PowerShell Module from a DBA’s Perspective - September 26, 2017
- Automatically Fixing High VLF Count on SQL Server 2012+ - September 19, 2017
- T-SQL Tuesday #94 – Automation Through PowerShell Checking the Status of AGs - September 12, 2017