June 23, 2016 at 6:07 pm
Hi all,
I've checked around about adding a Sysadmin via DAC, all the resources I've seen state you need to place the SQL Server service in Single User mode, and restart it.
Am I right in thinking that although DAC is a diagnostic tool, you don't need any restarts if the service is running just to enhance a login to Sysadmin ?
June 25, 2016 at 7:58 am
You don't need to set SQL Server in single user mode to use the DAC. This is a single threaded connection available for adminstrators that doesn't get resources blocked by long running queries.
If you start SQL Server in single user mode, and there are situations that need this, you can't get back to multi user mode without a restart.
June 25, 2016 at 10:50 am
If you're talking about situations where there's no sysadmin login, or the only one is sa and the password has been forgotten, then you don't need the DAC at all.
The way to add a sysadmin user when you can't log in as sysadmin is to restart SQL in single user mode (the service, not a database), log in with a domain/local machine account that is a member of the local machine's Administrators group, make the log in changes and then restart the service again.
When SQL is running normally, only a member of sysadmin can log in with the DAC (iirc), and can do whatever a login with sysadmin can normally do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2016 at 10:52 am
Steve Jones - SSC Editor (6/25/2016)
This is a single threaded connection available for adminstrators that doesn't get resources blocked by long running queries.
Kinda true. It's got its own scheduler and memory reservation so can run stuff when other schedulers stuck, but it still needs locks/latches/other resources as normal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply