To Gain SYSADMIN access to SQL Server you need to be local administrator of the server.
The steps are:
Stop the SQL Server Database Service
Open an elevated command prompt and run “SQLServr.Exe –m”
You may need to browse the Binn path of the SQL server installation, usually is: C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinn
Open an other elevated command prompt window and run:
SQLCMD –S . (. is for local server with default instance)
Now you are connected to the SQL Database and you cna start the Query:
/*To add a domain account run:*/ 1>CREATE LOGIN [MyDomainMyUser] FROM WINDOWS; 2>ALTER SERVER ROLE sysadmin ADD MEMBER [MyDomainMyUser]; 3>GO; /*To add an SQL account run:*/ 1>CREATE LOGIN SQLUser WITH PASSWORD = '************'; 2>ALTER SERVER ROLE sysadmin ADD MEMBER SQLUser; 3>GO; /*To change the sa password run:*/ ALTER LOGIN sa WITH PASSWORD = '************';
Finally close both command prompt windows and start the SQL service fom the services mmc.
Pantelis Apostolidis is a Sr. Specialist, Azure at Microsoft and a former Microsoft Azure MVP. For the last 20 years, Pantelis has been involved to major cloud projects in Greece and abroad, helping companies to adopt and deploy cloud technologies, driving business value. He is entitled to a lot of Microsoft Expert Certifications, demonstrating his proven experience in delivering high quality solutions. He is an author, blogger and he is acting as a spokesperson for conferences, workshops and webinars. He is also an active member of several communities as a moderator in azureheads.gr and autoexec.gr. Follow him on Twitter @papostolidis.
