So today I wanted to rerun a task of using ADMT on a server I configured, I was now connecting to this server via a different domain account then when I had first configured the server in my first test run. Now for the purpose of this particular DB and server purpose I could easily rebuild… but what if… you’re in a situation in which the data and the access to it is much more important.
Also… I was lazy… so I researched.. after a little while (my dev got involved too) it was simply a mission… a purpose a GOAL to figure it out… in the end it was really easy it was just SYNTAX, oh gawd the important of syntax.
Now the SQL team at Microsoft does a lot of wonky things and doesn’t follow standards that most other divisions follow, so it hats off to the walls, if the SQL guys are on mushrooms today better expect some funky changes without notice or documentation. Wait… what… anyway…
My research began.. . stackoverflow.… to… a random blog, archived on the wayback machine ahhh I feel that’d what will become of my blog….
Let me paraphrase everything:
- If you are a local admin you can fix this
- SYNTAX
My main issue was even though I kept starting the service with the sqlservr -m as the blog posted this resulted in an error, it turns out you have to specify the instance name… so
- change DIR to “C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn”
- run “sqlservr -s SQLEXPRESS -m”
*Note* If you are on said mushroom you can skip the space and run “sqlservr -sSQLEXPRESS -m” and it will work too
At this point there are two lines to watch out for:
A) SQL service is running in single user mode (-m)
B) SQL service is running under admin account that is logged in - open another cmd prompt as admin and run “sqlcmd -S COMPUTERNAME\INSTANCENAME -E”
E.G. sqlcmd -S SQLSERVER\SQLEXPRESS -E
*NOTE* the computername is super important else you will get a logon failure at the other console screen showing the compueraccount instead of the user account for some reason.
Because of this error I kept trying other things, my dev tried a couple things and thought one of them was enabling TCP/IP stack, but I said this was all local commands and connections with sqlcmd since this SQLEXPRESS doesn’t come bundled with SSMS and there was no member server or client system with SSMS available to connect or use, as to the point of resetting access locally.
once you get the 1> prompt you can just follow the other guides which is:
1> CREATE LOGIN [DOMAIN\USER] FROM WINDOWS
2>go
1>exec sp_addsrvrolemember @loginame=’DOMAIN\USER’,@rolename=’sysadmin’
2>go
1>exit
Yes that is loginame and not loginname, cause mushrooms.