Running SQL Database Maintenance Tasks Using SQLCMD

This article is about developing an advanced understanding of the Sqlcmd utility which lets you run T-SQL commands directly from the command prompt without needing SSMS (SQL Server Management Studio).

The article also highlights the importance of using Sqlcmd to perform some advanced-level database tasks that would otherwise require additional steps, e.g. getting connected to the database via a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The Sqlcmd utility can be a great time saver for Database developers and DBAs since they can run the required SQL scripts right from the command line.

SQLCMD Basics Overview

Let’s go through some basics of the Sqlcmd utility if you’re not familiar with it.

Simple definition

Sqlcmd is a command line tool that lets you run T-SQL commands directly from the command prompt.

Microsoft definition

According to the Microsoft documentation, the Sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks.

Basic uses of SQLCMD

The following are some of Sqlcmd’s basic uses as covered in the Microsoft Documentation:

1. Sqlcmd can run T-SQL statements (at the command prompt)
2. Sqlcmd can execute user-defined or system procedures (at the command prompt)
3. Sqlcmd can also run saved SQL scripts files (at the command prompt)
4. Sqlcmd can connect to multiple SQL Server instances and run scripts
5. Sqlcmd can write the output of T-SQL statements to a text file

Please refer to my article ‘Basics of Running T-SQL Statements from Command Line using SQLCMD‘, which is a guide on implementing the Sqlcmd utility to perform some of the basic day-to-day T-SQL tasks.

Pre-requisites

This article assumes that you have the basic know-how of database maintenance tasks performed via T-SQL statements, as well as some basic understanding of the Sqlcmd utility.

This article also assumes that the sample database ‘University’ has already been created on your desired SQL instance.

My article ‘Basics of Running T-SQL Statements from Command Line using SQLCMD‘ will help you with getting a solid understanding of Sqlcmd basics before moving on to its more advanced uses.

Advanced uses of SQLCMD

Apart from the basic uses, Sqlcmd has the following advanced uses:

1. Sqlcmd can run database maintenance tasks
2. Sqlcmd can run database tasks on multiple SQL instances
3. Sqlcmd can automate database maintenance tasks
4. Sqlcmd can automate T-SQL scripts on multiple instances

Database Task 1: Creating a ReadOnly Database User

Let’s take a look at a very important database task of creating a user who only has read-only access for a database (we’ll call this user ‘ReadOnly’).

Requirement: adding ReadOnly user to the sample database

Imagine that a DBA or database developer has been tasked to add the ReadOnly user to a previously created database.

They are going to meet this requirement by using Sqlcmd.

Principle of Least Privilege and ReadOnly user

The purpose of creating the ReadOnly user is to comply with The Principle of Least Privilege, and according to the Microsoft documentation, you should always follow this principle when granting permissions to database users. Grant the minimum permissions necessary for a user or role to accomplish a given task.

So, in most of cases, we’ll need to create a database user in such a way that this user’s permissions are limited to only being able to read the database objects, with no ability to modify them.

This is also very helpful in database report and analysis scenarios, in which a database user that is going to access the data should only be given the rights to read the information. Granting more rights than required – such as permissions to add or drop objects – can pose security risks in this case.

Steps to create the ReadOnly database user

Usually, a new ReadOnly database user is created as follows:

1. Login to SQL Server with sufficient rights to create a new database user
2. Select the desired database
3. Create a new Login with Password in SQL Server
4. Create a new User for that Login
5. Grant read-only permissions to that User for the required database

We can illustrate this process as follows:

Already setup sample database (‘University’)

As mentioned above, this article assumes that the sample database ‘University’ has already been created.

Please refer to my previous article ‘Basics of Running T-SQL Statements from Command Line using SQLCMD‘ to create a sample database or use the following T-SQL code to setup a sample database called ‘University’:

[expand title =”Code“]

-- (1) Create the ‘University’ sample database

CREATE DATABASE University;

GO


USE University


-- (2) Create Course table

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Course')

DROP TABLE dbo.Course

CREATE TABLE [dbo].[Course] (

[CourseId] INT IDENTITY (1, 1) NOT NULL,

[Name] VARCHAR (30) NOT NULL,

[Detail] VARCHAR (200) NULL,

CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseId] ASC)

);


-- (3) Create Student table

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Student')

DROP TABLE dbo.Student

CREATE TABLE [dbo].[Student] (

[StudentId] INT IDENTITY (1, 1) NOT NULL,

[Name] VARCHAR (30) NULL,

[Course] VARCHAR (30) NULL,

[Marks] INT NULL,

[ExamDate] DATETIME2 (7) NULL,

CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC)

);


-- (4) Populate Course table

SET IDENTITY_INSERT [dbo].[Course] ON

INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (1, N'DevOps for Databases', N'This is about DevOps for Databases')

INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (2, N'Power BI Fundamentals', N'This is about Power BI Fundamentals')

INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (3, N'T-SQL Programming', N'About T-SQL Programming')

INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (4, N'Tabular Data Modeling', N'This is about Tabular Data Modeling')

INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (5, N'Analysis Services Fundamentals', N'This is about Analysis Services Fundamentals')

SET IDENTITY_INSERT [dbo].[Course] OFF


-- (5) Populate Student table

SET IDENTITY_INSERT [dbo].[Student] ON

INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (1, N'Asif', N'Database Management System', 80, N'2016-01-01 00:00:00')

INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (2, N'Peter', N'Database Management System', 85, N'2016-01-01 00:00:00')

INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (3, N'Sam', N'Database Management System', 85, N'2016-01-01 00:00:00')

INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (4, N'Adil', N'Database Management System', 85, N'2016-01-01 00:00:00')

INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (5, N'Naveed', N'Database Management System', 90, N'2016-01-01 00:00:00')

SET IDENTITY_INSERT [dbo].[Student] OFF


GO

[/expand]

Using Sqlcmd to add ReadOnly user

First of all, you will need to invoke the Sqlcmd utility through ‘Run command’ window after making sure you have sufficient rights to create a database user.

Sqlcmd -S PC

As a result of running this command, you will get connected to the default unnamed SQL instance – if you have one. Otherwise, please mention <ComputerName>\<InstanceName> to connect to the desired SQL instance.

Once you’re connected to the desired SQL instance, add a new database user with read-only rights called ‘ReadOnly’ to the ‘University’ database by using the following code:

USE University

CREATE LOGIN ReadOnly with Password ='b1GS3crt00'

CREATE USER Readonly for login readonly

exec sp_addrolemember db_datareader,ReadOnly

GO

Check the newly created database user (ReadOnly)

Open SSMS (SQL Server Management Studio) and connect to the SQL Server Database Engine using the following credentials:

User Name: ReadOnly

Password: b1GS3crt00

As a result, you will be connected to SQL Database Engine.

When you’re connected to the SQL Database Engine, expand the Databases node in the Object Explorer and click on the University Database.

Next, choose Users under Security to see ‘ReadOnly’ database user as follows:

Database Task 2: Dropping Database ReadOnly User using SQLCMD

This database task is about dropping a user from a database via the Sqlcmd utility.

Requirement: drop ReadOnly user from the sample database

Consider a new requirement – dropping a database user with read-only access from the sample database.

Steps to drop ReadOnly database user

The general process of dropping the newly created ReadOnly user along with its Login is as follows:

1. Login to SQL Server with sufficient rights to create and drop a database user
2. Select the desired database
3. Remove ReadOnly user from the Data Reader role
4. Drop ReadOnly user from the database
5. Drop ReadOnly Login from SQL Server

Using Sqlcmd to drop ReadOnly user (with Login)

Please ensure that all other connections to the ‘University’ database through ReadOnly user are closed.

Connect to the SQL instance using Sqlcmd and run the following code at the command prompt to close all current sessions for the ReadOnly login (when connected to the SQL instance which was previously used to create the ReadOnly user)

SELECT session_id FROM sys.dm_exec_sessions where login_name='ReadOnly'

GO

You will need to check which session_id is returned and drop the connection based on that session_id:

KILL 55

GO

Please note that ‘55’ is used here as an example. You will have to drop whatever session_id you are getting on your machine when connected to the desired SQL instance.

Next, run the following code to drop the database user and login:

Use University

EXEC sp_droprolemember 'db_datareader', 'ReadOnly'

EXEC sp_dropuser ReadOnly

EXEC sp_droplogin ReadOnly

GO

As a result, the database user ReadOnly should be successfully dropped.

Check the dropped user (ReadOnly)

Try to connect to SQL Server using the following credentials:

User Name: ReadOnly

Password: b1GS3crt00

The failed connection attempt resulting from trying to connect to SQL Server using ReadOnly login proves that this login and database user have been successfully removed.

In this way, with the help of Sqlcmd, we have removed a database user with read-only access by simply running some code at the command prompt without the need to use SSMS (SQL Server Management Studio).

Database Task 3: Creating A Database User with read-only access in Multiple SQL Instances

Let’s explore the benefits of using an SQL script file called through the Sqlcmd utility to add a database user with read-only access to multiple SQL instances for the sample database.

Requirement: Add ReadOnly user for multiple instances for sample database

As a database developer or DBA, you are tasked to add a database user with read-only access to the sample database for multiple SQL instances via a script using Sqlcmd.

Pre-conditions

This task assumes that there are at least two SQL instances installed on your machine and both of them have the sample database ‘University’, while a database username ReadOnly (with read-only access) needs to be created for both databases.

Creating a sample database on another SQL Instance

Please skip this step if you have already created the ‘University’ sample database on a second SQL instance.

Let us first connect to another SQL instance by running the following code in the Run Command window:

sqlcmd –S <computername>\<sqlinstancename>

Once you are connected to the desired SQL instance, please use the code from the beginning of the article to create the ‘University’ database (or refer to my article ‘Basics of Running T-SQL Statements from Command Line using SQLCMD‘).

Create a script file to add ReadOnly database user

Copy the script below and save it in Notepad as AddReadOnyUniversityDatabaseUser.sql file in C:\SQLScripts folder for testing purposes.

-- This script creates a database user named ReadOnly with read-only access for the University database

USE University

CREATE LOGIN ReadOnly with Password ='b1GS3crt00'

CREATE USER Readonly for login readonly

exec sp_addrolemember db_datareader,ReadOnly

Connect to the first SQL instance and run the script through Sqlcmd

Open the Command Prompt by typing ‘cmd‘ in the search box. Then, run the following line:

Sqlcmd –S . –i c:\SQLScripts\AddReadOnyUniversityDatabaseUser.sql

Where “.” (dot) signifies a default unnamed SQL instance which can be replaced by a specific SQL instance of choice.

Connect to the second SQL instance and run the script through Sqlcmd

Next, open the command line and use the following code to run the SQL script which will create a database user on another SQL instance:

Sqlcmd –S .\SQLTAB –i c:\SQLScripts\AddReadOnyUniversityDatabaseUser.sql

Please note that ‘SQLTAB’ should be replaced this with the name of an SQL Instance installed on your machine.

Congratulations! You have successfully created a database user with read-only access for the sample database on multiple SQL instances simply by running a script file using the Sqlcmd utility.

We have learned to simplify various database tasks by using Sqlcmd in both direct mode (typing T-SQL code once connected to a desired SQL instance) and indirect mode (when Sqlcmd runs a script against a database on a desired SQL instance from the command line).

Things to Do

Now that you are ready to run database tasks such as adding a database user with read-only permissions using the sqlcmd utility, you can improve your skills further by trying the following things:

1. Try to create a database user with read–only access for the sample database SQLBookShop mentioned in my article excluding the stored procedures mentioned afterwards the description of using the sqlcmd utility.
2. Try creating a database and a database user with read-only access via a script file run through the sqlcmd utility for the SQLBookShop database including any one of the stored procedures mentioned in my article.
3. Create a script file and test run it using the Sqlcmd utility to remove a previously added read-only user from the sample database University on multiple SQL instances.

Useful tool:

dbForge Studio for SQL Server – powerful IDE for SQL Server management, administration, development, data reporting and analysis.