Don't Let Your Permissions be Hijacked!

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 discuss 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. The recording is a bit outdated, though, as I have updated the session and the article since.

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 no such thing as " too 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 contain malicious code. This is followed by a chapter where I cover how your permissions could be hijacked even in SELECT statements.

At this point I change the focus a little bit and discuss how you can write a stored procedure that performs privileged actions in a way that non-privileged users can run the procedure and where you have full control of how the permissions are applied. Thereby, you can avoid granting users powerful permissions in the first place. 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. In the last two chapters I look at attacks that could be conducted by developers who don't even have access to the server.

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.

It is certainly possible to identify more attack scenarios, but these appear 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 database, but no server-level permissions. This could be a consolidated server in a large corporation 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:

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 of 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:

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

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:

principal_id sid name type usage

---- ------------ ------- ------------------ -------------- ---------------

1 259 0x01050... DOMAIN\user WINDOWS LOGIN GRANT OR DENY

1 2 0x02 public SERVER ROLE GRANT OR DENY

1 3 0x03 sysadmin SERVER ROLE GRANT OR DENY

...

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.

principal_id sid name type usage

----- ------------ ----------- ------------------ -------------- ----------

0 259 0x010500... SOMMERWALD\sommar WINDOWS LOGIN DENY ONLY

0 2 0x02 public SERVER ROLE DENY ONLY

0 3 0x03 sysadmin SERVER ROLE DENY ONLY

...

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 hijack. At the same time, since 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 hijack 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.

Observe that in the second example, the USE command to switch to the target database must come before the EXECUTE AS command. If you have them in the reverse order, the USE statement will fail, since you have sandboxed yourself into a different database.

Msg 15517, Level 16, State 1, Line 152

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

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 database has a DDL trigger that performs actions outside the database that are perfectly legit. For instance, it may audit DDL operations to another database or to a linked server. These operations will fail if your maintenance job runs with EXECUTE AS USER. 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.

It is very common to let sa own all databases, but in my opinion this is not good practice. Instead, my recommendation is that each database should be owned by an SQL login that exists solely to own this database, and that has no other permissions. This prevents a security hole from opening up if someone accidentally marks the database as TRUSTWORTHY. (For a longer discussion on this, see the chapter EXECUTE AS and Server-Level Permissions in my article Packaging Permissions in Stored Procedures and this blog post from Andreas Wolter.)

There is also a consideration in the context of permission hijacking and the protection through the statement EXECUTE AS USER = 'dbo'. If the database is owned by sa, this means that you are on sa on server level. As we have seen, you don't have any server-level access. Or at least, you are not supposed to have any. But there could be specific situations where Microsoft has a glitch, so that you actually are understood as sysadmin nevertheless. Keep in mind that security is something that should consist of many layers, so if there is a hole in one layer, another layer will still protect you, and by establishing the principle that your databases should be owned by non-sysadmin logins, you add this extra layer.

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 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 prefers point-and-clicking over writing commands, this may make you feel a little uncomfortable. However, most dialogs in SSMS permit you to script the commands. So rather than using the OK button at the bottom, 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 are not the only means a malicious user can use to attack you. Stay tuned for how you can be attacked through other channels like DML triggers, stored procedures, and even SELECT statements. 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 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. This 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 yourself (to keep an eye of what the developers 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:

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 who need full rights to create tables, procedures 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 implies a database role, but as I said, it could also be an AD group. Whenever 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 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 the 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:

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', the job may fail due to a mismatch in dbo between the database and sys.databases, as I discussed in the previous chapter, as long as you have not embraced the habit of always explicitly set the database owner after restoring a backup.

On the other hand, the maintenance plans in SSMS do not have any similar option, so 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 of 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.

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 at 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 #8 is out of the question on a 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 #6, 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.

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:

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.

What if you want to take this route – despite my caveats – and still permit users to add good DDL triggers? You could add this line to the server-level DDL trigger:

IF is_srvrolemember('sysadmin') = 1 RETURN

to permit yourself to add DDL triggers. Users would send you the database DDL triggers they want, and you would review them before you deploy them. You should always modify the database DDL triggers by adding EXECUTE AS SELF:

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

(SELF? Because OWNER is not permitted for DDL triggers.) This ensures that the trigger will execute in an untrusted security context and it will not be able to perform actions outside the database. This includes any code invoked by the DDL trigger, for instance a DML trigger on auditing tables (so if they stick nasty code there, it will not work). When reviewing the DDL trigger, you refuse to accept the statement EXECUTE AS CALLER which takes the trigger out of the sandbox. Tell users that they need to use original_login() for proper auditing, since SYSTEM_USER & co will return the impersonated context.

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 has asked you to 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:

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 impersonated dbo, since you are sandboxed into the database. I would suggest that the best in such a situation is to read data from these sources into temp tables before accessing the user database and downgrading your permissions. You may get the idea to join with the tables in the target database to reduce the size of these temp tables, but don't do that. As we shall learn later, a mere SELECT on a table can be a threat.

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 now feel 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 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 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 the permission 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 work in database B, you issue this command:

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 on a table that you write to through the application, and this trigger checks whether the user has access to A, and if so, makes use of these permissions 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 the data is that sensitive. We will return to this theme in the chapter Attacks through Applications.

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 might be a simple way to protect yourself against this threat, to wit, this database DDL trigger:

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 to 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 to 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 that 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 to 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 a 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 idea 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 the 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 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:

In case you have not seen this form of CREATE USER before: This is a way to create a user that is "orphaned" by design. Such users can be practical for a number of things. For instance, say that you set up a database role that should be able to do A, B and C, but absolutely not D, you can create a user WITHOUT LOGIN and then impersonate that user to test that you got it right. If you are in the db_owner role, but you have no server-level permission so that you can create logins, this is a very practical feature.

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 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, 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. Only use a sandbox login, when there is an actual need for it. That is, when there actually is a need for cross-database or server-level access.

If you only have db_owner rights and you need to perform an action that could be dangerous and which includes access outside the database, you cannot create a sandbox login yourself, but you could speak nicely to the DBA to have her/him to create a sandbox login for you and grant you IMPERSONATE rights on that login. Else you may have to rely on code review.

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?

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. 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 the 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, preventing 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:

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 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.You may think that since you are making an explicit check for DDL triggers, you don't need to perform the first EXECUTE AS USER, but that statement should be a matter of routine before you run commands in a database where someone else has the powers to create DDL triggers.

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 3 is a little different.

Attacks through SELECT Statements

You might have been thinking: I'm only running a SELECT statement. Nothing could go wrong, even if this is a rogue database. Well, I should not come down too hard on you, since in an older version of this article there was a place where I said: As long as you are only selecting data, there is no attack surface. I was wrong, and I am deeply indebted to Baptiste Devigne who took me out of my fallacy. No, not even a simple SELECT statement is without risk.

The starting point here is that although we may think of user-defined functions as read-only operators, it is possible to perform hijacking attacks from inside a function, because there are a few loopholes that I will come back to after first a brief discussion on how the attacker would "package" a prepared function.

The attacker would not necessarily lure the victim to run the function explicitly. Rather the attacker would sort of hide it. If it is a scalar function, the attacker could call the UDF from a computed column, and then lure a user with interesting permissions to query that table. That user could be you as sysadmin, someone in the db_owner role, or someone with permissions in another database for a cross-DB attack.

A variation is to rename a table (or move it to a different schema), which the attacker knows that the intended victim queries regularly. The attacker writes a multi-statement function that returns the data in the table, but also runs malicious code. The attacker completes the trap by creating a view with the same name and columns as the original table on top of the TVF. To keep updates on the table working, the attacker writes INSTEAD OF triggers on the view. (If the attacker does not expect the attacked user to write to the table, there is little point in putting malicious code here.)

So what loopholes are there with user-defined functions that would permit permission hijacking attacks? I've identified four possible ways to exploit a user-defined function.

In the following, I will discuss these loopholes one by one. I should point out that while I present these mechanisms as specifically something that can be employed through user-defined functions, the attacks as such can be carried out from DDL triggers, DML triggers and stored procedures as well. And they can certainly be easier to implement in such modules that do not suffer from the restrictions of user-defined functions. But if the attacker knows that you will never write anything to the database, at least not without first running EXECUTE AS USER, the attacker may try a UDF in the hope you have your guard down.

But you should not. If you are in a database where there are users who are in the db_owner role or hold other permissions that permit them to to set up hijacking traps, you should always employ EXECUTE AS USER to impersonate dbo or a sandbox user/login, even if it is only a matter of querying data. Don't forget that if you are a plain user in the potentially dangerous database, you can always impersonate yourself to get rid of your permissions outside that database.

As you will see, for some of these attacks, you can also get protection by following best practice for configuration. But while you should adhere to these best practices, these configuration settings do not cover all attack vectors. So you will still need EXECUTE AS USER – no way around that.

Extended Stored Procedures

When user-defined functions were introduced in SQL 2000, Microsoft did for some reason leave a loophole that permits you to call certain extended stored procedures from user-defined functions. Here is an example of a malicious function waiting to hijack sysadmin permissions when they become available:

The function checks if the current user is a member of sysadmin and whether xp_cmdshell is enabled. If this is the case, the function goes on checking if DOMAIN\User has a login, and if not the function adds that user by calling xp_cmdshell which runs SQLCMD. When you run this function (most likely hidden as discussed above) as sysadmin, the connection for SQLCMD will be by the service account for SQL Server who is a member of sysadmin, so the CREATE LOGIN command will succeed. Next, the function checks if this DOMAIN\User is a member of sysadmin, and if not, the function performs the same trick to make that user sysadmin. xp_cmdshell normally produces a result set as output, but you can suppress it by passing 'no_output' as the second parameter.

When it comes to the particular attack with xp_cmdshell, it is quite easy to keep yourself protected against it: Simply adhere to best practice and keep xp_cmdshell disabled. Yes, you may be using xp_cmdshell in your convenience scripts, but change these scripts to enable xp_cmdshell when you need it and to disable it when you are done.

Note: You may think that you are safe, because you have set up a proxy account for xp_cmdshell. However, the proxy account is only used for low-privileged users. When you run as sysadmin, xp_cmdshell will still run under the context of the service account for SQL Server.

xp_cmdshell is not the only extended stored procedure you can call from a user-defined function. Here is a script that lists all extended stored procedures that are permitted to call from within a UDF:

The script finds all extended stored procedures (type X) and runs a cursor over them, and for each procedure, the script creates a function that attempts to execute the procedure. The script then calls the function inside TRY-CATCH. If error 557 is raised, this means that the call is not permitted, and the script moves on to the next procedure in the cursor. Else the name is printed.

When I run this on SQL 2025, it prints 65 procedures. Of these, a few more are protected by configuration options just like xp_cmdshell. The most significant group is the sp_OAxxxx procedures, which permits you to run OLE objects from inside SQL Server. These procedures are disabled by default, and you should keep them disabled, unless you have a legacy application that absolutely needs these procedures. There are also the configuration options to control whether the XPs for SQL Server Agent and Database Mail are enabled. However, it is not unlikely that you have a business need to keep these procedures enabled, so there may not be a lot of protection here.

But when it comes to the main bulk of these 65 procedures, they are enabled by default and there are no means to disable them. Some of them are very innocent like xp_msver or xp_sscanf. The majority of these procedures are undocumented, and there is more than one I have no idea what it does. But I can tell that some are quite powerful. For instance, xp_servicecontrol permits you to start any service on the machine, and xp_regwrite permits you to write anywhere in the registry. While that sounds scary, these actions will only succeed if the service account is local Administrator in Windows – which would be in direct violation of best practice.

There are still threats, though. There is one place in the registry where SQL Server has write access, and that is some of the keys (but not all) under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLnn.INSTANCENAME. Some of these write permissions are needed so that we can use SSMS to set configuration options that are stored in the registry rather than in the master database. This includes the default folders for data, log and backup files and not the least the settings for SQL authentication and login auditing. When you make changes to these settings, SSMS calls xp_instance_regwrite to write the new values. Another example of parameters you set in this way are linked-server provider options, for instance whether the provider is permitted to run in-process.

Thus, an attacker could sneak in code to have you making configuration changes you don't want to happen. Maybe an application team wanted you to enable SQL authentication, because they want to use an SQL login as the application login but you said no, and you told them to use Windows authentication. Now they are "solving" their problem this way. Or maybe someone is planning a brute-force attack on the password of sa or some other powerful user and has you to first turn off auditing of login failures so that the failed logins will go unnoticed. There is also the scenario with a disgruntled colleague who wants to create a mess before leaving the company and has you running a UDF that makes some crazy changes in the registry.

OPENQUERY and OPENROWSET

Both these rowset functions permit you run an SQL batch on a remote data source. That batch is intended to be a single query that returns exactly one result set. But there is nothing to stop you from adding other statements to that batch, as long as you return a result set. Thus, an attacker could add code to make a user sysadmin or to steal or manipulate data.

While the attacks involving extended stored procedures, as far as I can see, only can be directed against sysadmin, attacks with OPENQUERY and OPENROWSET can be used to attack any user that holds a permission that is interesting for the attacker.

Of these two functions, OPENROWSET is the most powerful for an attacker. With OPENROWSET you can make a connection to a remote data source without a linked server being defined. You can specify that the connection should be made with integrated security, which means that the batch will execute with the permissions of the current user on the remote data source. This makes the function very appealing for a permission hijacker. The remote data source could be any SQL Server instance on the network, including the local instance itself (a so-called loopback server). If you are a DBA who is sysadmin on many instances in the company, this is quite a scary thought!

There is a very simple means of protection when it comes to OPENROWSET: This function is only available if the configuration option Ad Hoc Distributed Queries is enabled. By default it is disabled, and I strongly recommend you keep it disabled. It's better to use linked servers.

Sure, with linked servers, the exactly same trick can be played with OPENQUERY. But with OPENQUERY, the attacker can only attack the servers for which there are linked servers defined on the system, something which you as a DBA has full control over. (Unless you have granted ALTER ANY LINKED SERVER to someone you shouldn't have granted it to.) This means that the attack surface typically is a lot smaller than with OPENROWSET.

Furthermore, it depends on how the linked server is set up. The access to the remote data source may be through a username/password stored in SQL Server that applies for everyone. While this is dubious from a general security perspective, there is no risk for permission hijacking here, since everyone has the same access rights on the linked server. It's a different matter if the mapping is set up only for some users, but not everyone. This is better from a general security perspective, but users who have access to the linked server may be of interest to an attacker who has not. Linked servers may also be set up with self-mapping – that is, users access the remote server with their own credentials. While this is the best from a general security perspective, such linked servers are the most interesting for a permission hijacking attack – not the least against someone who is sysadmin on the remote server.

The CLR

From a CLR module, you can run SQL statements on the so-called context connection. That is, they run on the same session from which the CLR module was invoked, so there is no new login. This can be done from any type of assembly, including SAFE assemblies. It seems that when you call a CLR function, you are only permitted to do read-only operations on the context connection. However, I am not sure that I would like to trust this from a security perspective. I would not be surprised if there are loopholes that permit you to run write operations, like for instance adding users to sysadmin. And if so, such a function could be used to perform a hijacking attack from a SELECT statement. (This is sort of a general security principle: If you don't understand a feature well enough to tell if it can be used for an attack, you need to assume that it can be.)

Note: A second way to run SQL statements from a CLR module is to open a new loopback connection back to the server. If this connection is made with integrated security, it will run under the context of the service account for SQL Server and be sysadmin. In the context of permission hijacking, this is not a threat for the simple reason that once the attacker has managed to get this module in place, he or she can run it directly and have full control over the server. No need to hijack someone else's permissions! An assembly of this type requires EXTERNAL ACCESS permission, and thus requires approval on server level. As you understand, if a developer approaches you and wants to install an assembly which requires EXTERNAL ACCESS or UNSAFE permission, you need to tread very carefully. Such a database should most likely be on a server on its own.

Query Store

Say that you want to know the salary of a certain employee. You are the application admin of a database and you have identified a user in your database who also has access to the payroll system, and you know the schema of this database. So you set up a hijacking attack to have this user to run queries against the Employees table on your behalf. However, this user only runs SELECT queries in your database, so there is no point in injecting queries in stored procedures or triggers which would permit you to write the data to a table in your database. As discussed above, you can inject queries against the payroll system into user-defined functions, but since they cannot update tables, so how would you actually get hold of the data?

In your function, you first read the salary (which you possibly have to do through OPENQUERY or OPENROWSET) into a variable. Then you use that variable in a dummy query with SELECT TOP or similar. That dummy query is only executed if you get a match in the payroll table. That single execution will appear in sys.query_store_runtime_stats and the column avg_rowcount will reveal the salary for you.

Yes, it is contrived, and it is cumbersome and tedious. But it is not impossible. And that is what counts when it comes to security: if an attack is possible, it is a threat.

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 that other article. Nevertheless, I recommend that you read the article in full, even if it is long, so that you get a full understanding of the technique. Using an advanced security feature without knowing how it works can be precarious, as you may use it in the wrong way.

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 with the certificate.

When you want 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 it) 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, but nothing else.

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

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.

If the stored procedure employs dynamic SQL, you need to be careful, though. The developer may purposely have left an SQL-injection hole so that he later can inject malicious commands which executes with the packaged permissions. Thus, you need to verify that what is intended to be an object name is properly wrapped in quotename etc. When in doubt, don't agree to sign.

Note: You may be familiar with the clause WITH EXECUTE AS OWNER that you can add to a procedure header. This is also ia 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 of 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 also 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:

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 it creates a new certificate and redoes 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 couple of 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 for that reason. 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:

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

I have added this to all places where the script runs DDL in the user database – exactly to prevent 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.

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 you grant that user the permissions needed on database level to run the procedure. You impersonate that login with EXECUTE 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 resides in the B database which 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 analogous:

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 have granted, 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.

When you are running commands in database B, don't forget to first run EXECUTE AS USER = 'dbo' to rid yourself of your permissions in A, so that the B admin cannot hijack your permissions through a DDL trigger.

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

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 issue #4, you need to write your stored procedure so that all access in your database is wrapped in dynamic SQL as in this example:

That is, you call sp_executesql in the context of your database. Then you sandbox the SQL batch to your database by having the current user to impersonate itself with EXECUTE AS USER. Only if you follow this pattern, the B admin can feel confident that you are not cheating on him. Would he see any statement that accesses anything in your database directly, he should refuse to run your procedure. Nor should he agree to the appearance of the REVERT command anywhere.

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):

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 owning 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 that the job owner will be added to the role SQLAgentUserRole in msdb. This does not happen immediately, but the next time you install a Cumulative Update or a security GDR. In this case, 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 when I tested this. (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.)

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:

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 restrictions. Obviously, you will need to give DOMAIN\ApplLogin access to those remote data sources as well.

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?

You know the base for the answer already: EXECUTE AS, but there are two things to consider. The first is that since you are running a script, a malicious user could have sneaked in the REVERT command in the script. Thankfully, there is a simple remedy for this: the WITH NO REVERT clause as in this example:

This clause disallows the REVERT command, so if there is a REVERT command in the script, it will produce an error. (And if that happens, there is all reason to contact your security department!).

The other thing to consider is which user/login should you impersonate? The answer to this question is a combination of what you want to permit and what the script is supposed to do. If you impersonate dbo, you are essentially saying that the application team is db_owner in the database – even if they don't have access to the SQL Server instance as such. To wit, I'm making the assumption that the script is so long that it is not realistic for you to review it all.

Note: One option here could be automatic code scanning. That is, a utility that scans the code for potentially malicious actions. I have heard people saying that such things exist, but I don't know of a specific tool, so I will have to refer you to Google. Personally, my faith in the idea is low, because I have the unnerving feeling that the attacker will be one step ahead and find a way to fly below the radar.

And this could be alright, because all you want to protect is the server itself, you may trust the application team to understand the database as such better than you do. On the other hand, if they are only supposed to deploy tables, stored procedures etc, you should not use dbo, but a sandbox user who is a member of our_ddladmin.

The script might include legit actions on server level that you are prepared to accept, for instance adding a linked server or bulk loading a data file. In this case, you would use a sandbox login for which you have tailored the permission set. To find out what is the exact set of permissions you need, you request the application team to document what the script is doing. Before you run the script in production, you should run it in a QA environment. If you get permission errors, it does not have to be because some developer has sneaked in something evil. It is more likely that the application team has been oblivious and forgotten a thing or two. If there is a real hijacking attempt, the attacker is likely to be smart enough to check the permissions before running the attack. Or the script may check the server name, and only run the attack on the production server. In other words, because it ran well in QA, does not mean that you can have your guard when you later run it in production.

The script could include legit actions that you are prepared to accept as such, but which requires permissions that are too powerful to be granted to a sandbox user/login. In this case, you need to request that the application team provides these actions in a separate, short, script that you can review in full. I should point that this could be both actions on server level and database level. For instance, if the application team is supposed to only work with tables and procedures, they may still want to use certificate signing to package permissions in stored procedures (which is an excellent idea). In that case, they will need to supply you with a separate script with the calls to GrantPermsToSP, and you will need to review the procedures in question.

In practice, it may not be very common that DBAs get a plain installation script, but rather the deployment is packaged into something. One popular packaging is the DACPAC which you run with SqlPackage. The DACPAC describes a model of the database, and SqlPackage figures out steps to transform the database from its current schema to the schema described by the DACPAC. Running this directly on a production database seems unwise to me for a number of reasons, but you can tell SqlPackage that rather than executing the commands, it should produce a script. You can then add EXECUTE AS USER = 'dbo' WITH NO REVERT on top of that script. You would of course test in QA before doing it live. Although, if you are brave enough to trust the application team that it will work from a functional perspective, you can run SqlPackage to deploy directly, since SqlPackage has options to control the authentication, permitting you to specify a sandbox login. (I need to add that my own experience with DACPACs and SqlPackage is very limited, and I never used it for deployments myself.)

Another possibility is that you are looking at an install package from a vendor which provides you no means to extract any script and on top of all it may insist on being executed as sysadmin. I guess the only way out in this dire situation is auditing. Assuming that the package performs a lot of actions on database level, auditing on that level may produce too much information to be useful, but you should be able to track what is going on server level. As for how to set up the auditing, that goes beyond the scope of this article, but SQL Server Audit is likely to be the best option. Most important: it audits that it has been turned off, an action that you definitely should label as suspect. Some readers may have a preference for DDL triggers, but that is not an option in this context. DDL triggers can be disabled with DISABLE TRIGGER, a command that itself does not fire DDL triggers. Thus, this permits evil code in the installation package to disable auditing silently.

Another challenge are things like a deployment pipeline. A developer checks in some piece of code in Git and 30 minutes later it is in production. This is scary for more reasons than just security. One would like to think that policies are set up so that pull requests to the production branch always must be reviewed by some people – and that you as the DBA are one of them. If that is not the case, at very least you could require that the pipeline connects to the server with a login that has limited permissions, preferably nothing beyond membership in our_ddladmin. (That is, the account should have no permission to manipulate DDL triggers.)

Attacks through Applications

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 to a jump server with my other Windows account and from here 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 a victim of 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 sensitive SQL Server instance for A. Would it be that bad that the application for system A is a thick Windows client sitting on the users' desktops, an application server could be introduced by moving the thick 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:

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 be taking 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 them from sneaking 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 lure you to 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 in the autumn of 2019, when I was working with a revision of my article on certificate signing and particularly the script GrantPermsToSP_server.sql. I was considering if there were any tricks the local database DBA could play to get access to the certificate for instance by using 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.

With one exception: the attacks through SELECT statements. It was not until four years later after I first had published the article you are reading that Baptiste Devigne pointed out to me that a UDF could be used to call extended stored procedures in a permission-hijacking attack and how that could be used from a computed column. It was not that I was not aware that this particular loophole existed, but I was well acquainted with it. (I've occasionally employed it for testing locking and isolation levels.) It was simply the case that the thought this ability could be used for permission hijacking never :had occurred to me. That is not the end of it. After Baptiste's mail, I investigated the possibilities with extended stored procedures and worked with a revision of the article only from that angle. Then, eventually, after over a month, it came to my mind to think about other attack possibilities that could be employed from inside a UDF, like OPENQUERY and OPENROWSET..

Here lies the challenge with security. We may think we have a sound security design. However, there is a hole due to a feature that we are not aware of, but which the attacker could use to get past our barriers. Or we are aware of the feature, but we simply overlook the fact that it could be used for the type of attack we want to protect us against. When working with security, we constantly have to think outside the box – before an attacker thinks the same thought.

And who says that I have reached the end of the line and the article now covers all attack vectors? There may be more ways of hijacking that I have overlooked. Or, even worse, maybe some of the means of protection I have suggested have holes which makes them insecure? 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: Things I could explain more clearly, details that are inaccurate etc. And not the least, I very much welcome if you to point out any spelling or grammar error that you see,

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. And again my thanks to Baptiste Devigne for pointing out how SELECT statements could be exploited.

Don't Let Your Permissions be Hijacked!

Introduction

Attacks through DDL Triggers

Attacks against sysadmin

Attacks against db_owner

Cross-DB Attacks

An Elaboration on Maintenance Jobs

DML Triggers

Attacks against sysadmin

Cross-DB Attacks

Attacks against db_owner

Attacks through Stored Procedures

Attacks through SELECT Statements

Extended Stored Procedures

OPENQUERY and OPENROWSET

The CLR

Query Store

Writing Your Own Stored Procedures

The db_owner Case

The sysadmin Case

The Cross-DB Case

Agent Jobs

Attacks through Deployment Scripts

Attacks through Applications

Conclusion

Revision History