I really like Microsoft philosophy nowadays, they are putting a lot of time developing good open source tools like SQLCLI, SQLOps and Visual Studio Code, those tools has something in common “a built-in terminal” a multi-platform and code geek like me really appreciate those details. It is pretty neat to use the built-int terminal to do stuff without leaving your development environment. This functionality is also available on SSMS (in some way), it is called SQLCMD mode.
This feature allows you to interact directly with Windows command line without leaving your SSMS environment, for example long time ago I had the need to generate a SQL file from an output of a query, I knew I can use SQLCMD for such thing but I didn’t want to create an external script to be call from command … so I did my research and figure out SQLCMD mode in SSMS can do such thing without having to run outside SSMS.
Here is how you can enable SQLCMD mode in SSMS:
Alright! so, now let’s do a quick example. Imagine you don’t want to lose the list of roles assigned to all your database users for a specific database, in order to script out the roles we will run a query and generate an output directly to Windows having the T-SQL script ready to run when needed.
Here is the T-SQL code:
BEGIN SET NOCOUNT ON :OUT F:\MyDB_Roles.sql DECLARE @Role varchar(255) SET @Role= '%' -- Add Users to roles SELECT 'EXEC sp_addrolemember ''' + RTrim(r.name) + ''',''' + RTrim(Coalesce(l.name,u.name)) + '''' as '-- Add Role Users' FROM dbo.sysusers u INNER JOIN sysmembers m ON u.uid = m.memberuid INNER JOIN sysusers r ON m.groupuid = r.uid LEFT OUTER JOIN master.dbo.syslogins l ON u.sid = l.sid WHERE r.issqlrole = 1 AND u.name <> 'dbo' AND r.name LIKE '%' ORDER BY r.name,u.name PRINT '-- End of script' END
Note the third line, I’m using the :OUT
command which will take care of redirecting the query result into a SQL file within Windows:
:OUT F:\MyDB_Roles.sql
Once the query is executed, here is how the SQL File output file looks from Visual Studio code:
As you can see this is a normal T-SQL script generated from SSMS, it contains the roles we wanted to script out from our database. As you may realize by now this is a very handy option in SSMS, and to be fair with SQLCMD mode this is just a simple use case there is more scenarios where this SSMS functionality can help you to achieve very neat things.
For more information about other parameters for SQLCMD, please check this link from Microsoft docs.
Stay tuned for more DBA mastery tips!
I’m a Microsoft Data Platform MVP also a very experienced multi platform DBA (MySQL, Oracle, SQL Server, SQL Azure) with over 10 years of experience in the database field.
I work in database support as primary consultant and DBA manager for larger US based companies in the healthcare, insurance, retail, food and energy industries.
I have a few certifications under my belt MCSE, MCSA, MCP, MCTS, ITIL v3, Docker, Kubernetes and RedHat openshift.
International speaker, blogger, Guatemala SQL community group leader, mentor.
Love everything related to technology, sci-fi and video games.
The greatest value in SQLCMD is the :connect directive and the use of cmdvars.
There’s an option to make SQLCMD the default mode for new queries. Everyone should turn that on!
Absolutely!! This post was just quick note about how to enable it nothing fancy yet. I totally agree with you, it can be turned on as default when SSMS starts and definitely it’s a MUST 😀
Heh… and people say that xp_CmdShell is a security risk. 😉
I agree with you xp_cmdshell it’s a security risk 🙂
SQLCMD mode has a limited set of commands instead of full access to the whole OS.
Xp_cmdshell by default can only be executed by a login with the sysadmin role. If xp_cmdshell is a risk you’re either already hacked (someone has sysadmin that shouldn’t) or you have untrustworthy people with sysadmin role.
Turning it off by default was nothing more than a feel-good move. Only sysadmins can execute it by default, and who can enable it? Oh yeah, sysadmins. Essentially, on or off, the risk is the same.
Even if it wasn’t there the risk would be the same because sysadmins have access to the entire windows API through TSQL.
Sorry. I missed your reply.
There was tremendous irony intended in my post. xp_CmdShell is not a security risk. The only people that can enable it are the same people that can use it… unless you’ve made the horrible mistake of giving a non-sysadmin privs to use it directly, only sysadmins can use it. Turning it off will not prevent an attacker that “got it” with sysadmin privs from using it. Turning it off is to deprive real sysadmins of a powerful tool that can allow them to do their jobs much more quickly and effectively. Combined with the command line level SQLCmd, some absolutely amazing things can be do that will put the likes of SSIS to shame.