Have you ever wondered why some database administrators (DBAs) use different credentials when logging into SQL Server? In a recent conversation with a DBA from a large financial organization, I discovered that this practice is quite common in many companies. Let’s explore why DBAs use different credentials and how it enhances security.

Typically, companies provide DBAs with two different Windows accounts: a “regular” account for general work like emails and laptop login, and an “admin” account for performing administrative tasks on the server. The regular account does not have access to SQL Servers, while the admin-level account does. By using a separate Windows account to connect to SQL Server, companies can strengthen security measures.

So, how does a DBA connect to SQL Server using the admin account when logged in with the regular account? There are a couple of methods to achieve this.

Method 1: “Run as Different User”

The first method involves using the “Run as Different User” option. To do this, follow these steps:

1. Right-click on the SQL Server Management Studio (SSMS) executable file (ssms.exe).
2. Press the Shift key and then select the “Run as Different User” option.
3. A window will pop up, prompting you to enter the username and password for the admin account.
4. After entering the credentials, SSMS will open with the admin account.

It’s important to note that pressing the Shift key is crucial to see the “Run as Different User” option.

Method 2: Using the “Run as” Command

The second method involves using the “runas.exe” command to run a program using a different user’s current network environment. Here’s how you can set it up:

1. Create a shortcut on your desktop by right-clicking and selecting “New Shortcut”.
2. In the location/parameter field, enter the following command: C:\Windows\System32\runas.exe /noprofile /env /user:SQLAuthority\adm_Pinal "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe"
3. Make sure to replace “SQLAuthority\adm_Pinal” with the correct admin account username and the path for the SSMS executable file.
4. Save the shortcut and double-click on it.
5. A command prompt will appear, prompting you to enter the password for the admin account.
6. Once the password is entered, SSMS will open with the admin account.

By following these steps, you can easily connect to SQL Server using the admin account, even when logged in with the regular account.

It’s important to note that if you are using SQL Authentication, you do not need to use these methods, as Windows credentials are not passed to SQL Server.

Using different credentials to connect to SQL Server is a common practice in many organizations, especially those that prioritize security. By separating regular work activities from administrative tasks, companies can minimize the risk of unauthorized access to sensitive data. If you’re a DBA, consider implementing this practice to enhance the security of your SQL Server environment.