Labels

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
    en.endpoint_id=ses.endpoint_id

where en.name='Dedicated Admin Connection'
/***********************************************/






No comments:
Write comments

Please do not enter spam links

Services

More Services