Introduction
I was recently working a production issue that required me to log in via the Dedicated Administrator Connection (DAC). When I tried to log in I was greeted with the following message:
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.
After searching through my scripts folder and not finding anything that would tell me who was connected via the DAC I turned to Google. I found a lot of posts detailing how to use the DAC but none telling how to tell who is using it.
Once the emergency was taken care of I made a point of going back and writing a query to tell who is using the DAC. To help the community or at least that part that relies on Google like myself I am also blogging it and adding appropriate search tags.
The Query
I know, I know, enough with the backstory already lets see some code. The code I have posted here will show all users by connection, although I have included a commented WHERE clause to filter to just the DAC connection. Here is the SQL:
1 2 3 4 5 6 7 8 9 | SELECT ep.name, ep.protocol_desc, es.login_name, es.nt_user_name, es.host_name FROM master.sys.dm_exec_sessions es INNER JOIN master.sys.tcp_endpoints ep ON es.endpoint_id = ep.endpoint_id --WHERE ep.endpoint_id = 1 --Uncomment WHERE to show who is on DAC Connection |
Conclusion
Hopefully this script is helpful. Unfortunately this script will not tell you who is connected via the DAC when nobody is able to connect to the SQL instance in question, netstat from a command line on the server may help there. Please let me know if you run into any issues, have any ideas that would make it better or just want to share how you are using it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
0 thoughts on “Get Users By Connection”