Loosing and forgetting all administration account within SQL Server

Has it ever occurred to you that you lost all SQL user login with sysadmin privilege and forgot the SA password?
Or you changed the server name and forgot to enable SQL login authentication ?

Well, people who are working with SQL Server did feel that too. There are some ways to solve this kind of problem.

It also happened to me when I changed the server name that places the SQL Server application inside it. After changing the computer name, I tried to login to SQL server and I was unable to do it because the SQL server reads the new name of the computer and it is considered that all the windows user login is not registered yet. And it gets worse when I realize that the server authentication that I configured is only Windows Authentication mode only which means that I was only able login to SQL server using windows user login.

In this blog I will show you step by step how to login to SQL server with minimum configuration and you will be able to create the user login through SQLCMD.


1. Using cmd, run the script below:

========================================================================
SQL server without instance name:

net start mssqlserver -f

SQL server with instance name:

net start mssql$<instance name>
========================================================================

2. open sqlcmd using command prompt, just type SQLCMD then hit enter button. This will enable you to write a query just like when you open query window using SQL server management studio

3. Start creating new user. According to my case, I didn't activate SQL server authentication mode, thus I need to register the windows user so that I'm able to login into SQL server. Just run the query below:

========================================================================

If you  register the windows user under specific domain:

Create login [DomainName\UserName] From Windows

go

If you register under the local user:

Create login [ComputerName\UserName] From Windows

go

=======================================================================

4. Give permission as sysadmin to the user you just created so that you will have full access to the SQL Server

sp_addsrvrolemember 'domainName\UserName','sysadmin'

5. Stop the sql Server service then re-start the service normally (whether through cmd or using SQL configuration manager)

6. Try to login using the user you just created into your SQL Server

Note: if you are using windows user authentication to login to SQL Server, make sure that particular user has a privilege to open SQL server application 

Happy Trying :D

Hope this is useful for you 

No GUI in Windows Server 2012

Unable to see the windows start button and other user interface where you normally see once you login into your Windows server and the only thing you see is only the command prompt (CMD) and the black background on the desktop

Whether you realize or not, it can happen for some reasons.

This can happen if start installing the OS using server core installation without GUI

Or you accidentally remove 'user interface and infrastructure' feature from server manager and when you are back to perform login windows....... the GUI is gone

Here I will show you how to bring back the GUI on Windows Server 2012.

Because the only tool you have is the command prompt, you will play around with it in order to bring the user interface back.

First try to run the command "SConfig" to see the server configuration

If you see the number stated "Restore Graphical User Interface (GUI)" like the picture below, well you are in luck, just type the number and restart the system then all the GUI will be back normally. 

But if you don't have that kind of information in server configuration as the previous screen shoot, don't have to worry , just go back to your earlier command prompt and run this script:

=====================================================================

dism /online /enable-feature /featurename:server-gui-shell /source:\\vmm\winsxs /all

====================================================================

When the it asks you to reboot the system after enabling the feature, press Y button then hit Enter

and it takes few minutes to start. 

Once the system is back in, and you login to the system, Clinggg.... the beauty of GUI is finally back as it suppose to be :"D

Are we done ?

I don't think so

There is one more step to do.....

If you go to windows start button and look for server manager, you will find nothing from the menu 

After bringing back the GUI of the server, we now have to bring back the server manager as well. In order to enable the server manager, I did run this script through powershell:

=======================================================================

install-windowsfeature server-gui-shell

=======================================================================

After you finish running the script above, try to go to start menu and look for the server manager.
Then, a magic happens doesn't it?.... The server manager now is there without rebooting the system :D

I found the way to solve this through this blog:

https://yungchou.wordpress.com/2014/12/09/changing-server-installation-option-from-server-core-to-server-gui-shell/

The article he wrote finally saved my life of the day after few hours searching on the internet trying to troubleshoot this issue :D

I also found nice article for you to practice in your own environment how to turn off the GUI and bring it back on using Windows Server 2012 using the link below:

http://www.howtogeek.com/111967/how-to-turn-the-gui-off-and-on-in-windows-server-2012/

Try to avoid doing this in the production environment though :D

Hope this will be useful and informative for you

Thanks your visiting my very first blog, Cheers :D 

Happy trying guys....