How to script SQL Server database users with roles

Introduction

A request is received from the development team asking to copy one of the production databases to the DEV SQL Server in order to simulate real time testing scenarios on it. Once the production database backup is restored to the DEV SQL instance, replacing the old copy, the old DEV database users will be replaced with the live ones. But we still need the old DEV database logins for the DEV site connection.

How to format all SQL objects in your database

The Format SQL objects feature allow formatting one or more database objects with the specified formatting profile, without having to script them first.

There are three ways to invoke the Format SQL objects feature. First, you need to select a database from which you want to format objects. Otherwise, when you try to initiate the Format SQL objects feature, you’ll be prompted with the following message:

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 4

In part 1 and part 2 of this series information was provided on how to configure ApexSQL Audit to accomplish PCI requirements from 3 and up to 8, while in part 3 the addressing requirements 10.1, 10.2 and 10.3 of the PCI DSS 3.1 standard via ApexSQL Audit was explained

In this part, the rest of the PCI DSS 10-Track and monitor all access to network resources and cardholder requirements section will be described and as well as some requirements from section 12 that can be met using the ApexSQL Audit. This article is based on the latest PCI DSS 3.1 compliance regulation

How to restore only a specific filegroup – A piecemeal restore

What is a piecemeal restore?

A piecemeal restores allow the user to restore only a specific filegroups from a database instead of restoring a whole database. In cases of emergency the important thing is to get the needed data as quickly as possible, and restore the rest of the data later while having the most significant data online. This approach minimizes the downtime when a disaster occurs.

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 3

Part 1 and part 2 of this article described configuring ApexSQL Audit to meet PCI DSS 3.1 standard developed to ensure security of cardholders’ payments and data. In this part, the requirement sections 10-Track and monitor all access to network resources and cardholder will be described. For more details about differences between PCI DSS 2.0 and PCI DWW 3.0 and differences between PCI DSS 3.0 and PCI DSS 3.1, check the official PCI Security Standards Council LLC documents Summary of Changes from PCI DSS Version 2.0 to 3.0 and Summary of changes from PCI DSS Version 3.0 to 3.1

SQL Server database shrink – How and when to schedule and perform shrinking of database files

From time to time, DBAs find themselves in a situation where a SQL Server database becomes too large for their production environment, and needs to be shrunk in size in order to free space and allocate it back to the system.

Before shrinking a SQL Server database or database files, it is very important to understand how the process works and what are the immediate consequences of the shrinking process.

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 1

The PCI DSS (Payment Card Industry Data Security Standard) is a multidimensional security standard designed as a set of technical and operational requirements to protect data of credit card holders. The PCI DSS applies to all entities that store, process or transmit cardholder data, including software developers of applications and devices manufacturers when used in those transactions

This standard originated in 2005 and was created by the PCI SSC (Payment Card Industry Security Standards Council) organization. The PCI SSC organization is founded by American Express, Discover Financial Services, JCB International, MasterCard Worldwide, and Visa, Inc., with a goal to improve security of payment account data via the PCI Security Standards

How to continuously pump transaction log file audit data directly to a SQL Server database

In Automating daily transaction log reading, we’ve shown how to automate the process of pumping transaction log data into SQL Server tables with ApexSQL Log. The proposed solution revolved around the creation of a batch file which runs ApexSQL Log CLI commands. The batch file is then scheduled with a Windows scheduler to run on a daily basis. The result is a regular daily update of 2 tables, specifically created by ApexSQL Log to hold audited data, which are populated with fresh auditing results each night.