Set Sql to Single User mode From C# and Create Admin User

Question

I have a c# winform application which connects to a database and read data. This database is used by another app also. I just executed a query from my app to read some data.

I don't have the user and password of the database.

So I have to change sql service to single user mode manually : add -m parameter to sql service and then restart the service and Because I have local admin password. I can login with windows login and create user and set sysadmin roll to it.

Now I want to do all this programmability on c#

Is there a way to have Create sysadmin user on sql with windows local admin password with sql command ?

Answer 1

I was able to do it

first create batch file example test.bat and set code into

@ECHO OFF
ECHO Please Wait....
NET STOP MSSQLSERVER
NET START MSSQLSERVER /m"SQLCMD"
sqlcmd -s . -E -i query.sql
NET STOP MSSQLSERVER
NET START MSSQLSERVER

first stop sql server

and start -m parameter for start single user mode

"SQLCMD" only connect from command line in single user mode

after start single user mode execute query.sql

-s . server address here my local

-e connect windows authentication

-i sql query for execute

query.sql:

use mydatabase
Go
IF  EXISTS (SELECT * FROM sys.server_principals  WHERE name = N'newuser') DROP login [newuser]
Go
create LOGIN newuser WITH PASSWORD = '123456',CHECK_POLICY = OFF,CHECK_EXPIRATION = OFF ;
Go
EXEC master..sp_addsrvrolemember @loginame = N'newuser', @rolename = N'sysadmin'
Go

in sql query first check if newuser exist drop then create with sysadmin role

after execute sql query stop sql server and again start normaly without parameter

now execute batch file run as administrator on with c#

 var proc = new Process();

  proc.StartInfo.CreateNoWindow = true; //run background
   proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;

   proc.StartInfo.Verb = "runas"; //ask local admin pass when run
   proc.StartInfo.FileName = "batch file address";
  proc.Start();