Testing Non-Privileged Access to SQL Server During Development

Mike Wolfe -

Insufficient permissions are a regular source of end-user runtime errors when working with SQL Server.

During development, we typically work with a development copy of a database.  That "Dev" database is often running on an instance of SQL Server (usually Express or Developer Edition) on our local machine.  For ease of use–and because security is less of a concern when working on a Dev environment–we tend to run with sysadmin-type permissions on our local instance of SQL Server.

But what if you want to debug or test a query, stored procedure, or Microsoft Access application as a non-privileged user?

A Low-Privileged Account

The first step is to create an account that you can grant minimal privileges.

If you need to test network permissions in an Active Directory domain environment, this will need to be a domain account created via Active Directory Users and Computers.

For all other scenarios, the easiest thing to do is create a local Windows account.  I like to name this account bobbyT:

Creating a Low-Privileged Local User for SQL Server Testing
It can be difficult to test how an Access application with a SQL Server backend behaves for a low-privileged user. Creating such a user account is step one.
No Longer SetMike Wolfe

Inefficient Approaches

There are several inefficient ways to use a second account for testing:

  1. Connect from another computer
  2. Connect from a virtual machine
  3. Log out and log in with the low-privileged account

I prefer a different approach: running applications in isolated user environments.

User Environments

Running individual applications in a separate user environment is the most lightweight approach to doing this sort of testing.

Before you go down this road, there are a few important caveats and items to be aware of.  Once you know what they are, most are easy to work around.  But without understanding them, you can find yourself in some puzzling situations.

Application Environment Inheritance
Understanding how applications inherit environment settings from one another can shed light on some otherwise puzzling situations.
No Longer SetMike Wolfe

Be sure to read the entire article above (or at least bookmark it) for when you inevitably get yourself into trouble with multiple user environments.

Creating User Environments with Run As

The idea is to launch an application in a user environment that is different than that of the user currently logged into the computer.

This will grant the application (and any of its descendants) the same rights as the user you are running as.  Typically, we use the "Run as Administrator" command to run applications with elevated permissions.  In this case, we are flipping that idea on its head.

We want to use Run As to run applications with reduced permissions.

Run As for SQL Server Management Studio

SSMS is a simple case if we are connecting to a local instance (see below for advice on connecting to an instance of SQL Server on another domain).

To run SQL Server Management Studio as a different user (a local user named "bobbyT") via the Windows Start menu, you can follow these steps:

  1. Click on the Windows Start menu and search for "SQL Server Management Studio".
  2. Right-click on "SQL Server Management Studio" and select "Run as different user" from the context menu.
  3. In the "Windows Security" dialog box that appears, enter the username "bobbyT" and the password for the user account.
  4. Click "OK" to launch SQL Server Management Studio as the specified user.

Run As for Microsoft Access Applications

With Access applications, I often find myself closing and re-opening applications multiple times during a debugging session.

To avoid having to constantly go through the above steps, I like to take advantage of "Application Environment Inheritance" as described in my article linked above.  To do this, I start by creating a cmd window that launches a second cmd window running as the low-privileged user.  

Here are the steps.  Note that you will need to substitute your computer name and local user name for "MyComputerName" and "bobbyT" respectively.

  1. Open a cmd window.
  2. Run the following command: runas /user:MyComputerName\bobbyT cmd
  3. Enter the password when prompted.
  4. Run the following command in the cmd window titled "cmd (running as MyComputerName\bobbyT)":
    "C:\Program Files\Microsoft Office\Path\To\MSACCESS.EXE"  "C:\Path\To\My\FrontEnd.accdb"
Note that you cannot use the "." (period) as an alias for the local computer name when invoking the runas command.
Here's what a cmd window looks like when running as a different user.

If you leave the cmd window open, then you can easily re-open your Access application by pressing the [Up] arrow key to restore the previous command and then pressing [Enter] to re-launch the application.

Connecting to Another Domain with runas /netonly

If you try using any of the above "Run As" approaches to connect to a network resource in a domain to which your computer is not joined, you will likely receive an error about a failed or missing "Trust Relationship."  

That's because you can't run applications as a domain user from a computer that is not joined to that domain (or joined to another domain with a trusted relationship).  What you can do, though, is run the application using your logged-in account for local tasks, but a domain account for network-related tasks.

The key to making that work is the /netonly flag of the runas command.  I describe that process in detail here:

Using Windows Authentication to Connect to SQL Server from Outside the Domain
You don’t need to join your computer to a domain to be able to connect to SQL Server using Windows Authentication. You just need to know this simple trick.
No Longer SetMike Wolfe

Cover image created with Microsoft Designer