How To Find Who is Connected using DAC in SQL Server

This can be funny post because we only use DAC when no other option left to connect SQL server instance and until you connect you cant' find who is using same.

But this can be handy while testing or for trainers who might have left one DAC connection open and during demo get error 

"Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process"

So for those who just want to check if some one is using dac even though just for audit purpose, here is the query i found over web during training

select case when ses.session_id= @@SPID
       then 'It''s me! '   else '' end
        + coalesce(ses.login_name,'???') as WhosGotTheDAC,
    ses.session_id, ses.login_time,
    ses.status, ses.original_login_name
from sys.endpoints as en
join sys.dm_exec_sessions ses on

where'Dedicated Admin Connection'

No comments:
Write comments

Please do not enter spam links

Meet US


More Services