Using “Execute As” in a Stored Procedure To Access A Separate Database

Here is a scenario that I often run across. A client wants me to create an add-on to a pre-existing application that runs on SQL Server. I don’t want to touch the existing application and database (let’s call this database PROPIETARY) so I create a separate database (we’ll call it TECHKNOWSOLVE) that queries the existing database. Now, in the PROPRIETARY database is a table (often an employees table) that contains all sorts of confidential information (like salary, age, social security number, etc) that I don’t need. In the TECHKNOWSOLVE database I just need a few columns from this table, like LastName, FirstName, and EmployeeID.

Here’s what I do. I create a job that runs a stored procedure once a day. In the stored procedure I delete the data in the TECHKNOWSOLVE Employees table and then repopulate it with the data I need from the PROPRIETARY Employees table. All works well since the job runs under my login and I am a system administrator.

But what if users need to update the TECHKNOWSOLVE Employees table before the next job runs? If I create a button for them within my application that runs the stored procedure, they will get an error unless I give them select permission on the Employees table in PROPRIETARY and that’s a bad idea. I could grant column permissions within the Employees table in PROPRIETARY but I’d really like to touch the PROPRIETARY database as little as possible. So instead, I make a change in my stored procedure.

Here’s the relevant part of the original procedure:

Create procedure uspGetEmployees
as
insert into tblEmployees select employee_no, first_name, last_name
from PROPRIETARY.dbo.employees

Here’s the change:

Create procedure uspGetEmployees
with execute as ‘DOMAINNAMEAllPowerfulUser’
as
insert into tblEmployees select employee_no, first_name, last_name
from PROPRIETARY.dbo.employees

Now if you run this you will get an error: The Server principal “User” is not able to access the database “model” under the current security context. That’s because you’re not done yet. There are a few steps you’re going to have to take before you can get the stored procedure to work. This MSDN article gives some excellent information about extending database impersonation but I thought it also left a few things out, thus my tutorial here.
 

The first thing you have to do is make sure the OWNER of your database, in this case TECHKNOWSOLVE, is also a user in the PROPRIETARY database. Once you’ve done that, you have to grant that user authenticate permissions in the PROPRIETARY database. Here’s how you do that:

  • In the PROPRIETARY database, under Security and then users, right-click on the user who is the owner of TECHKNOWSOLVE. Choose Properties
  • Click on Securables. Click Add
  • Choose Specific Objects and Click OK
  • Click on the Object Types button, click Databases and Click OK
  • Click on the Browse button
  • Select the applicable database (PROPRIETARY) and click OK.
    Down below you will see all the permissions. Scroll down until you see Authenticate.
    Check Grant and click OK

Ah, but we’re not done yet! Now we have to tell the SQL Server that our TECHKNOWSOLVE database is TRUSTWORTHY.

In the Master Database, run this query:

ALTER DATABASE TECHKNOWSOLVE SET TRUSTWORTHY ON;

Now you should be ready to go, but you could still run into trouble. If you don’t have Service Pack 1 or later installed and you use this with a stored procedure that includes transactions and rollbacks, you could get the following error: A severe error occurred on the current command. The results, if any, should be discarded. This error will be fixed by installing the latest service pack.

You may also get this error: The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YOURDATABASENAME’. You should correct this situation by resetting the owner of database ‘YOURDATABASENAME’ using the ALTER AUTHORIZATION statement.

This blog post gives good information about this error. The easiest solution I found was to change the database owner of TECHKNOWSOLVE to sa, using this command:
EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false

 

Advertisements