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.
Siddhartha
In mentioned scenario how you weil able to connect through sqlcmd prompt if you don’t hat access target server???
If you are able to connect through sql cmd prompt which means you has access to connect target server so obviously you will able to connect sql through ssms.
Soyeluddin Biswas
Thanks Siddhartha for your comment. My intention was to create a login using Command Prompt. I have updated my context.