Restrict User Query Access in SQL Server

Problem

I’ve long believed that granting users access to a production operational database without understanding the performance implications of writing queries is a bad idea. You might argue, “We’ll only give them access to a single view,” and that might be true, but they can still consume most of the CPU and bring operations to a halt. What are the other options? Ultimately, users need access to the data. Learn how you can restrict access in SQL Server yet still give users access to what they need.

Solution

In this article, I aim to explore what a user can achieve when granted access to a single view for pulling data into their reports. We’ll start by setting up a demo environment and exploring a simple query that this user can run to consume most of the server’s CPU. Next, I’ll share a few workarounds that ensure our SQL Server remains secure while delivering the essential data to the user.

Everybody Loves Querying SQL

Once you have an operational database in place, the most important aspect is capturing data. After you’ve captured the data, users want to view it. However, retrieving data should never come at the expense of capturing it. I’ve seen database owners grant db_datareader access to a large group of people who don’t even know how to write an efficient T-SQL statement or have any desire to learn. I understand users want and even crave this precious access, but if I gave in to my Ben and Jerry’s cravings every night, I would soon need new pants.

One could argue that permitting users to query a single view minimizes the risk of adverse effects on the server. I agree that you should try to reduce the number of objects a user can access for several reasons. One reason that comes to mind is that you are limiting the amount of potentially sensitive data they can expose. In the upcoming sections, you’ll see how providing access to a single view for a user can lead to devastating performance issues.

Video Version

Setting Up an Environment

Using the code below, I’ll create a database that includes a table called Customers. Our Customers table will store one million records.

/*
* MSSQLTips.com
* Create one database and one table with one million rows
*/
USE [master];
GO
 
IF DB_ID('ServerStressDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ServerStressDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ServerStressDemo;
END;
GO
 
CREATE DATABASE ServerStressDemo;
GO
 
ALTER DATABASE ServerStressDemo SET RECOVERY SIMPLE;
GO
 
USE ServerStressDemo;
GO
 
CREATE TABLE dbo.Customers
(
    CustomerId INT IDENTITY(1000, 1),
    CustomerNumber VARCHAR(50),
    FirstName VARCHAR(260),
    LastName VARCHAR(260),
    Region VARCHAR(260),
    HiddenColumn INT,
    CONSTRAINT PK_Customers
        PRIMARY KEY CLUSTERED (CustomerId),
    CONSTRAINT UC_CustomerNumber
        UNIQUE (CustomerId)
);
GO
 
INSERT INTO dbo.Customers
(
    CustomerNumber,
    FirstName,
    LastName,
    Region,
    HiddenColumn
)
SELECT TOP 1000000
    CONCAT('C-', CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR)),
    CONCAT('CustomerFirst', CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR)),
    CONCAT('CustomerLast', CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR)),
    CASE
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 4 = 1 THEN
            'North'
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 4 = 2 THEN
            'South'
        WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 4 = 3 THEN
            'East'
        ELSE
            'West'
    END,
    '42'
FROM sys.all_columns s1
    CROSS JOIN sys.all_columns s2;
GO

Next, I’ll create two additional objects:

A SQL view created from our Customers table. A view acts like a saved query and is useful for restricting a user’s access to the underlying table(s).
A user who only has select access to the SQL view.

/*
* MSSQLTips.com
* Create a sql view without the hidden column
*/
CREATE OR ALTER VIEW dbo.vw_Customers
AS
SELECT CustomerNumber,
       FirstName,
       LastName,
       Region
FROM dbo.Customers;
GO
 
CREATE LOGIN TestUser WITH PASSWORD = 'IB3tYouCantGu3ssTh1sPassw0rd';
GO
 
CREATE USER TestUser FOR LOGIN TestUser;
GO
 
GRANT SELECT ON dbo.vw_Customers TO TestUser;
GO

Now, let’s ensure our TestUser cannot query the Customers table.

/*
* MSSQLTips.com
* Make sure we cannot see the dbo.Customers table
*/
EXECUTE AS USER = 'TestUser';
 
SELECT CustomerNumber,
       FirstName,
       LastName,
       Region,
       HiddenColumn
FROM dbo.Customers;
 
REVERT;
GO

Output:

Msg 229, Level 14, State 5, Line 98
The SELECT permission was denied on the object 'Customers', database 'ServerStressDemo', schema 'dbo'.

This time we’ll query the vw_Customers view.

/*
* MSSQLTips.com
* Make sure we can query the view vw_Customers
*/
EXECUTE AS USER = 'TestUser';
 
SELECT TOP 10 CustomerNumber,
       FirstName,
       LastName,
       Region
FROM dbo.vw_Customers;
 
REVERT;
GO

Output:

CustomerNumber FirstName            LastName             Region
-------------- -------------------- -------------------- ------
C-1            CustomerFirst1       CustomerLast1        North
C-2            CustomerFirst2       CustomerLast2        South
C-3            CustomerFirst3       CustomerLast3        East
C-4            CustomerFirst4       CustomerLast4        West
C-5            CustomerFirst5       CustomerLast5        North
C-6            CustomerFirst6       CustomerLast6        South
C-7            CustomerFirst7       CustomerLast7        East
C-8            CustomerFirst8       CustomerLast8        West
C-9            CustomerFirst9       CustomerLast9        North
C-10           CustomerFirst10      CustomerLast10       South
 
(10 rows affected)

The results are as I expected. The user can only query the view; there’s no way they can disrupt the SQL Server, right?

Consume All the CPU

I designed the query below to consume a lot of CPU. However, what if a user executes it with malicious intent or simply by chance? Perhaps they came across it while reading an online article, maybe even this one.

With that in mind, I do not recommend running this in a production environment. Brent Ozar has a post with a significantly longer query, but this one seems effective on my VM.

/*
* MSSQLTips.com
* Please do not run this in production
*/
EXECUTE AS USER = 'TestUser';
 
;WITH CTE
AS (SELECT TOP 10000000
        CONVERT(FLOAT, c1.CustomerId) * 1000 AS Column1,
        NEWID() Column2
    FROM dbo.vw_Customers c1,
         dbo.vw_Customers c2
   )
SELECT SUM(CONVERT(FLOAT, c1.Column1) + CONVERT(FLOAT, c2.Column1))
FROM CTE c1,
     CTE c2;
 
REVERT;
GO

On my VM, I’ll open Perfmon to check the CPU usage. As shown in the screenshot below, the CPU reaches 100% usage. I stopped the query after a few seconds to give the computer fans a break.

Workarounds

Now that we’ve seen the pressure that limited access can cause, how can we work around it? The obvious choice is to prevent anyone from querying the SQL Server; however, here are three other suggestions.

Power BI

Not everyone has access to Power BI, but if you do or are considering making the switch, it’s an excellent tool for preventing direct database querying. If you don’t use a direct query but instead have periodic refreshes, this design reduces resource usage on the data source. From my experience, when someone invests the effort to create a semantic model, they have given considerable thought to ensure the data accurately conveys its intended message.

Buying premium capacity can be expensive; however, there are other options, such as Power BI Premium Per User. It’s hard to beat the ease of pulling data into a tool like Excel from Power BI.

Resource Governor

Microsoft defines the Resource Governor as a tool for managing Database Engine resource consumption and enforcing workload policies. While the Resource Governor does not prevent users from executing inefficient queries, it can help limit their CPU, memory, and I/O usage. Enabling the Resource Governor requires careful consideration and a deep understanding of your current workload. Please do not enable it in production on a whim.

Unfortunately, Azure SQL Database doesn’t offer the Resource Governor as an option. For SQL Server, you must be running on Enterprise Edition to enable it. It’s also important to periodically review the governor’s settings to ensure they remain valid. In other words, it’s not a set-it-and-forget-it tool. Dinesh Asanka created a helpful article titled “SQL Server Resource Governor Configuration with T-SQL and SSMS,” which covers everything you need to get started.

Secondary Reader

If users insist on querying SQL, consider creating an Always On availability group with a read-only replica. This method enables users to query the database without consuming resources from the primary SQL Server. Additionally, you can use the secondary replica to create backups. As with the other two options mentioned above, setting up an Always On availability group requires careful planning and consideration. Edwin Sarmiento wrote an article titled “Read-Scale Always On Availability Group to Offloading Reporting” to help you get started.

Clean Up

When you’re done with the demo, don’t forget to tidy up using the statements below.

/*
 * MSSQLTips.com
 * Clean up when you are done
*/
USE [master];
GO
 
IF DB_ID('ServerStressDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ServerStressDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ServerStressDemo;
END;
GO
 
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'TestUser')
BEGIN
    DROP LOGIN TestUser;
END;
GO

Summary

In this article, we explored how granting access to a single SQL view can lead to potential performance issues. I’m sure you can think of other creative ways to wreak havoc with minimal access. I live in the real world and understand that the boss will occasionally ask DBAs to grant access to a SQL database, and no amount of saying no will change their minds. However, consider one of the alternatives I mentioned earlier to keep your operational database doing what it does best: capturing and storing data.

Next Steps

Are you interested in exploring Power BI but unsure where to begin? Hristo Hristov has developed the course “Introduction to Power BI,” which covers all the fundamentals and beyond.
If the three options listed above are out of reach, at least consider tracking resource-intensive queries. Edwin Sarmiento wrote the article, “Identify CPU-Intensive SQL Queries with SQL Server Query Store,” which applies to any edition of SQL Server or Azure.
When you need access to actively running queries in a production environment, there is no better tool than Adam Machanic’s sp_whoisactive. For details on using the tool, check out Eric Blinn’s article “SQL Scripts for Monitoring Current Activity, Blocking and Performance.”

Resources

Community

Subscribe