Scenario

You have got the SQL database engine installed but no SSMS installed on it. In this scenario how you will connect to the target database engine and create a login to allow a connection or how will you connect to SQL database engine without SSMS.

Solution

Create a login using the command prompt.

The below steps are to create a login using Command Prompt.

Open CMD and type the below command to connect to the Database engine

sqlcmd -S FDQN\INSTANCE,port

then hit enter

if you see “1>” in the next line, then you are now connected to the SQL engine.

Now type the below command to create the login and give a permission

CREATE LOGIN [you_login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

hit enter.
Then type the below command to add the “SYSADMIN” permission.

ALTER SERVER ROLE [SYSADMIN] ADD MEMBER [your_login_name]

then type

GO

and hit enter.

Screenshot for your reference:

Now this created a login for you and you can connect to the Database engine from another server

Now verify if the login is created

Open CMD and type the below command to connect to the Database engine

sqlcmd -S FDQN\INSTANCE,port

Now type below command to see if your login got created.

SELECT name from sys.syslogins

You will get the list of logins present on this SQL instance as shown in the below screenshot.