Backdoor to Elevate SQL Server Security Privileges

By: Jeffrey Yao   |   Comments (1)   |   Related: > Security

Problem

There are many best practices and guidelines regarding security settings in SQL Server, such as granting users the minimum required permissions, setting the database trustworthy setting off, renaming the sa login, etc. But I seldom see examples illustrate if these practices are not followed and the potential harm to SQL Server security. In this tip we look at some detailed examples.

Solution

SQL Server is a pretty robust system especially when all security best practices are followed. However, in the real world, due to various business requirements, we cannot always follow all best practices, otherwise, Microsoft could simply just harden all security settings in the system, for example: removing options for users to configure any security settings.

For DBAs, as long as we understand the potential security loopholes, we will be more confident in making informed decisions and adopt necessary mitigation approaches. We will look at a few common cases to explore how security settings may cause unexpected problems.

Case 1 – Database user with db_securityadmin privilege gaining db_owner privilege in database

In this case, we will see how a user with db_securityadmin privilege can become a member of the db_owner role.

We will first create a database [DB1] and then create a login and corresponding user in [DB1].

Let’s first set up the environment. We will run the following code in a SSMS window [Win_1], connected as a sysadmin.

use master;

if db_id('db1') is not null
  drop database DB1;
create database DB1;

alter database DB1 set recovery simple;
go

create login John with password='HelloWorld!', check_policy=off;
go

use DB1
create user [John] for login [John];
alter role db_securityadmin add member [John];
go 

Now we will open another SSMS window (Win_2) and log in as [John].

After connecting as [John], we will then try to create a table as follows:

-- this is Win_2, logged in as [John]
use DB1
create table dbo.TestTable (id int);

And we will get the following error:

Msg 262, Level 14, State 1, Line 3
CREATE TABLE permission denied in database 'DB1'.

This is expected because [John] is just a db_securityadmin member, who will not be able to do any DDL/DML work.

So how can [John] elevate himself to do DDL/DML or even more work, like creating/dropping a user? It is actually pretty simple.

In [Win_2] SSMS window, we can run the following code.

-- this is Win_2, logged in as [John]
use DB1
create role TestRole; --first create a new role
grant control on database::db1 to [TestRole]; --grant the maximum permission to this new role
alter role [TestRole] add member [John]; --add [John] self to this new role

-- now [John] can DML/DDL any database objects
-- such as create/populate/select/drop a table
create table dbo.table_john (id int)
insert into dbo.table_john (id) values (1), (2);
select * from dbo.table_john;
drop table dbo.table_john; 

The script mainly does two things, first it creates a database role [TestRole] and then grants database [Control] permission to this new role, and then adds [John] to this new role, thus [John] gains almost all privileges inside [DB1]. We can see this by running the following:

use DB1
select * from sys.fn_my_permissions(null, 'database') 
order by permission_name; 

We will get a total of 74 permissions (this is what I got in SQL Server 2016), like the following:

Case 2 – Database trustworthy setting = 1

In this case, we will see how a database user in one database (with trustworthy = 1) can take ownership of another database.

We will create two databases and create two logins and their corresponding users.

Let’s first set up the environment.

We first open an SSMS window (Win_1) as a sysadmin and run the following code.

use master;
create database DB1;
create database DB2
 
alter database DB1 set recovery simple;
alter database DB2 set recovery simple;
go

Use DB1;
create user [John] for login [John];
alter role db_owner add member [John];
create user [Mary] for login [Mary];
alter role db_datareader add member [Mary];
go
 
use DB2
create user [Mary] for login [Mary];
alter role db_owner add member [Mary];
go

-- in DB2, we create a table and populate it with two rows
Use DB2
create table dbo.t (id int, a varchar(100));
insert into DB2.dbo.t (id, a) values (1, 'hello'), (2, 'world');
go 

Now we open another SSMS window [Win_2] and log in as [John]. In this new window [Win_2], we run the following script and we can find that [John] cannot access DB2 data:

-- this is Win_2
use DB1
print 'I am ' + quotename(user_name(), '[]');
select * from db2.dbo.t; 

We get the following message:

So [John] cannot access DB2 objects, and this is as expected. Now even if we try to execute as [Mary] in [Win_2] and access DB2.dbo.t, we still cannot succeed as shown below:

-- still run inside Win_2
exec as user = 'mary'
select suser_sname(), * from db2.dbo.t
--delete from db2.dbo.t -- you can even delete
revert

Now if we check the trustworthy setting of DB1, we can see that it is set to off (by default):

We will turn on the trustworthy setting for DB1 to show what happens if this is on (this would be done in window [Win_1] by a sysadmin):

alter database DB1 set trustworthy on;
--check trustworthy setting of DB1, DB2
select name, is_trustworthy_on
from sys.databases
where name in ('db1', 'db2');

Now, we will run the previous two scripts again in [Win_2] window as user [John] and we will see:

• If run as [John] directly, we still cannot access DB2.dbo.t:

• But if [John] impersonates [Mary] (who is a db_owner of the DB2 database), [John] can access DB2 without problem:

Actually, through impersonation, if [DB1] is set with Trustworthy on, [John] is elevated to have the same privilege as user [Mary], which in this case, [Mary] is a db_owner of [DB2], even though [John] is not a user of [DB2].

For example, [John] can even add himself into the DB2 database as a db_owner member via the following dynamic code (run it in [Win_2] window):

use DB1
exec as user = 'Mary'
--exec ('use db2; drop user [John]');
exec ('use db2; create user [John] from login [John]; alter role db_owner add member [John];')
revert 

We can verify it by running the following code in [Win_1] window.

We see [John] is now a user in [DB2] and is a member of the db_owner role.

Case 3 – A login with SecurityAdmin privilege can gain SysAdmin privilege

In Microsoft BOL, there is an important note about the [securityadmin] role as shown below:

Say we have a login named [John] and has [securityadmin] membership. We’ll see how [John] can access sysadmin privileges.

We first open an SSMS window [Win_1] as a sysadmin and run the following code:

-- this is window Win_1, logged in as sysadmin
use master;
select suser_sid('John') is not null
drop login [John];
create login [John] with password='HelloWorld!', check_policy;
-- add to [securityadmin] fixed server role
alter server role securityadmin add member [John];  

Now we open another SSMS window [Win_2] and log in as [John]:

-- this is window [Win_2] and logged in as [John]
-- we create another login
if suser_sid('Mary') is not null
drop login [Mary];
create login Mary with password='HelloWorld!', check_policy=off;
-- grant [Control Server] permission 
grant control server to [Mary]; 

Since [John] creates login [Mary], it means [John] knows Mary’s password, so John can open another SSMS windows [Win_3] and log in as [Mary]. Since [Mary] has [Control Server] privilege, [Mary] has almost exactly the same privilege as a sysadmin login, meaning [Mary] can create/drop databases, sp_configure anything or shutdown the SQL Server instance as shown below.

If [John] tries to do the same in [Win_2], he will not be able to.

Summary

In this tip, we have reviewed some cases how a SQL Server login or a database user can elevate to a higher privileged login / database user. This reminds us to be careful when granting such privileges and we need to put auditing in place in case such privileges are abused.

All scripts are tested in SQL Server 2016, but I expect them to be runnable on SQL Server 2008 and later versions as well.

Next Steps

There are a few other potential cases I did not mention in this tip, such as potential harmful CLR stored procedure when database trustworthy is on, or high privileged proxy accounts being utilized in malicious SQL Server Agent Jobs, or the cross database chaining setting and its potential impact to database user privileges, etc.

It may be worthwhile for you to dig deep and explore the potential risks. Only after knowing the risks and how such risks can materialize could we better mitigate these risks in our daily DBA practices.

You can also read a few security-related tips below:

• Ownership chaining in SQL Server security feature or security risk
• Running SQL Server Agent with a least privilege service account
• SQL Server Security Checklist

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips