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.
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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
26 Comments. Leave new
Hi Pinal/Vinod
This is fantastic & tricky
I never used this before
Thanks
Nice post. Good alternative way to start work quickly.
It is very use full for me to open the SQL server in remote system thanks for this
Yes, if your day job involves in opening remote server by default. Then feel free to use -S (servername) -U (username) -P (password) options in the shortcut. It works like gem there !!!
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-
*******************************
Jim – For SQL Server 2005, use sqlwb.exe is the shortcut I think. The -E parameter still works there.
Hi Vinod,
It is working fine but object explorer is loading automatically..
Thanks,
Vijayakumar
Yes. You can also change that default view. Check my tips II at my blog at:
Nice Post….Really helpful.
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?
Hi – i have both 2008 and 2012 installed. When i do SSMS it always opens up 2008. Is there a different command for 2012? Thanks.
Any command line to shutdown SSMS?
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.
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
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.
fantastic post.. every day I need to use so really helpful
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!
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!
If you are going for most excellent contents like me, just pay a visit this web page everyday because it presents feature contents, thanks
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!
how does you .bat file looks like? Why don’t you use SQLCMD command line rather than SSMS?
The best option would be for you to use a powershell script. you can open SSMS and connect to any database and then open any scripts that you need to open on that server.
I’m trying to open a stored procedure dynamically from an external program so I can quickly make changes. How do I get SSMS to open a new query tab instead of launching a full instance of SSMS? I tried using /Edit and a bunch of hacks like that to get it to work, but I haven’t had any success.
I tried all these workaround but no help yet.
Hi Vinod,
Is there any way, we can use the current open instance and just connect the DB with this command, or going to one more step, if the ssms is open and it’s connected to desired DB just open new query window,
something like this, is it possible?
Kind Regards
Bharat
The -P password options seems to have been removed in SSMS 18, which is rather puzzling because the -U user name option is still there.
Is there any way to execute that input file (not just to open in ssms but execute it)