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
