How to decrypt stored procedures on multiple SQL databases

ApexSQL Decrypt main grid with loaded SQL databases

Problem

Before we show how to decrypt stored procedures, we need to give some background about this concept. When stored procedures are built in the SQL Server, their body text is reachable to all that have required permissions to access the data. Because of that, it’s simple to expose underlying content during created stored procedures and analyze that content through the SQL Server Management Studio, Windows PowerShell, or any commercial tool. As a result of data transparency, a SQL database can become vulnerable and compromised by any type of cyber-criminal activity. This is where encryption comes into place.

March 3, 2021

How to export SQL Server data to a CSV file

The Job summary window

This article will explain how to export SQL Server data to a CSV file type. CSV represents a file format where plain text values are separated by a delimiter, usually comma symbol (Comma-Separated Values). It is one of the most common format types for any spreadsheet program. CSV files are easy to organize and edit, and can be used by many different business software programs for data analysis. Large amounts of data can be better organized with CSV files, and those files can be easily edited and created by different spreadsheet applications.

February 26, 2021

Using Ignore comparison script options to define database changes

Changes shown in the Action center tab

Challenge

Database development in a multiuser environment has its challenges on a daily level even without including the database source control. But when the database source control is included, based on the chosen source control system, those challenges can multiply by the number of database developers working on the same database.

February 19, 2021

How to script SQL objects, format, and run SQL scripts on multiple SQL Server instances

The Script button under the Home tab

A part of DBA’s and developer’s role is to migrate SQL Server databases between hosts or migrate from a local development environment to a live hosted environment. It requires copying all database objects such as database schema, tables, stored procedures, functions, indexes, etc. For this, different methods can be used depending on a requirement such as a database backup restore, object SQL scripts, BACPAC files, etc.

January 12, 2021

How to query data using a SELECT statement in SQL Server

Highlighted aliases in a query

The SELECT statement in SQL is the most used of the DML statements and it’s used for fetching data from tables in SQL Server. This article is intended for DBAs and developers who want to get familiar with all the major components of the SELECT statement and what this statement is all about.

January 4, 2021

How to use Dynamic data masking on Azure SQL Database

The preview window in the main window of ApexSQL Mask

SQL databases can contain a lot of data, including a lot of sensitive data. This sensitive information can easily be misused. Mask SQL Server data is of great importance when the SQL database contains a lot of sensitive data. Microsoft has offered a dynamic data masking feature that has been implemented in SQL Server 2016 and later versions, as one of the solutions for protecting sensitive data in SQL databases.

December 22, 2020

How to track data changes using database auditing techniques

ApexSQL Audit - before after data changes report

Tracking data changes helps us to understand moving parts in a database, learn about changing trends, easily recover from unwanted data changes or data loss, and adds more context to the auditing documentation. In this article, we will introduce ways of tracking database changes by exploring some out of many database auditing mechanisms in the SQL Server ecosystem.

November 27, 2020

How to automatically revert database changes based on SQL auditing alerts using SQL Server transaction logs

Generating CLI script for generating Undo script from SQL Server transaction log

One of the problems that can be solved with SQL auditing technologies in conjunction with SQL Server transaction logs is finding specific events, i.e. specific database or data change, capturing the event, and resolving it as wanted or unwanted. Depending on methodology and tools this can be an easy or complicated task. The ApexSQL Audit tool provides the solution wherein a few easy steps a specific event can be audited, with high granularity and precision. The additional problem surfaces out in case unwanted change occurs, how to easily revert those changes? The usual methodology consists of creating SQL Server transaction log backups and restoring them. But restoring implies reverting the database to a specific moment in time, thus reverting even wanted changes.

November 17, 2020

How to collect information about SQL audits and database mail using SQL documentation

Database Mail object type

Manual replication of a SQL Server is still nothing out of the ordinary, so keeping some SQL documentation about the ‘original’ SQL Server is recommended to use as a reference. SQL Documentation can come in handy especially when server level objects have to be reconstructed manually on the new instance, for example, SQL audit settings or database mail. Having the T-SQL scripts also with this documentation for recreating those objects will certainly simplify the actions needed for the task.

November 17, 2020

How to search for SQL Server data

Database text search pop-up window

In day-to-day work, DBAs and developers often have to search SQL Server data especially tables that contain specific data. In SQL Server Management Studio (SSMS), or Visual Studio, there is no simple solution to performing such tasks.

November 16, 2020

How to mask SQL Server data using Dynamic data masking

The main grid of ApexSQL Mask, in which will be selecting columns to mask SQL Server data

In the world of today, information can be classified into sensitive and non-sensitive data. The sensitive data is classified as data that contains private, personally identifiable, financial, and security information, whose discovery can be misused and damage the reputation of a person or an organization.

November 2, 2020

Different methods to search SQL Server data

Text search command from ApexSQL main menu

One of the most basic and repetitive operations in the SQL world is to search SQL Server data. Usually used to locate specific row items, sometimes for exporting purposes, or check if dependent values are properly populated.

October 22, 2020

GDPR survival kit for SQL Server DBAs – SQL Server auditing and best practices

SQL Server Audit report

In the current digital age, security controls and data privacy and protection has been an influential factor in how organizations over the globe store and protect business data, especially since the EU General Data Protection Regulation (GDPR) establishes a new bar for compliance, security, and privacy rights of the individuals.

October 2, 2020

How to perform a custom SQL Server security health check analysis

Results of Acceptable Analysis Services Login Account rule execution

The overall security of SQL Server instances is a broad topic. Security breaches happen often, and, in most cases, they are resolved quickly, and the level of security is tightened, but the best and most desirable scenario is to avoid them entirely. This can be done by taking proactive actions in analyzing possible security risks and patching them before the least desirable scenario happens. With SQL Server environments being dynamic, there is an almost constant need for better security and management of SQL Server instances.

September 25, 2020