Vinod Kumar M is my very good friend renowned SQL Server Expert. Vinod Kumar has worked with SQL Server extensively since joining the industry over a decade ago. Before joining Microsoft, he was a Microsoft MVP for SQL Server for more than 3 years. He now works with MTC as a Technology Architect. He is a well-known speaker at all major Microsoft and third party technical conferences. Here is a very interesting blog post he sent on the subject of SSMS Command Line Parameters. Don’t forget to visit Vinod’s blog for additional SSMS Tipsand follow him on twitter.

Using SQL Server Management Studio is something we do instantly after connecting to our mailing system everyday if we are a SQL Developer or SQL Administrator. Though this is a very common step, there can be optimizations we can make even in this mundane yet simple task. Let us look at the steps we take normally.

We find the “SQL Server Management Studio” icon from the start menu and will click on it.

We can also type “ssms” in the run prompt to quickly launch. This is my favorite and I use almost daily.

After Management Studio launches we need to give the Authentication mode. Let us assume we use Windows Authentication and we press “Enter” to login.

This initializes the Object Explorer with the Login.

Right click on Database or Server node to open a query window.

As we can see that even though this is a common activity has taken close to 4-5 clicks. How about making this efficient? If you didn’t know we have some hidden command line parameters available with SQL Server Management Studio. To know the parameters, from the run prompt just for this command.

C:\> ssms /?

This brings the list of parameters we can use. The figure below shows the same.

To tabulate the various options:

Command Line Switch

Description

S

The name of the SQL Server instance to connect to.

d

The name of the SQL Server database to connect to.

U

The name of the SQL Server login to connect with.

P

The password associated with the login.

E

Use Windows Authentication to login to SQL Server.

noSplash

Suppress splash screen.

?

Displays this usage information.

file_name

File to load upon startup. Good for loading a .SQL file directly.

Having known all the parameters, here is the tip we would like to give you. Fire Management Studio using the authentication in one shot using the following command.

C:\> ssms -E

In this case we have used the Windows Authentication to login. We can replace the same with –U and –P parameters for SQL Authentication. Feel free to use the –d option to connect to a specific database.

Another tip, if you want to change the default behavior of the shortcut, then right click the shortcut and select properties. Add the –E or other appropriate options to your desire.

After adding the option. Click the “Ok” button. Now when we fire the Management Studio icon, this will open the management studio with an active Query window ready to do the work.

We would love to hear how many of you have played around with these parameters in the past? Have you enabled these in your Laptop’s or machines you work daily? Do drop us a comment about the same. I did talk about few SSMS Tips earlier over my blog and thought will be worth mentioning here.

Great tip.
I’d love to see SSMS support multiple start-up connections. In other words if you use the -S -d options more than once, it will utilize all provided settings to connect to numerous servers. Maybe in the future a -S1 -S2 feature?

This is also the problem for me. I work on an application that needs to be able to open multiple files (scripts) in same ssms instance. But I could not find the proper cmd command to do so. Did you had any luck in finding the solution? It would be great if you wold share that.
Thanks in advance.

Having read this I thought it was rather informative. I appreciate you spending some time and effort to put this article together. I once again find myself spending way too much time both reading and posting comments. But so what, it was still worthwhile!

Hi, sorry to leave such a late reply, I’m just hoping someone can help me. I daily have to execute several queries, across several different servers (I work for a huge supermarket chain). I’ve made a .bat to open all these queries on ssms, but is there a way to make them each connect to a different server? I’ve used the -s parameter but it takes the last -s and applies it to all the queries. Thanks a lot!

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.