Warnings up front, this has some serious security implications. The method I’m going to use minimizes that somewhat but it’s really easy to shoot yourself in the foot here, so be careful!

Impersonation allows you to grant a user the ability to mimic another user and gain access to all of the permissions that the impersonated user has. However if you have worked with this much you will know that you can only impersonate database level permissions. Or can you?

To start with data base level impersonation. There are 2 users, UserA and UserB. UserA is dbo and UserB can impersonate UserA. UserB can do anything that dbo can by impersonating UserA. We want to apply the principal of least privilege wherever possible so we start by only granting UserA those privileges that UserB needs to impersonate. However, we will probably soon have a UserC that needs a subset of those permissions. We don’t want to have to create yet another user to be impersonated, and we don’t want to grant UserC the extra privileges that UserA has. The solution is to create stored procedures that do the work. These stored procedures then use the EXECUTE AS clause to have the stored procedure run as if another user is actually running it. Then we grant execute access to that stored procedure.

An excellent example of this is creating a stored procedure that truncates a table.

-- Create table to truncate
CREATE TABLE TruncateMe (Id int NOT NULL IDENTITY(1,1))
GO

-- User that has permission to truncate the table
CREATE USER Imp_TruncateMe WITHOUT LOGIN
-- Grant user ALTER permission so it can truncate the table
GRANT ALTER ON TruncateMe TO Imp_TruncateMe
GO

-- Create procedure to do the truncate impersonating Imp_TruncateMe
CREATE PROCEDURE dbo.Truncate_TruncateMe
WITH EXECUTE AS 'Imp_TruncateMe'
AS 
TRUNCATE TABLE TruncateMe
GO

Now in order to give someone permission to truncate our table we don’t have to grant the IMPERSONATE permission, or even the ALTER TABLE permission, we can just grant EXECUTE to the stored procedure.

That’s great but we want to impersonate a server level permission. To start with we need the TRUSTWORTHY setting of databases. So what does TRUSTWORTHY do? If the TRUSTWORTHY database setting is set to ON then the instance trusts EVERYTHING in that database. This means that any impersonated user in the database will have the ability to use the permissions of the associated login. This can have some pretty serious security implications. Personally I don’t know every implication of using TRUSTWORTHY but I think this one is pretty significant on it’s own.

Here is the scenario, you want to grant a junior DBA access to run DBCC HELP. Unfortunately this DBCC command requires membership in the sysadmin server role. You aren’t quite ready to give your junior DBA sysadmin permissions so you need a work around.

FYI if this seems contrived, it is. I couldn’t come up with a good server level permission on the fly. This should be good enough to get the point across though.

As a method of minimizing risk I put my “impersonation” stored procedures in a separate database when I’m setting TRUSTWORTHY ON. And even more particularly when I’m using a permission that requires sysadmin. Why? Because no matter how careful you are mistakes happen. Application databases tend to have fairly complicated permissions and eventually someone is granted db_owner and you have forgotten that they can now generate stored procedures that can impersonate a login with sysadmin permissions. I want a database where the users only have CONNECT to the database and EXECUTE to specific SPs. And those are the only users in the DB. That means that the SPs have to be created by a sysadmin, but I’m ok with that restriction.

In general the process runs like this:

1. We create a new database and set the TRUSTWORTHY flag on
2. We create a login with the permissions we want
3. Set the login as the owner of the new database
4. We create a stored procedure that does the work we want within the new database.
5. We add the EXECUTE AS OWNER clause to the SP

I ran several tests here and the only way I could get it to work was by using EXECUTE AS OWNER. EXECUTE AS ‘UserName’ would not work even with TRUSTWORTHY ON. I did not try anything other than setting up the “OWNER” (schema of the SP) as dbo. It might work with a different schema but I suspect not.

-- Create a login to be the owner of our impersonation database
USE master
GO
-- Make the password as obnoxious as possible because 
-- no one ever needs to or should log in as this login.
CREATE LOGIN Imp_DBO WITH PASSWORD = 'VeryStrongPassword', 
CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
GO

-- Create database with TRUSTWORTHY set ON
-- to contain our impersonation SP
CREATE DATABASE ImpTest WITH TRUSTWORTHY ON;
GO
-- Change owner to login created for the purpose
ALTER AUTHORIZATION ON DATABASE::ImpTest TO Imp_DBO;
GO

-- Grant the login the permissions needed
USE master;
GO
-- In this case sysadmin is required but only use it 
-- if it is REQUIRED!
ALTER SERVER ROLE sysadmin ADD MEMBER Imp_DBO;
GO

-- Create stored procedure to mimic DBCC HELP
USE ImpTest
GO
-- SP must be in the dbo schema for this to work.
CREATE PROCEDURE dbo.MyDBCCHelp (@dbcc_param varchar(50))
WITH EXECUTE AS OWNER
AS 
DBCC HELP (@dbcc_param)
GO

-- Create a login to test with
USE master
GO
CREATE LOGIN Imp_User WITH PASSWORD = 'VeryStrongPassword', 
CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
GO

-- Create user in the ImpTest database and 
-- grant it execute to dbo.MyDBCCHelp
USE ImpTest
GO
CREATE USER Imp_User FROM LOGIN Imp_User
GO
GRANT EXECUTE ON dbo.MyDBCCHelp TO Imp_User
GO

Open a connection using the new login and test.

EXEC ImpTest.dbo.MyDBCCHelp 'CHECKDB'

Now our new login can run a sysadmin only DBCC command and the only permissions it has are to connect to the new database and to execute a stored procedure. I could even put additional controls into the new stored procedure if I wanted to, logging for example. I should note that you want to use ORIGINAL_LOGIN if you are logging user information when using impersonation in order to get the original login name.

Obviously this won’t work with views and functions as there is no EXECUTE AS clause.

Last time, when using this method of impersonation I create a separate database that has the absolute minimal security inside of it. The only users are those that need to execute the stored procedure(s) and they only have execute on the specific stored procedure(s) that they need. This avoids giving any access I don’t intend. You can create a world of problems if you aren’t careful.

Filed under: Impersonation, Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tagged: code language, database permissions, Impersonation, language sql, microsoft sql server, security, server permissions, sql statements, T-SQL