sql server - limit all users to 1 session -

i have been required configure sql server allow 1 session per login. have found few references on creating login triggers in order prevent login establishing more 1 session, wondering if there way define @ lower level, session limit default, rather having define in login each user?

i seeing lot of references topic in both "questions may have answer" , "similar questions" here on stackoverflow, far have either not found or not understood post describes trying do. have seen reference declarative management framework allows configure sql server policy think.

i going keep on looking through articles here try learn this, in meantime... advice appreciated!

the example logon trigger in books online pretty close think want, i've made few changes make work logins.

-- trigger must created user 'view server state' permission in order trigger have unrestricted access sys.dm_exec_sessions. create trigger connection_limit_trigger on server execute self logon begin     -- check whether caller sysadmin.     -- note: trigger executing under elevated security context must switch caller's context test sysadmin status.     declare @issysadmin int     execute caller     set @issysadmin = isnull(is_srvrolemember ('sysadmin'), 0)     revert      -- if user not sysadmin , there many non-dormant sessions login 'rollback' logon.     -- note: sys.dm_exec_sessions not include entry logon attempt.     if ((@issysadmin = 0) , ((select count(1) sys.dm_exec_sessions is_user_process = 1 , status <> 'dormant' , original_login_name = original_login()) > 1))     begin         raiserror('this login has exceeded maximum of 1 connections sql server.', 16, 1)         rollback     end end 

i've added in check limit doesn't apply sysadmin logins , doesn't count dormant connection pool connections. couple of things note;

• if connection not closed can hang around in sys.dm_exec_sessions while, in case user won't able re-connect until dead connection clears itself.
• if mess logon trigger can lock (and else!) out of sql server. see logon trigger page details of how in :)