SQL Server – DEDICATED ADMINISTRATOR CONNECTION ( DAC )

Background

Trying to review Extended Stored Procedures, but ran into a stumbling issue.

Recreate

Query

Issued a query against master.sys.all_extended_procedures to get a list of extended Stored Procedures.

SQL


select * from sys.all_extended_procedures

Output

Output – Image

sys.all_extended_procedure.20181205.0857AM

Output – Text

Msg 208, Level 16, State 1, Line 17
Invalid object name 'sys.all_extended_procedures'.

Remediation

SQLCMD

SQLCMD – Connect

Syntax


sqlcmd -A -S [computer]

Sample


sqlcmd -A -S localhost

SQLCMD – Query

SQLCMD – Query – master.sys.all_extended_procedures

Syntax


sqlcmd -A -S localhost -Q"select left(name, 30) as [name], dll_name from master.sys.all_extended_procedures"

Sample

sqlcmd -A -S localhost -Q"select left(name, 30) as [name], left(dll_name,30) as [dll_name] from master.sys.all_extended_procedures where name like 'xp%' order by 1"

 

Output

sys.all_extended_procedure.xp_.20181205.0911AM

Explanation

  1. xp_delete_file
    • dll_name :- xpstar.dll

 

SSMS

SSMS – Query – master.sys.all_extended_procedures

Syntax


EXEC sp_helpextendedproc;  

Sample

EXEC sp_helpextendedproc xp_delete_file;  

 

Output

sp_helpextendedproc.20181205.0915AM

Explanation

  1. xp_delete_file
    • dll_name :- xpstar.dll

 

Summary

To access certain SQL Server functionalities, please connect using Dedicated administrator connection (DAC).

 

References

  1. Microsoft
    • Docs \ SQL \ Relational databases \ System stored procedures
      • sp_helpextendedproc (Transact-SQL)