Category Archives: Tips

SQLCMD mode in SSMS

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

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.

Go to Query in main menu, then click SQLCMD mode to enable it

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 very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

SQL Vulnerability Assessment

Vulnerability assessment is probably one of the most underrated new security features in SSMS 17. This feature is not new to SQL Server though, it already exists on Azure SQL DB but it was finally introduced in SSMS with the version 17.

If you are supporting a SQL Server environment where you have to face an exhaust external or internal IT audit process every quarter or perhaps monthly basis, I think this tool is great helping you to being proactive making you aware of all the security changes or threats you must remediate before auditors arrive to your desk asking for questions.

The only requirement for this new SSMS feature, is to have the 17.4 or latest SSMS version installed on your client machine, and a valid path to store the vulnerability scan result:

According to Microsoft, the VA (let’s use the short name from now on) runs lightweight scan directly to your SQL Server instance or a specific database, looking for security vulnerabilities and deviations from best practices, such as misconfigurations, excessive permissions, and exposed sensitive data. It also checks for security best practices to be in place in order to make your database environment safe.

The VA results contains all the security checks (passed or failed) , organized in three categories: high, medium and low risk.

Report header:

Security checks detail:

If we take a look at the example above, we can see how the Vulnerability assessments provides guidance on how to resolve the failed checks, in case the security check doesn’t apply to your environment you can dismiss such security check and it will no longer appear on future checks.

Let’s dig a little bit more about the security check ID VA1287 – Sensitive data columns should be classified:

As you noticed, the screenshot has a highlighted section where the scan result shows a couple of table columns not in compliance with GDPR. As you may know GDPR is s a regulation in EU law on data protection and privacy, which goes active on 05/25; so this is one major reason why you should  consider using the VA tool.

If you scroll down to the bottom of the AV1287 check, the report provides a detailed description and the impact of having this vulnerability active in your environment:

So, at this point you have your scan results with the description and impact of each one. Now let’s take a look what the tool recommends as remediation:

As you can see there is no rocket science here, the vulnerability scan recommends to implement security measures as Always encrypted or Dynamic Data Masking (SQL Server 16 +) to protect the sensitive information in your environment, and the most important part it helps you to stay away from trouble with your internal or external IT audit.

Let’s not forget about the baseline option:
This is very useful option to avoid false positives, let’s say you determined one of the scan results does not apply in your environment and you don’t want to have it in this report. The solution is click on the “Approve as baseline”, so the next scan will show the same result marked as passed.

Thanks for reading!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

Reviewing the SQL Server ERROR log

 

The SQL Server ERROR log is the best source to understand what’s wrong with your SQL Server instance, think this log is like your flight recorder.

In case you are not too familiar with the SQL Server ERROR log, the xp_readerrorlog extended stored procedure is your friend; and as a good friend you must get to know him. In this post, you will find some examples of how to take advantage of this undocumented stored procedure and maximize your time avoiding the utilization of slow and painful tools like Event Viewer from Windows.

First thing first, let’s understand what are the parameters we need to provide to the stored procedure and the most important how we have to use them.

Archive ID:
The first parameter is is about picking the order of the log we want to query:

  • Current log = 0
  • Archived error log #1 = 1
  • Archived error log #2 = 2

Log type:
Then, the second parameter is the SQL Server component we are focusing in:

  • SQL Server Instance Log = 1
  • SQL Agent Log = 0

So, combining these two parameter we can check the current log (0 in the first parameter) for a SQL Server instance (1 in the second parameter) or the SQL Server Agent (2 in the second parameter)

Here is how it looks like in T-SQL code:

-- Checking the current SQL Server instance and SQL Agent logs:
xp_readerrorlog 0,1 -- Remember 1 for SQL Server instance
xp_readerrorlog 0,2 -- Remember 2 fro SQL Server Agent

We also have an optional parameter we can include, it’s a simple search string. This parameter it’s really handy when you are looking for a specific event.

Here are some T-SQL examples how we can combine the first two parameters with a specific search string:

-- Checking for failed logins in our SQL Server instance:
xp_readerrorlog 0,1,'Login failed' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"Login failed" --For SQL Server 2014 onwards

-- Checking for TempDB related entries in our SQL Server instance:
xp_readerrorlog 0,1,'TempDB' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"TempDB" --For SQL Server 2014 onwards

Hopefully this tip could help you to save some time when looking at your SQL Server instance for recent errors or some events from the past that you might need to collect for further analysis.

Stay tuned for more DBA mastery tips.

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.