SQL Server – Using SSMS Commandline Parameters – Guest Post by Vinod Kumar M

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 Tips and 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.

  1. We find the “SQL Server Management Studio” icon from the start menu and will click on it.
    1. We can also type “ssms” in the run prompt to quickly launch. This is my favorite and I use almost daily.
    2. After Management Studio launches we need to give the Authentication mode. Let us assume we use Windows Authentication and we press “Enter” to login.
    3. This initializes the Object Explorer with the Login.
    4. 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.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

19 thoughts on “SQL Server – Using SSMS Commandline Parameters – Guest Post by Vinod Kumar M

  1. HI Folks

    Sorry I cannot get to this blog from work so cannot post

    I assume this is NOT for Sql Server 2005 as I cannot find ssms.exe

    Thanks
    Jim Cox Jr.
    SMC Livelink Database Administration
    General Dynamics (AIS)
    [email removed]
    *******************************
    -Display Message as HTML, if available-
    *******************************

  2. 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?

  3. Pingback: SQL SERVER – Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049 | SQL Server Journey with SQL Authority

  4. How to bypass this login prompt altogether. I need it to be able to open a lot of files without nuisance of cancelling login prompt for each of the files.

  5. Hey Vinod, thanks for the post!

    Using ssms -s [server] multiple times opens a new instance of ssms.

    Do you know if there’s a way that you can use the ssms -s [server] parameter to open the connection within a existing instance?

    Thanks

  6. That is a very good tip especially to those fresh to the blogosphere. Short but very precise information… Appreciate your sharing this one. A must read article!

  7. 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s