Don't Let Your Permissions be Hijacked!

An SQL text by Erland Sommarskog, SQL Server MVP. Most recent update 2024-08-10.
Copyright applies to this text. See here for font conventions used in this article.
Reviewed by Thomas Grohser, Ola Hallengren and Andreas Wolter.

Introduction

This text discusses how a user's permissions in SQL Server can be hijacked so that the user unknowingly runs code the user should not run. The intended audience for this article are people who are in some sort of DBA role, on server level or database level. Obviously, the more permissions you have, the more interesting it will be for an attacker to exploit them, but I will also point out situations where a user with read-only permission could be the victim of such an attack, and unknowingly help an attacker to steal data. In addition to pointing out the dangers, I'm discussing what measures you can take to defend yourself and other innocent users against this sort of hijacking.

Note: I also have a session on this topic, and if you prefer listening rather than reading, there is a recording on YouTube. You find slides and scripts for the recording here.

Most of the attacks that I describe are from users who already have permissions elevated beyond just running stored procedures and reading and updating data in tables. But I will also show how a developer who does not even have access to the system may find a way to exploit you. Some of the attacks that I present may seem a bit far-fetched to you, but I encourage you to read this article with an open mind. When it comes to security, there is really nothing that is improbable. If an attack is technically possible, it is likely to be attempted with success sooner or later somewhere. You don't want that "somewhere" to be where you work.

The starting point for this exploration is how jobs for index and statistics maintenance could be hijacked by DDL triggers, and I also spend a chapter on mitigating actions for maintenance jobs. I then go on to discuss how you can be lured to run DML triggers or stored procedures that contains malicious code. At this point I change the focus a little bit and discuss how you can write stored procedures that performs privileged actions in a way that non-privileged users can run the procedures. The next point I look at is Agent jobs, which is a tremendous attack surface for permission hijacking – unless you follow the advice I give in this chapter. The last thing I look at is how a developer could sneak in code in a deployment script that you run or take benefit of that you are the user of an application he or she have control over.

There is an immense variation between workplaces how things are organised and what roles people have, and I cannot cover each and every case that exists out there. Instead, I will work from some template scenarios that I dress in examples to keep them less abstract. These examples may not rhyme exactly with the situation where you are, but I hope that you are able to translate them to the presumptions at your site to determine whether you could be susceptible to a certain type of attack.

For the better part of this article, I will focus on three attack scenarios:

1. Attacks against sysadmin. You are the server-level DBA and you are member of sysadmin or hold the permission CONTROL SERVER. The attacker would be a person who has permissions to create stored procedures and triggers in a database, directly or indirectly as a developer whose code is deployed to the database. The attacker desires to become sysadmin or use your permissions to steal or manipulate data in some database.
2. Cross-DB attacks. You are a user in database A, and the attacker is a person with elevated permission in database B who have no access of his own to A, but who wants to exploit your permission for data theft or similar. In several of the scenarios I discuss, I will assume that you are in the db_owner role in A, but the attack as such could be a directed against a read-only user in A as well.
3. Attacks against db_owner. You are the database-level DBA in a database and you are member of db_owner or have CONTROL permission in that database. The attacker is a person who have permissions to create stored procedures and other executable modules in the database, directly or indirectly, and who aspires to gain full control over the database.

It is certainly possible to identify more attack scenarios, but these appeared to me to be the most likely combinations. Just as a reminder: if you hold any permission, it can be hijacked, and the more powerful it is, the more interesting it will be to abuse it.

Attacks through DDL Triggers

Attacks against sysadmin

Imagine a server where you are the server-level DBA. On this server there are many databases, and for many (or all) databases, there are local users who have db_owner rights in the local database, but no server-level permissions. This could be a consolidated server in a large cooperation with databases for 50 to 100 different small applications. It could be a server at a university where each student has their own database to play around in. It could be a hosting service where each customer has one or more databases.

As a service, you think that you should schedule maintenance jobs for the regular stuff: backup databases, check database integrity, update statistics and rebuild or reorganise indexes. But should you? Such a job would typically run as sa, that is be sysadmin. A local power user who wishes to take over the server, could add this DDL trigger to his or her database:

CREATE OR ALTER TRIGGER EvilDdlTri ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
   IF is_srvrolemember('sysadmin') = 1
       EXEC('USE master ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\EvilUser]')

The backup and integrity-check operations pose no danger; they will not fire the DDL trigger. But UPDATE STATISTICS and ALTER INDEX REBUILD/REORGANIZE will.

That is, the DDL trigger checks if the current user is sysadmin, and in such case the DDL trigger runs one or more statements that exploit the permissions of sysadmin – for whom SQL Server never performs any permission checks at all. In this example, the attacker adds him- or herself to sysadmin, which is not without risks. At any time someone could get the idea to review who are the members of sysadmin and the appearance of an unexpected name is likely to result in an investigation with the attacker as the prime suspect. I can think of a few better ways to gain access to sysadmin, that I am not detailing here, but which I leave to the reader's imagination. One thing is important to point out though: if the ultimate desire of the attacker is to steal or manipulate data, this can easily be carried out directly from the DDL trigger. No need to deal with permissions or role membership.

Because there are so many possible evil actions, it is not possible to set up something that stops the DDL trigger once it is there. Sure, you can set up extensive auditing on the server, but there will be a lot information to get through to find suspicious actions. And all you will be able to tell is that the updates were performed by sa or the service account for SQL Server Agent.

Thus, you need to find a way to prevent this attack from being possible at all. A good security principle is that you should never run with more permissions than required for the task. (Often referred to as PoLP, Principle of Least Privilege, described here in Wikipedia and in an SQL Server context in this blog post from Andreas Wolter at Microsoft.) As it turns out, there is a simple measure that you can take to abide to that principle, and this is one we will use with some variations throughout this article.

A maintenance procedure to rebuild indexes typically involves dynamic SQL, since you iterate over the databases on the server, and then over the tables in the databases. You may have something similar to the variations below:

SELECT @sql = 'ALTER INDEX ' + quotename(@ixname) + ' ON ' + 
              quotename(@schema) + '.' + quotename(@table) + ' REORGANIZE'
SELECT @sp_executesql = @db + '.sys.sp_executesql'
EXEC @sp_executesql @sql

SELECT @sql = 'USE ' + quotename(@db) + '
              ALTER INDEX ' + quotename(@ixname) + ' ON ' + 
              quotename(@schema) + '.' + quotename(@table) + ' REORGANIZE'
EXEC (@sql)

All it takes to avoid permission hijacking is to include this command in your @sql batch:

EXECUTE AS USER = 'dbo'

That is, you impersonate the database owner. It may not seem obvious why this would work, but the key is that whenever you impersonate a database user, your security context is not trusted on server level – even if your real you are sysadmin. To illustrate the point, find a database to play in (tempdb will do), and create this DDL trigger:

CREATE OR ALTER TRIGGER ddltri ON database for DDL_DATABASE_LEVEL_EVENTS AS
   SELECT is_srvrolemember('sysadmin'),* FROM sys.login_token

Find some table and run:

ALTER INDEX ALL ON sometable REBUILD

Assuming that you are running as sysadmin, you will get an output like this:

Look particularly at the first column which confirms that you are sysadmin and the usage column to the right which says GRANT OR DENY for the major bulk of your tokens. That is, they are good for gaining permissions.

Now, wrap the above in EXECUTE AS USER:

EXECUTE AS USER = 'dbo'
ALTER INDEX ALL ON sometable REBUILD
REVERT

The output is now something like this:

The leftmost column says 0, that is, you are not sysadmin. The usage column to the right says DENY ONLY all the way. That is, all these tokens are only good for honouring DENY, but you cannot gain any permissions through them. Thus, you have deprived yourself of all rights on server level. And while not shown here, the same is true for access to other databases. Once you have issued EXECUTE AS USER, you are sandboxed into the current database. Thus, a DDL trigger that tries to exploit your server-level permissions, or tries to steal data from another database on the instance, would not find any permissions to exploit. At the same time, as you are impersonating dbo, you still have the full powers inside the database so the maintenance tasks can be carried out. (There is still a risk that a user in the database with lower permissions than db_owner could exploit your db_owner permissions, something I will return to at the end of this chapter.)

You may note the command REVERT in the example above; this command ends the impersonation and restores your original self. You may ask what would happen if the evil user would put that command in the DDL trigger. The answer is nothing at all, because a REVERT only has effect towards an EXECUTE AS which was executed in the same scope as the REVERT command.

Here is the how the snippets above look like after applying this safeguard:

SELECT @sql = 'EXECUTE AS USER = ''dbo''
               ALTER INDEX ' + quotename(@ixname) + ' ON ' + 
               quotename(@schema) + '.' + quotename(@table) + ' REORGANIZE
               REVERT'
SELECT @sp_executesql = @db + '.sys.sp_executesql'
EXEC @sp_executesql @sql

SELECT @sql = 'USE ' + quotename(@db) + '
              EXECUTE AS USER = ''dbo''
              ALTER INDEX ' + quotename(@ixname) + ' ON ' + 
              quotename(@schema) + '.' + quotename(@table) + ' REORGANIZE
              REVERT'
EXEC (@sql) 

Observe that in the second example, the USE command to switch to the target database must come before the EXECUTE AS command for the effect to be the desired.

You need to beware of that the above may fail with this puzzling error:

This happens when the SID for dbo inside the database (in sys.database_principals) does not match the column sys.databases.owner_sid. This can easily happen when you restore a backup on a different server (or on the same server with a different name), because RESTORE will either retain the owner listed in sys.databases for that database name, or set the owner to the login running the RESTORE command. But RESTORE will never read the SID for dbo inside the database, nor update it. The remedy to this problem is to change the database owner with ALTER AUTHORIZATION to the intended owner. The best is to make this a habit when you restore databases.

There is a possibility that the DDL trigger performs actions outside the database that are perfectly legit. For instance, it may audit DDL operations to another database or to a linked server. I can think of workarounds for this situation, but they are too complicated to warrant space in this article. Mail me if you need help with this.

Some readers may know that if the database is set as TRUSTWORTHY and is owned by sa (or some other sysadmin user), EXECUTE AS USER will not have the desired effect, but the DDL trigger will have access to the sysadmin permissions. However, this is not a flaw with my suggested approach. The combination of TRUSTWORTHY and dbo = sa is a security risk of its own and permits a db_owner to elevate to sysadmin without help of DDL triggers. In my opinion, best practice mandates that a database should be owned by an SQL login which exists solely to own this database, and that has no other permissions. For a longer discussion on this topic, see the chapter EXECUTE AS and Server-Level Permissions in my article Packaging Permissions in Stored Procedures or this blog post from Andreas Wolter.

In this chapter, I have focused on maintenance jobs, because they are the most obvious target of hijacking attacks. Since such jobs run regularly, the attacker does not have to wait long until the attack strikes. However, as a server-level DBA you need to take this precaution every time you perform any sort of DDL inside a user database where there are persons who can create DDL triggers but who are not sysadmin.

This also means that if you perform actions such as adding users, granting permissions, creating indexes etc, you need to bracket your statements in EXECUTE AS USER = 'dbo'. From this follows that you cannot use the UI in SSMS directly. If you are the sort of person who prefer point-and-clicking over writing commands, this may make you feel a little uncomfortable. However, most dialogs in SSMS permits you to script the commands. So rather than using the OK buttom at the button, you can use the script button on top and then add EXECUTE AS USER = 'dbo' to the generated script. Just don't touch that OK button. It's dangerous!

And, no, DDL triggers is not the only means a malicious user can attack you. Stay tuned for you how can be attacked through other channels like DML triggers and stored procedures. I will talk about this in later chapters.

Attacks against db_owner

If you are db_owner in a database, you can be the victim of an attack through a DDL trigger, if there is a user in the database who is a member of the db_ddladmin role, but whose aspirations go beyond that.

DDL triggers should in my opinion be under the control of members in db_owner only. There may be users you want to permit to be able to change stored procedures, create tables and triggers on these tables, and therefore you add them to db_ddladmin. But they should not be able to change DDL triggers. That is too powerful as it permits them to hijack your db_owner permissions. And it also permits them to meddle with any auditing through DDL triggers that you may have set up (to keep an eye of what they are doing).

Therefore, if you have db_ddladmin users who are not db_owner, I recommend that you should run this command for each of them:

DENY ALTER ANY DATABASE DDL TRIGGER TO [Domain\User]

This is even more important, if you assume both server-level and database-level DBA duties in a database, but you admit other users – be they developers, junior DBAs etc – into the db_ddladmin role in one or more databases. The more powerful your permissions are, the more interesting it will be to hijack them.

Or even better: do not add users directly to db_ddladmin, but instead create a database role or an AD group that you add to db_ddladmin and DENY that role or AD group the permission ALTER ANY DATABASE DDL TRIGGER. This group would be the only member of db_ddladmin. Then you add all users that need full rights to create tables, procecdures etc to this role instead of db_ddladmin. In the rest of this article, I will refer to this role/group as our_ddladmin. The style of the name imples a database role, but as I said, it could also be an AD group. When ever you see this name this means db_ddladmin minus rights to DDL triggers. (Why not simply DENY db_ddladmin directly? SQL Server does not agree to that.)

Cross-DB Attacks

Cross-DB attacks could be performed with help of DDL triggers, but I will cover this in the chapter on DML triggers, as it is just a variation of that type of attack.

An Elaboration on Maintenance Jobs

Before we move on to other hijacking threats, I like to discuss maintenance jobs a little further, as they are a very easy targets for this type of attack. Since they typically run daily or at least weekly, they give attackers plenty of time to fine-tune their DDL triggers.

In the previous chapter, I assumed that you were implementing your own maintenance job. But that was only to keep focus on the solution with EXECUTE AS USER = 'dbo'. In reality, there is little reason to reinvent the wheel, and most people use one of these two standard solutions:

1. The built-in maintenance plans in SQL Server Management Studio.
2. Ola Hallengren's Maintenance Solution.

Let's start with Ola Hallengren's solution. His procedure IndexOptimize accepts the parameter @ExecuteAsUser by which you can request that the generated commands should be executed under the context of the user you specify. You can specify 'dbo', but you can also specify any other username of your choice. The idea in the latter case is that you have a user (presumably created WITHOUT LOGIN) in every database, which you have added to our_ddladmin. This permits you to also get protection for hijacking attempts against db_owner. (Keep in mind that to have a user created in every new database, you can add this user to the model database.) The default for @ExecuteAsUser is NULL which means no impersonation. That is, you explicitly have to request to be protected against permission hijacking.

Beware that if you specify @ExecuteAsUser='dbo', that the job may fail due to a mismatch in dbo between the database and sys.databases, as I discussed in the previous chapter.

On the other hand, the maintenance plans in SSMS, do not have any such option, so you with them you have no choice but to be open to permission hijacking. Before you panic and throw them out the window thinking that they are big security risks. I like to put things in perspective. First or all, keep in mind that the issue is only with index and statistics maintenance, as they fire DDL triggers. BACKUP and DBCC CHECKDB do not fire DDL triggers, and thus the protection for EXECUTE AS USER is not needed for them. (In fact, when I tested, DBCC CHECKDB failed when I bracketed it with EXECUTE AS USER.)

Disclaimer: Maintenance plans may include a few more operations than just BACKUP, DBCC CHECKDB and index/statistics maintenance. I have not tested all operations to see if they fire DDL triggers, nor have I tested all flavours of the BACKUP statement. If you want to know whether a certain action fires a DDL trigger, I recommend that you test for yourself. On a database processed by the maintenance job, set up a DDL trigger that logs the result of the eventdata() function to a table. Then you can see the next morning what you trapped.

Also, keep in mind that for DDL triggers to be a risk, there must be users in the database who have permissions to create DDL triggers without being sysadmin. On many servers, the same team assumes DBA duties on server and database level, and as long you don't add a developer or a performance consultant into the db_owner role, and you never use db_ddladmin, but always our_ddladmin for these people, there is no one who can gain anything by a DDL trigger attack.

But if you have databases with users who are in db_owner, without being members of sysadmin, you need to be careful. What are your options when it comes to index and statistics maintenance and you are using the maintenance plans from SSMS? Here is a list of options.

1. Write your own maintenance job which uses EXECUTE AS USER where needed.
2. You switch to Ola Hallengren's solution, at least for index and statistics maintenance to make use of the @ExecuteAsUser parameter to IndexOptimize.
3. Schedule separate index and maintenance jobs for each database where there is a db_owner user who is not in sysadmin. Such a job needs to be owned by a login who is db_owner in the database, but that holds no permissions outside of the database. This aligns well with the best practice for database ownership that I mentioned in the previous chapter: a database should be owned by an SQL login that exists solely to own that database. That is, if you go with this principle for database ownership, you make the database owner the owner of the job. (Note that you cannot schedule a single job that handles all these databases – the local power users may be looking into stealing data from each other.)
4. Tell the local power users in the concerned databases that they will need to schedule index and statistics maintenance themselves from Windows Task Scheduler. (Why not from SQL Server Agent? We will come to that in a later chapter.)
5. Ban database-level DDL triggers with a server-level DDL trigger. This would permit you to use the maintenance plans in SSMS without having schedule separate jobs. I detail this below.
6. If you assume both server-level and database-level DBA duties, but you have added a developer or a consultant to db_owner for troubleshooting or performance tuning, maybe that person can be demoted to our_ddladmin, and thereby evading the need to schedule separate jobs.
7. Trust that the persons in question will never act maliciously.

I like to point out that the numbering in this list does not reflect an order of preference on my part, but you need to make a decision on your own of what fits best to your site. Obviously, security is an important point here, but you may find some of these options less practical than others for you. For instance, while #7 is out of the question on high-sensitive system, it may be OK on development or test servers where there are no real assets to be found.

It is with some hesitation I present solution #5, banning database DDL triggers with a server-level DDL trigger. There are several reasons for my hesitation. First of all, only consider this option if you have full control over from where database backups are being restored. If users can restore databases themselves (which they can if they own the database or have been granted membership in the server role dbcreator), and they can read backups from a file share they have write access to, just forget about this idea. This permits them to restore a database with a DDL trigger with nasty code in it. The same is true, if you think they could lure someone on your team to restore a backup from the outside.

Another reason for my hesitation is that database DDL triggers can be very useful, not the least for auditing, so I don't like the idea of banning them. Finally, I am not entirely confident that my suggestion is fully secure – maybe there is a loophole I have overlooked? So if you take this route, scrutinise it carefully on your own.

Here is the server-level DDL trigger itself:

CREATE OR ALTER TRIGGER server_ddltri ON ALL SERVER 
                FOR CREATE_TRIGGER, ALTER_TRIGGER AS
   DECLARE @objecttype varchar(20),
           @eventdata xml = eventdata()
   SELECT @objecttype = E.e.value('(./text())[1]', 'nvarchar(MAX)')
   FROM   @eventdata.nodes('/EVENT_INSTANCE/TargetObjectType') AS E(e)
   IF upper(@objecttype) = 'DATABASE'
   BEGIN
      ROLLBACK TRANSACTION
      ; THROW 50000, 'Database level DDL triggers not permitted on this server', 1
   END

Deploying the server-level DDL trigger is not enough. You also need to check existing databases for DDL triggers, so that the trap has not already been set for you:

CREATE TABLE #ddltridbs (db sysname NOT NULL PRIMARY KEY)
DECLARE @sql nvarchar(MAX)
SELECT @sql = 
   (SELECT 'IF EXISTS (SELECT *
                       FROM   ' + quotename(name) + '.sys.triggers
                       WHERE parent_class_desc = ''DATABASE'')
              INSERT #ddltridbs (db) VALUES(' + quotename(name, '''') + ')' +
              char(13) + char(10)
    FROM   sys.databases
    WHERE  state_desc = 'ONLINE'
      AND  database_id > 4
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
PRINT @sql
EXEC (@sql)
SELECT * FROM #ddltridbs

You also need to run this if you restore a backup that comes from the outside, or it is an old backup taken before you created the DDL trigger.

IF is_srvrolemember('sysadmin') = 1 RETURN

CREATE OR ALTER TRIGGER ddltri ON DATABASE WITH EXECUTE AS SELF
        FOR DDL_DATABASE_LEVEL_EVENTS AS

DML Triggers

If a DDL trigger can be utilised for permission hijacking, so can a regular trigger for INSERT, UPDATE or DELETE. In this chapter we will take a closer look at this.

Attacks against sysadmin

If you are a skilled DBA, you may be asked to do some data cleansing or some other complicated update of tables in a database. Not that it is your particular responsibility, but people think you are better at writing complex SQL operations than they are themselves. Or so they say. But one day when you do this, the person who have asked you do this operation, has added an extra trigger on one of the tables with a check if the current user is in sysadmin, and which executes something nasty if so.

We learnt in the chapter on DDL triggers that there is a simple means of protection against this sort of attack:

EXECUTE AS USER = 'dbo' 

At least when it comes to protecting your sysadmin permissions, but you may also have to consider attacks against db_owner, which I discuss later in the chapter.

If the operation requires that you retrieve data from other databases or linked servers, this is not going to work once you have issued the above. I would suggest that the best in such a situation is to read data from these sources into temp tables before downgrading your permissions and only bracket the actual updates with EXECUTE AS USER + REVERT. As long as you are only selecting data, there is no attack surface.

For the particular scenario I outlined, you more or less have to check sys.triggers for what triggers there are on the tables you are about to update, and you need to review these triggers. Primarily, not to find hijacking attempts, but you need to know what these triggers are doing, since they could affect your data manipulation task. However, while this investigation is advisable, it is not really a replacement for using EXECUTE AS USER, because there is a risk that by the fourth time you are asked to perform the same data manipulation task, you feel now confident that you know the triggers and you don't need to check them anew. But by that time, the rogue user has added an extra trigger with evil commands.

Cross-DB Attacks

In this scenario, you are the administrator for the database A which has some decently sensitive data. The administrator of database B has a legit need to get some of the data in your database into B, and the B admin tells you I have created a table in my database where you can dump the data. I have temporarily created a user for your login and granted you INSERT permissions on that table. And if you don't watch out, there is a trigger on that table that gives the B admin far more access to your database than you would like to. The trigger could add the B admin to your database as a user, or simply copy a lot more data than you had intended to give him.

It would not help you much to look in in sys.triggers. The B admin may have denied you VIEW DEFINITION on some level, so that you don't see the trigger listed. And even if you see the trigger, your permissions will not permit you to view its code. Of course, if the table was created for you to dump the data, the mere fact that there is a trigger is a little suspicious, but the B admin may be able to talk you out of your concerns.

A variation is that the B admin tells you that he has created a schema for you and given you CONTROL on that schema, so that you can create the tables and dump the data there. In this case, the sneaky B admin has added the evil code to a DDL trigger. And you will never be able to tell if there are any DDL triggers in the database with the permissions you have.

So how do you defend yourself? The answer is again EXECUTE AS USER. But you don't have permissions to impersonate dbo in database B, so whom do you impersonate? Answer: yourself. Say that your login is DOMAIN\AdminForDB_A. You first extract all data you need into temp tables. When it is time to write data to database B, you issue this command:

USE B
go
EXECUTE AS USER = 'DOMAIN\AdminForDB_A'

By making your security context not trusted, you have renounced yourself of all access outside database B, including your own database, and no trigger can hijack your permissions. As I discussed in the previous chapter, there is one exception to this: database B may be marked as TRUSTWORTHY and this is nothing you have control over. However, you can check the column is_trustworthy_on in sys.databases for database B, and would it be set, it is time to discuss alternate solutions with the B admin – don't trust a trustworthy database!

Note: to see data in sys.databases about databases you don't own, you need the server permission VIEW ANY DATABASE. This permission is granted by default to public. But the server-level DBA may have revoked this permission.

There is another type of cross-DB attack: Say that you are a read-only user of database A as well as a read-write user of database B. The evil B admin could have a trigger that checks whether the user has access to A, and if so, use that to copy data from A to his own database. As you are accessing the database through an application, there is not really much you can do yourself to prevent this from happening. But maybe database A should not be on a shared instance at all if data is that sensitive. We will return to this theme in the chapter Attacks through the Application.

Attacks against db_owner

Attacks against db_owner through DML triggers could be from members in the db_ddladmin or our_ddladmin role or from users who have ALTER permissions on the dbo schema or other important schemas or tables in the database. They could create a trigger on a table and lure you to run an operation on that table and then exploit your permissions. And maybe they don't have to lure you – they know that you are regularly performing maintenance on that table.

There can be a simple way to protect yourself against this threat, to wit, this database DDL trigger:

CREATE TRIGGER no_triggers_please ON DATABASE FOR 
     CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER AS
   IF is_rolemember('db_owner') = 0
   BEGIN
      ROLLBACK TRANSACTION
      ; THROW 50000, 'Only db_owner can work with triggers in this database', 11
   END  

This trigger is good if the design pattern for the database is such that triggers are not used at all, or very sparingly. It is also good if you want to reserve trigger-writing for db_owner. A particular situation when you may want to do this is when you have granted a developer ALTER permission on the dbo schema to be able to work with stored procedures, but you don't want that developer work with triggers or tables, as the developer may not have the skills for this. (You may have to add more events to the list, e.g. ALTER_TABLE, in this case.)

To repeat, for this trigger to be meaningful, you should never add people directly to db_ddladmin, but create the role our_ddladmin which is a member of db_ddladmin, but which have been denied the permission ALTER ANY DATABASE DDL TRIGGER. Because if they are in db_ddladmin directly, they will have the powers to change or disable the DDL trigger no_triggers_please.

This solution is far from always feasible. I want to in no way suggest that you should not use triggers – triggers can be an essential building block for database integrity. You could very well have a developer that you want to permit perform anything when it comes to DDL (save for DDL triggers!), including DML triggers, but you don't want that person to create users or certificates. In this case, our_ddladmin is a good choice.

But this also means that this developer may lay traps for you in triggers. The solution is to impersonate a sandbox user, a concept I will elaborate on in the next chapter, whenever you are writing or deleting data in tables.

Attacks through Stored Procedures

Continuing from the scenario in the previous chapter, the person asking for your help provides you with a stored procedure for you to run. A procedure that may be prepared with some evil stunt to use your permissions for actions you did not intend.

Overall, this attack has a lot in common with the attack through DML triggers. You could say the DML trigger attack is more devilish, though, because you may not expect any trigger at all to be there. On the other hand, if you are given a stored procedure to execute you can clearly see that you are about to step into unknown territory, and it only takes a minor level of paranoia to get the idea to at least skim through that procedure to see what it is up to. But you may find that the procedure calls other procedures, and it builds complicated dynamic SQL where some parts are taken from tables etc. In the end, time may not permit you get through all that code. So while code review is a virtue, I would not really rely on it as my main defence line against permission hijacking.

No, as before, impersonation is what you should use. In the cross-DB scenario, you impersonate yourself as I described in the previous chapter, there is no difference at all. As sysadmin you can impersonate dbo to protect your sysadmin permissions. However, depending on the situation in the database, you could still be victim of an attack against db_owner.

When it comes to attacks against db_owner, there is risk for an attack if there is someone who has CREATE PROCEDURE permissions in the database, and has ALTER permission on a central schema like dbo. This includes users in the our_ddladmin role.

Before we look at by what means you can defend yourself, let's first consider whether there should be any such users. If there is a developer who needs to work with a few stored procedures, but who should not deal with tables, triggers or views, maybe it is a better to create a separate schema where the developer can work and you only grant the developer ALTER on this schema. When the developer has completed his work, you review the code to check for suspicious activity (and everything you don't understand, you label as suspicious). If the code passes the review, you move the procedures to dbo or any other schema where the they are supposed to reside. This means that you only need to have your guard up when executing procedures in the developer schema.

Before I move on, I like to remark that if you don't want the developer to be able to modify data in the database, you also need to change the owner of the developer schema. See further the section A Security Threat with Ownership Chaining in my article Packaging Permissions in Stored Procedures.

I realise that this is not always practical, because it may be the case that the developer is going to work all over the place, so you have no choice but to grant the developer permissions on the dbo schema. But this also means that any procedure in the database is a threat to you, including those you have written yourself – but which the attacker may have modified to include malicious code that executes if powerful enough permissions are present.

In this situation, you cannot run application procedures as yourself, but you need to impersonate a user with minimal permissions. I would recommend that you create a sandbox user, like this:

CREATE USER sandboxuser WITHOUT LOGIN

You grant sandboxuser exactly the permissions a typical application user would have. For instance, if normal users are members of the role ApplicationUsers, you add sandboxuser as a member to that role. When you need to run application procedures in the database, you impersonate this sandbox user with EXECUTE AS USER. Beside the protection against permission hijacking, there is a second advantage which in practice may be more important. To wit, if a developer has been oblivious about what permissions normal users have and has added something that requires permissions beyond that, you will get a permission error and you can discuss with the developer how to rectify this. (One example of this could be use of dynamic SQL, despite that the ApplicationUsers role only has permission to run stored procedures, but no SELECT permissions on tables.)

Note: if you also need to use a sandbox user when running direct DML statements to protect you from evil DML triggers, the sandbox user will obviously need INSERT, UPDATE and DELETE permissions.

Just beware that a login-less user does not have any access outside the database, so access to linked servers or other databases will fail. If you are only db_owner, you may have no choice but to rely on code review for such situations.

If you are also wearing the sysadmin hat, you can resolve this situation by creating a sandbox login for impersonation with EXECUTE AS LOGIN. This will work for cross-database access, but you need to tread carefully here, though, if you are on a server with many unrelated databases. If you use the same sandbox login in many databases, you can be victim of a cross-DB attack, so you would need one sandbox login per database. For this reason, my preference is for login-less users, which also keep sys.server_principals less cluttered.

What if you need access to a linked server? If the linked server is set up with login-mapping using a sandbox login still works, but not if the linked server uses self-mapping, because the impersonation is not trusted outside the SQL Server instance. What options do you have?

• You can set up login-mapping for the sandbox login only. However, this requires that the remote SQL Server instance has SQL authentication enabled. To enable SQL authentication, the instance needs to be restarted for the change to take effect. Which for a critical system may be out of the question until the next maintenance window.
• You ask the AD admins to create a sandbox user in Windows for you. You add this sandbox user on all servers and databases you need to access. You start SSMS with the RUNAS command to run SSMS as the sandbox user. Yes, that is a bit of a mouthful.

Let's now go back to the situation where you are in the sysadmin role and you are making a guest appearance in a database where you do not normally assume the database-level DBA duties, but they fall on a local power user, and on top of that there are users in this database who can create procedures and possibly also DML triggers without being db_owner. You need to protect your sysadmin permissions from attacks from the database DBA, and you need to protect the db_owner permissions against attacks from power users with lower privileges in the database. If the database-level DBA provides you with a sandbox user to impersonate, you would of course use that. And if the db_owner tells you that there are no users with elevated permissions, you just go on and impersonate dbo. After all, the threats against db_owner is the responsibility of the database-level DBA. Then again, you may have an unnerving feeling who will get the blame if something bad happens, so you may want to play it safe anyway. Here are the steps you would take in such case:

1. First impersonate dbo.
2. As dbo you create a sandbox user WITHOUT LOGIN.
3. Grant that user the permissions needed on tables and stored procedures.
4. Impersonate that user.
5. Run your script.
6. Revert to be dbo.
7. Drop the sandbox user
8. Revert back to your true self.

Yes, that is more work, but if you are in an environment where security is above anything else, this is how you need to do it. Observe here that you need to make two impersonations. You first need to downgrade yourself to dbo so that you can create and drop the login-less user safely, without being a victim of an attack through a DDL trigger.

If you prefer to use a sandbox login instead, this is not much different, since you still need to add that login as a user in the database, so it is only that step 2 is a little different.

Writing Your Own Stored Procedures

The main tactic so far has been to impersonate a database user to deprive yourself of permissions that could be hijacked. But as the reader may have sensed, this is not always a feasible option. What if you actually need your own permissions in the middle of the task? For instance, your data manipulation task may require you to load data with BULK INSERT (which requires a server-level permission). I suggested earlier that you could load data into temp tables before using EXECUTE AS USER, but you may not always find this practical. Moreover, what if this task is to be executed repeatedly, and you don't want to babysit it yourself every time? You would prefer to give the users a stored procedure they can run themselves at their own liking, but they may not have all the permissions needed. And then there is the whole business of Agent jobs that we will look at in the next chapter.

There is a solution to all this: it is possible to package permissions with a stored procedure, so that the permissions come with the procedure, rather from the user running the procedure. The great thing with this is that the procedure has full control of how the permissions are applied. The technique we will use is certificate signing which I discuss in detail in my longer article Packaging Permissions in Stored Procedures (which I have already referred to a few times). Here, I will only give you a quick description of the concepts and show you examples of how easily this can be automated with a procedure and a script from my longer article.

The outline is this: you create a certificate, and then you sign your stored procedure with that certificate. Next, you create a principal from that certificate and you grant that principal the permissions you want to embed in the procedure. If the permission is a database permission, you create a database principal, i.e. a user. If the permission is a server permission, you create a server principal, i.e. a login. These principals are not normal logins or users, but a special type that cannot actually log in. They exist only to connect permissions and certificate.

When you want a to package a permission to be used in the same database, the certificate only needs to exist in that database. If you want to package a server-level permission, the certificate must exist both in master (so that we can create a login to grant permissions to) and in the user database (so that the procedure can be signed). Certificates can also be used to cater for cross-database access, and I will return to this later.

When you execute a procedure that has been signed with a certificate, and the signature is valid, the token for the principal created from the certificate is added to the sets of security tokens for the current process. (You can inspect these sets in sys.login_token and sys.user_token.) Your set of permissions is the union of all permissions granted to these tokens, so the permissions granted to the certificate will be in effect inside the procedure. Once the procedure exits, the token for the certificate principal is removed.

Note the passage in italics in the previous paragraph. If the procedure is altered, the signature becomes invalid (and in fact SQL Server removes it entirely). Thus, a user who has permission to change the stored procedure, but not the permissions required to sign the procedure cannot change the procedure and exploit the permissions packaged into it. What you sign is what will execute with the extra permissions.

What is also very interesting is under which conditions the token for the certificate principal remains among the security tokens:

1. If you invoke dynamic SQL, the certificate principal is still there, and so are the permissions granted.
2. The same is true if you call a system procedure.
3. But if you invoke a user-written module – a trigger, a stored procedure or a function – the token is removed, unless that module is also signed with the certificate.

The last thing is extremely important in the context of this article. This means that you can package any permission you want into your stored procedure, without having to worry about this permission being hijacked by a trigger when inserting or updating data. The same is true if your procedure calls another stored procedure that someone else could modify. That procedure will not have access to the permissions packaged in your procedure. What you sign, is what you see.

Note: You may be familiar with the clause WITH EXECUTE AS OWNER that you can add to a procedure header. This is also is a method to package permissions in stored procedures but it has several shortcomings and I refer you to my longer article for the details. Here I only like to point out that the effect of this clause remains in effect when you enter user-written modules, and thus permits for permission hijacking.

The db_owner Case

Let's now look at some practical examples how to apply this technique in the three situations we have discussed in this article, starting with when you are in the db_owner role. Say that the application needs to be able to create a new database user (which requires the permission ALTER ANY USER) and add this user to the standard role for application users (which requires ALTER permission on that role). You put the code to build the CREATE USER and ALTER ROLE statements with dynamic SQL in a stored procedure, add_new_user. Presumably, this procedure checks that the user invoking the procedure has the rights to perform this action (as defined by the rules in the application) and any other business rules that may apply.

The outline above includes a couple of steps, to give you an understanding of the process. However, you don't have to figure out the commands yourself, but I have a shortcut for you. In my article on certificate signing, I introduce the stored procedure GrantPermsToSP which automates the process for you. Here is how you would do it for add_new_user:

DECLARE @perms Management.Permission_list
INSERT @perms (perm) VALUES('ALTER ANY USER',
                           ('ALTER ON ROLE::ApplicationUsers')
EXEC Management.GrantPermsToSP 'add_new_user', @perms, @debug = 1

The procedure creates the certificate and the user with names formed from the name of the stored procedure passed to it. If you re-run GrantPermsToSP for the same procedure, it will first drop everything that was created the first time, and then create a new certificate and redo the operation. With the parameter @debug set to 1, the procedure prints the statements generated. I definitely recommend that you run with the debug output the first times you use GrantPermsToSP to get an understanding of what it's doing.

When you test add_new_user, you should impersonate your sandbox user (or some user that by the application rules is entitled to create users) to make sure that everything is working as intended.

The sysadmin Case

Say that you have been asked to write a data-manipulation task for a database D, and the task includes loading a data file with BULK INSERT, which requires the server-level permission ADMINISTER BULK OPERATIONS. This is nothing you want to grant directly to the users in D, as this would permit them to load about any file on the server and that is not permissible. Therefore, you decide to write a stored procedure, load_data, and use certificate signing to package the bulk-load permission inside the stored procedure.

Note: For this to work, you need to wrap the BULK INSERT statement in dynamic SQL, even if the file name is fixed and there is no need for dynamic SQL. This is a special case with BULK INSERT and does not apply to certificate signing in general. See also the chapter Using BULK INSERT to Reload a Table in the appendix to my certificate-signing article.

In my article on certificate signing, I present a script GrantPermsToSP_server.sql which automates this task. (It is a script, not a stored procedure, since as a server-level DBA you may want to run this on different servers on different occasions, but you could make it into a stored procedure, if you wish). The script has a header part where you set things up, and the rest you can leave as it is. Here is how the header part would look for the example above:

USE master
go
-- Set up parameters: the procedure to sign and the database it belongs to.
DECLARE @database nvarchar(260) = 'D',
        @procname nvarchar(520) = 'dbo.load_data'

-- The permissions to grant through the certificate. Leave table empty
-- to only remove current permissions.
DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('ADMINISTER BULK OPERATIONS')

-- Run with debug or not?
DECLARE @debug bit = 1

If you would try this and inspect the debug output, you will find a few occurrences of this statement:

EXECUTE AS USER = 'dbo'

I have added this to all places where the script runs DDL in the user database – exactly to avoid permission hijacking.

As GrantPermsToSP, the script forms the names of the certificate and the login from the names of the database and the procedure. If you re-run the script, all existing objects – certificates, logins, signatures etc – are dropped and new ones are created.

There are two more things to mention:

• The script removes the private key of the certificate to make absolutely sure that the local power user cannot use the certificate to sign procedures. (The private key must be present when you sign, but only the public key is needed to validate the signature.)
• If the database is part of an availability group, the script copies the certificate, the login and the permissions to master on all nodes in the AG.

Even if the idea is that the users in D are to run this procedure themselves, you need to test that it works. Obviously, you cannot run load_data as yourself, for two reasons: 1) your permissions could be hijacked. 2) you would not test that you have packaged the permission correctly. However, neither can you apply your standard trick with impersonating dbo with EXECUTE AS USER, because when you impersonate a database user you are sandboxed into the current database, and no server-level permissions apply, not even those added through a certificate login. You would have to create a sandbox login which you add as a user to database D, (don't forget to run EXECUTE AS USER before doing this!) and your grant that user the permissions needed on database level to run the procedure. You impersonate that login with EXCUTE AS LOGIN. Rather than creating a sandbox login, you could simply impersonate one of the users in D, preferably with their consent.

The Cross-DB Case

Say that the B admin needs to get data from your database on a regular basis. The condition is still that you have the final say over what data you permit to be exported to B. You want to write a stored procedure that the B admin can run without your presence – and still have full control of what data is extracted. Can this be achieved with certificate signing? Absolutely, but unless you are also in the db_owner role in B, it is a bit difficult, and in practice you will need to collaborate with the B admin, even if you don't trust that person.

To understand this, let's first look at how you would do it if you would happen to have db_owner powers in both databases. In this case, it is not too different from packaging server-level permissions. I don't have a pre-cooked script here, but you could work from GrantPermsToSP_server.sql as the steps are the analogous:

1. Create a certificate in B.
2. Sign the procedure in B.
3. Remove the private key.
4. Export the certificate to database A (rather than master, that is).
5. Create a user in A from the certificate (and not a login).
6. Grant that user the permissions needed (typically SELECT permission on the tables to export from).

When you test this out, you do as in the previous section: you set up a sandbox login which you add as a user in B – but not in database A! You see, not only does the certificate provide the SELECT permissions you grant to it, but it also comes with CONNECT permissions to database A, so the user running the procedure does not need to have access to the database on its own.

Let's now turn to the case that you are the admin of A only. This presents you with a number of challenges:

1. You may not have permissions to create stored procedures in database B.
2. You definitely do not have permissions to create certificates and sign procedures in B. The db_owner of B should never agree to that, just as you would never agree to give the B admin such permissions in your database.
3. How would you test this procedure?
4. Would the B admin have trust enough in you to run the stored procedure you have written? For all he cares, you may be one who is playing the hijacking game.

There are mitigations for some of these issues. For the first two, a solution is that you create the procedure in a database you have control over together with a certificate and sign the procedure. You can then compose an export script that you send to the B admin. This script would include the stored procedure, creation of the public key of the certificate and an ADD SIGNATURE statement with the signature from your source database. I describe these steps in more detail in the chapter on cross-database access in my permission-packaging article. This certainly is an exercise on the advanced side.

For the last issue, the B admin needs to review your procedure to verify that the only operations you perform in your own database are plain SELECT statements that cannot hijack any permissions. Would he see a DML operation or a call to a stored procedure, he will refuse to run your procedure, since a trigger or a nested stored procedure could hijack his permissions in database B.

What I don't have a good mitigation for is step 3. You cannot test your procedure in database B by running it yourself – your permissions could be hijacked. You cannot use EXECUTE AS USER, as it will sandbox you into B so access back to your own database will fail despite the certificate. I leave it to your own creative mind to find the best solution. But as I said above, you will most likely have to collaborate with the B admin in some way.

Agent Jobs

By default, when you create a job in SQL Server Agent and you are sysadmin, the job will execute in the context of the service account for SQL Server Agent, which also is a member of the sysadmin role. Imagine now that you create a job that runs a stored procedure in a database, where there are users with permissions to change that procedure, but who are not sysadmin. By now, you understand that this is a risk for permission hijacking if anything, and this is something you never should agree to. In this chapter, I will present what alternatives there are.

I like to start with establishing a few principles about Agent jobs that run stored procedures performing application tasks in a user database (so this is not really about index and statistics maintenance):

• They should execute in the context of a database user that has same permission set as the typical application user, for instance being member of the ApplicationUsers role (or what you may call it) and with no server-level permission beyond the default.
• If the procedure needs to perform actions beyond the permission set in the previous bullet point, those permissions should be packaged into the procedure with certificate signing, no matter whether these are permissions in the same database, in a second database or on server level.
• Jobs should be not be owned by persons, who may leave the company and fall out of the AD which will cause problems. The job owner should be an abstract owner, like sa for jobs that run as sysadmin, or a dedicated application id, more about this later.
• Avoid adding non-sysadmin users to any of the SQLAgent roles in msdb.
• It follows from the points above, that it is always a person in the sysadmin role who adds and maintains the jobs as such. (The code executed by the jobs could very well be written by a developer with no server-level permissions.)
• And while not really a principle, I like to remark that there are alternatives to Agent for application jobs. The devs could use the Windows Task Scheduler on the application server, they could have their own little scheduling service, or, if they have advanced needs, use a third-party scheduler.

There are three ways to control the security context of an Agent job that I will discuss here. The first option is to make use of a parameter which sits on the Advanced page of the job-step dialogue. On the bottom of that page, you find Run as user. You will need to ask the person responsible for the database which user to use, but presumably this is a sandbox user created WITHOUT LOGIN. With this option, SQL Server Agent will issue an EXECUTE AS USER for this user which means that the job will be confined to the database and cannot access other databases, linked servers or other server resources. Thus, this option is not feasible for all jobs. Observe that for this option to work, the job owner must be a member of sysadmin or else the parameter is ignored. (See the documentation for sp_update_jobstep, the parameter @database_user_name.)

The second option is to change the job owner, which you do on the front page for the job. As I said above, the job owner should be a login tied to the application and not to a person. It can be an SQL login; the advantage is that you can "keep it the family" and you do not need to involve the Windows admin. I would expect, though, that many prefer to use a Windows login. Whichever is fine, as long as the permission set is the same as a typical application user. (So it would not be the database owner, even if this is a login dedicated to own that database, as this would permit users with permissions to change the procedure without being db_owner to hijack the db_owner permissions.) When you change the job owner to a non-sysadmin user, SQL Agent will issue an EXECUTE AS LOGIN for this login. Since this is impersonation on server level, this means that there are no restrictions inside the instance, so the job can access other databases and employ server-level permissions packaged into procedures. However, the impersonation is not trusted outside SQL Server, which means that the job cannot access linked servers with self-mapping. Linked servers with explicit user-mapping works, though.

When using this option, you should be aware of that the job owner will be added to the role SQLAgentUserRole in msdb. This does not happen immediately, but every time you install a Cumulative Update or a security GDR, the install process runs the script msdb110_upgrade.sql and this script has the idea of adding job owners who are not sysadmin to the SQLAgentUserRole. One could hope that Microsoft would change this in the future, but this "feature" has been there for a long time and it was still present in CU14 for SQL 2022, which was the most recent CU at this writing. (I have seen a case on the SQL forums where this led to the entire CU install failing, because the job owner was no longer in the AD.)

There is a second drawback with this option that I will return to below.

The last option is to use a proxy and run the job step as a CmdExec job. It takes a little more work, and it may require you to involve more people. Nevertheless, once you have got it going, it is likely that you will settle on this as your standard, because it is a more general solution. Here are the steps:

1. The AD admin creates a Windows login and adds it to the AD group(s) needed for the application in question.
2. You create a credential in SQL Server for this login:

   CREATE CREDENTIAL ApplJobRunner WITH IDENTITY = 'DOMAIN\ApplLogin', SECRET='password'

   ApplJobRunner is just a name and you can choose whatever you like. The Windows user and the password is what you got from the domain admin.
3. In Object Explorer, you go to the node for SQL Server Agent and then to Proxies. Select Operating System (CmdExec) and then New from the context menu. Enter Proxy name (a name you choose) and the name of the credential from step 2.
4. On the first page, set sa or some other non-person who is sysadmin as the job owner.
5. When you create the job step, you change the job type to Operating system (CmdExec) and then from the Run as dropdown, select the proxy you created in step 3.
6. The text for the job step would be something like this:

   SQLCMD -I -b -S $(ESCAPE_DQUOTE(SRVR)) -d dbname -Q"EXEC somesp"

   (The ‑I option forces QUOTED_IDENTIFIER to be ON. The ‑b option ensures that if the procedure fails, the job will also be reported as failed. The ‑S option is followed by an Agent token which expands to Server\Instance. This saves you from having to edit the job step if the database and the job is moved to a different server.)

SQL Server Agent retrieves the credential from SQL Server and performs a login into Windows with the username and password stored with the credential. This means that everything runs just like a login from the application, and therefore access to linked servers works without restriction. Obviously, you will need to give DOMAIN\ApplLogin access to those remote data sources as well.

Some practical notes:

• Obviously, the persons responsible for the database need to know if a job fails. Set up alerts, so that the right persons get a mail if the job fails (and on success as well, if they want that).
• The power users of the database do not have access to the job history, but there is a good way to deal with this: on the Advanced page of the job step, you can direct that output is to be sent to a file. Note that this requires the job owner to be sysadmin, so this is another nail in the coffin for the option of setting the job owner to an application login. This file should be located so that powers users of the database have access to it. If the power users want to know details for how long time the job ran etc, they will need to cater for that inside their stored procedure and log to their database.
• If the users are not happy with letting the job to run on a schedule, but they want to be able to start it on a demand, there are a couple of possibilities, which I discuss in the chapter Letting Users Start Specific Jobs in the appendix to my article Packaging Permissions in Stored Procedures. This chapter can also serve as inspiration, if you need to package other msdb permissions into a stored procedure, for instance for sending mail.

Attacks through Deployment Scripts

So far, I have discussed situations where the attacker is someone who already has access to the SQL Server instance. In this chapter and the next, I will look at how persons who do not even have access to the instance of their own could hijack your permissions to gain access.

You are sysadmin and you get a deployment package/script from the application team to run for an application database. For the sake of the example, we could assume that your DBA duties in this case encompass both the server and the database. What do you do? Just run the script and be happy? Well, a developer could have managed to sneak in some code that creates a login for him and adds that login to the sysadmin role or something equally malicious. What could you do to protect yourself against this?

The first question to ask is what is the script or package supposed to do? Is it supposed to do things inside the database only, or will it also perform actions on server level like adding linked servers, jobs etc? If you have been involved in producing the package yourself, you are likely to know this already. If you were just given the script from the application team, I think it is reasonable to require that they provide documentation of what the deployment package will do on database and server level. But would it be that bad that you have no information at all, I would suggest that when you test the deployment script in your QA environment, you run it with this command:

 EXECUTE AS USER = 'dbo' WITH NO REVERT

There is a clause we have not seen before: WITH NO REVERT. This clause makes it impossible to revert back to your true self. The reason you should use this clause is that the evil developer may have sneaked in a REVERT in the script, and if that command is on top level, it would undo your impersonation, since they are in the same scope.

If you impersonate dbo this way, this means that any attempts to perform things on server level will fail, and you if notice something unexpected you would bring that up with the application team. "Unexpected" here does not necessarily mean a hijacking attempt, but it could just as well be something intended for the application that you have concerns over. For instance, they could be attempting to set the database TRUSTWORTHY because they have a procedure with EXECUTE AS OWNER, and they don't know about certificate signing.

If the update script needs to access something outside the database – and it may be something as uncontroversial as accessing a linked server – impersonating dbo will not do. Here are some possible lines of defence for this case:

1. Code review. This will certainly reveal blatant attempts where CREATE LOGIN is followed by an ALTER SERVER ROLE on sysadmin, but one could expect an attacker to be a little more skilled and hide his attempts. A good review should still find such attempts, but if there are many scripts with several thousand lines of code, you could easily miss it. And your busy schedule may not permit you to spend several hours on reviewing the script.
2. Automatic code scan. That is, you have a script that runs through the code and checks for suspicious operations. I don't have any such script to offer, but I have heard of more than one person who have written such a script, so Google may find you something. I will have to admit that my faith in this is limited, because I think the attacker will always be one step ahead and find a way to fly below the radar.
3. Running with a login with limited permissions. If you know that all the deployment script needs to do on server level is to access a linked server, you could set up a sandbox login and somehow arrange so that sandbox login has access to the linked server. (Keep in mind that if you want self-mapping to work, you cannot use EXECUTE AS LOGIN, but you actually need to log in as the sandbox login.) The same is true for a single action like bulk-loading definition data from a file; you would grant the sandbox login ADMINISTER BULK OPERATIONS or whatever the permission is needed for the action in question. But if the script performs multiple actions on server level (or in msdb), it may take some time to figure out the exact set of permissions that you need. And in the end you may find that there is something that requires sysadmin or at least CONTROL SERVER, which shoots down this option entirely.
4. Auditing. This may be the best option. You have a good server-level audit set up, and when the deployment script has completed, you review the audit log for suspicious activities, including auditing having been stopped. (Note that a server-level DDL trigger is not an option for auditing here, since the trigger can be disabled and re-enabled without leaving any trace. Enabling/disabling triggers does itself not fire DDL triggers.)

In the above I have assumed that you have a bare SQL script. But you may get a deployment package as a DACPAC or an MSI install. This may be more difficult to deal with. A truly security-minded and paranoid DBA might try to extract the SQL code one way or another, but far from all have the time or skills to do this. If you can run the install package with a login with limited permissions, by all means do that. But auditing may be the only option.

In the end, you may simply have to give up. So while the application team does not have access to the server, they will in practive have db_owner rights in the database, or whichever permission your run their deployment scripts with.

One step further of this is a deployment pipeline where checkins into the production branch results in an installation into production. As you may understand by now, this opens the possibility for a malicious developer to sneak in bad code. I can see the point with this sort of pipelining for a test or QA system. But I find it quite hard to swallow for a critical system, both from a security perspective and a stability perspective. At least if I am a DBA with no insight or involvement in that pipeline. If you are in this situation, you can try to have the pipeline to be executed with limited permissions, no stronger than membership in our_ddladmin, that is with ALTER ANY DATABASE DDL TRIGGER denied. Anything that goes beyond that should require manual intervention. But it may take a political battle establish that principle. Else? Auditing, I guess.

Attacks through the Application

You are sysadmin, but you are also the user of some applications. The time reporting system, a purchase-request system, you name it. You log into these applications with Windows authentication. But what you don't know is that a developer has sneaked in code to check if the current user is sysadmin, and in that case the application executes some malicious code. Maybe the code makes the developer sysadmin. Maybe it steals or manipulates data.

This could be code in stored procedures, but it could also be code that is submitted directly from the client, and not within your realm to control or review. And the attack may not be against the server that the time-reporting system is on. No, the developer knows that you are also the DBA for the very sensitive HR system or whatever, and sets up a connection in that direction.

What means of protection do you have here? On your own, there is not a lot you can do. Since you are going through an application, EXECUTE AS USER is not available to you. But with good standards in your organisation, this can be avoided. I have been in an environment, where I had two Windows accounts. One was a plain-vanilla account which I used to log in to Windows from my laptop. Then I used Remote Desktop where I logged on with my other Windows account to access a jump server from where I could access a sensitive SQL Server instance. So that is the key: for your daily stuff as an employee, you use an account with no power permissions, and for your qualified work as a DBA, you use a different account, but which is not good for mail and other activities. That account is only for administrating SQL Server. Rather than connecting through a jump server, it suffices to start SSMS with the RUNAS command for the qualified account (if the network topology permits it).

You don't have to be a sysadmin to be victim for an application attack. If it is only about stealing data, a developer of the less sensitive system B may check if a user also has access to the highly sensitive system A, and in that case, silently log in to that system and extract data to a file on the application server for B (to which the developer may have access). It goes without saying that it would not be a very good idea to have the databases for A and B on the same SQL Server instance. But as discussed above, the application may just set up a connection to the other server. Can this be prevented? Yes, at least as long as there is an application server for the sensitive system, so that the network can be segmented to only permit the application server and jump servers for the DBA team to connect to the instance for A. Would it be that bad that the application for system A is a fat Windows client sitting on the users' desktops, an application server could be introduced by moving the fat client to Terminal Server or Citrix.

Conclusion

In this article we have looked at some of the dangers you can encounter in SQL Server. More precisely, how users in your workplace could attempt to hijack your permissions and get you to run code that you should not run. Here is a summary of the most important lines of defence we have looked at:

• Use EXECUTE AS USER to remove all your permissions outside the current database.
• Don't put people directly in the role db_ddladmin, but put them in the user-defined role our_ddladmin which is a member of db_ddladmin, but which have been denied the permission ALTER ANY DATABASE DDL TRIGGER.
• Create a sandbox user or a sandbox login that has no permissions beyond those needed for the task. Typically, this permission set should align with the set of a normal application user.
• Use certificate signing when a stored procedure needs to perform an action that requires permissions beyond what is reasonable to grant to a regular application user.
• Agent jobs for applications should run with the same permission set as a normal application user.
• Use different Windows logins for your actual DBA duties on the one hand, and your plain everyday work like reading mail, reporting time etc on the other.
• Make sure that sensitive databases cannot be reached from application servers for other systems.

I'm making these suggestions because they are relatively simple to implement on a daily basis. Sometimes, there may be no other choice to make a complete review of the environment to make sure that there is no malicious code. However, I don't believe in code review as the major defence line, because it is too time-consuming (and boring) in the general case. Defence lines with such characteristics tend to fall apart easily.

I like to reinforce what I said in the introduction. You may find some of the attacks I have suggested to be far-fetched, and you may also think that an attacker would take too big risks by for instance trying to sneak something into a deployment script. That may be true, but it does not really matter. When it comes to security, there is really not anything which is too far-fetched or too risky when the stakes are high enough for the attacker. For instance, a disgruntled employee who is about to leave the company may not care too much for the risk of being discovered, when his sole aim is to cause as much mess as possible. No, when we work with security, we need to identify all possible means of attack there may be and make sure that they cannot be exploited.

In several places I have talked about local power users or other people as persons you don't trust. This may seem alien to you, because you think you trust your co-workers. But if you trusted them to 100 %, why don't you just make them sysadmin and smile? Ah, you don't trust them that much, after all. Maybe not in the sense that you think that they would perform malicious actions, but you don't trust that they have the skills to use the sysadmin permissions wisely. Again, that does not really matter. If you don't want to make them sysadmin (or db_owner or whatever) explicitly, you must also prevent that they don't sneak into that role behind your back.

What you should not underrate is the risk of a benevolent attacker. At the PASS Summit 2017, I listened to the presentation 5 Ways to Bypass *or Ensure* SQL Server Security by Matt Martin. This presentation was quite a bit of an eye-opener for me, not the least with regards to SQL Server Agent security. In this presentation, Matt told of tricks he had played in his early career as a junior developer to gain access as sysadmin or similar. Not out of malicious intent, but only to work around corporate red tape to be able to deliver the reports his business users wanted. And one of your colleagues could be the same. While it may be for a benevolent reason, they are certainly overstepping internal security regulations. And one day they will run a command they did not understand, but which causes a big mess. For you to clean up.

Writing about security is always precarious. The starting point for this article was about a year ago of this writing, when I was working with a revision of my article on certificate signing and particularly the script GrantPermsToSP_server.sql. I was considering various tricks the local database DBA could play to get access to the certificate for instance by help of DDL triggers. But it took quite a while until I realised that the local power user simply could use a DDL trigger to add himself to sysadmin. And that script had been up there in some shape or form for more than 10 years. That certainly was quite an embarrassing moment for me. I was able to identify the solution with EXECUTE AS USER = 'dbo' to remove that risk. Once I had made this realisation, I started to think more about it, and I identified the other scenarios in this article.

But what if I have made the same mistake again? The challenge with security is that you always need to think outside of the box to identify vulnerabilities. If you find some advice in this article that does not seem sound to you and which could be exploited, please let me know by mailing me at esquel@sommarskog.se. And for that matter, you are also welcome to make other comments on the contents – and that most emphatically includes pointing out spelling and grammar errors.

Finally, I like to express my thanks to my fellow Data Platform MVPs Thomas Grohser and Ola Hallengren as well as former MVP Andreas Wolter, at the time Program Manager for Security at Microsoft, for having been kind to review this article and making some very valuable comments to my original draft.

Revision History

2024-08-10

I got a mail from Reece Goding which pointed out that one particular passage was not very clear and improved that section by removing a sentence that was more confusing than helpful. I also made a general review of the article, to incorporate points from the session I have with the same name. They are all minor points, but nevertheless:

• In the beginning there is a note with a link to a recording of the aforementioned session.
• In the place where I say that you cannot use the UI in SSMS directly, I've added a remark that you can use the UI to generate a script with the commands and then bracket this script with EXECUTE AS USER.
• I introduce the role our_ddladmin as an alternative to db_ddladmin. This is a user-defined role that is a member of db_ddladmin, but which have denied rights to play with DDL triggers.
• In the section Attacks through Stored Procedures, I discuss options when you need to use a sandbox login and you need to access linked servers.
• In the section Agent Jobs, I point out that to direct output to a file, the job must be owned by sysadmin.

2021-01-16

First version.

Back to my home page.