Stored Procedure Ownership Chaining

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Stored Procedure Ownership Chaining

11 posts, Page 1 of 2

1 2 »»

Stored Procedure Ownership Chaining

Rate Topic

Display Mode

Topic Options

Author

Message

Jeremiah Peschka

Posted Monday, September 29, 2008 12:02 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:06 PM
Points: 30, Visits: 174

I have several stored procedures in my database that are used to load data from a datamart that is housed in a separate database. These procedures are, generally, in the form:

CREATE PROCEDURE load_stuff
WITH EXECUTE AS OWNER AS
INSERT INTO my_db.dbo.report_table
(
  column_a
)
SELECT
  column_b
FROM data_mart.dbo.source_table
WHERE
  foo = 'bar';

These run fine when I execute the query in SQL Server Management Studio. When I try to execute them using EXEC load_stuff, the procedure fails with a security warning:

The server principal "the_user" is not able to access the database "data_mart" under the current security context.

The OWNER of the sproc is dbo, which is the_user (for the sake of our example). The OWNER of both databases is also the_user and the_user is mapped to dbo (which is what SQL Server should do). Is this because the user in question is being aliased as dbo and I should use a different user account for my cross-database data access?

I understand that this is happening because cross database ownership chaining is currently disabled, but what is the best practice in this scenario?

Thanks,

Jeremiah

Jeremiah Peschka
Microsoft SQL Server MVP
Managing Director - Brent Ozar PLF, LLC

Post #577899

Michael Earl-395764

Posted Monday, September 29, 2008 12:23 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

You either need to give the user access to the objects in both databases, or you need to enable cross-database ownership chaining.

Post #577909

Jeremiah Peschka

Posted Monday, September 29, 2008 12:28 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:06 PM
Points: 30, Visits: 174

Michael Earl (9/29/2008)

You either need to give the user access to the objects in both databases, or you need to enable cross-database ownership chaining.

In this case, dbo owns both databases and all objects in both databases. dbo is mapped to the same server level login. Is the user to login mapping what is derailing the access in this case? If I use a different user, everything runs as it should.

Jeremiah Peschka
Microsoft SQL Server MVP
Managing Director - Brent Ozar PLF, LLC

Post #577913

Michael Earl-395764

Posted Monday, September 29, 2008 12:35 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

Whatever server login being used must have access to the procedure and the table individually if the procedure is accessing a table in a different database. So, your server login needs to be granted access to both databases and given permissions to both objects.

Post #577915

Jeremiah Peschka

Posted Monday, September 29, 2008 1:42 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:06 PM
Points: 30, Visits: 174

From BOL:

Server or database principals other than sa or dbo can call EXECUTE AS.

The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal.

So, the ultimate solution is to create a new user for these load processes and force the sprocs to EXECUTE AS the load user.

Jeremiah Peschka
Microsoft SQL Server MVP
Managing Director - Brent Ozar PLF, LLC

Post #577948

Jeremiah Peschka

Posted Tuesday, September 30, 2008 11:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:06 PM
Points: 30, Visits: 174

I'm clearly not understanding something with ownership chaining.

I have created a database login, Processor, and given processor data_reader and data_writer permissions to both my datamart and web databases.

I then created a stored procedure, Select_EstimateReport WITH EXECUTE AS 'Processor'. Select_EstimateReport pulls from both the web and datamart databases.

When I run the query in Select_EstimateReport in SSMS as my own user, everything runs correctly (which I expected since I am a member of the dbo role on both databases, but not sysadmin on the server). However, when I attempt to run the sproc that I created, I get a "The server principal XYZ is not able to access the database under the current security context" error message.

The underlying goal is to keep our data mart separate from the web reporting database.

What should I be doing differently/in addition to get this working correctly?

Thanks,

Jeremiah

Jeremiah Peschka
Microsoft SQL Server MVP
Managing Director - Brent Ozar PLF, LLC

Post #578523

RBarryYoung

Posted Tuesday, September 30, 2008 1:33 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

Have you set your Web database to be TRUSTED?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #578634

Jeremiah Peschka

Posted Tuesday, September 30, 2008 3:39 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:06 PM
Points: 30, Visits: 174

rbarryyoung (9/30/2008)

Have you set your Web database to be TRUSTED?

I have not. Do you mean that I should set the TRUSTWORTHY option? That is the only thing I could see in Books Online for SQL 2005.

As in:

ALTER DATABASE data_mart SET TRUSTWORTHY ON;

Jeremiah Peschka
Microsoft SQL Server MVP
Managing Director - Brent Ozar PLF, LLC

Post #578729

RBarryYoung

Posted Tuesday, September 30, 2008 3:47 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

Yes, thats it.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #578734

Jeremiah Peschka

Posted Tuesday, September 30, 2008 5:52 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:06 PM
Points: 30, Visits: 174

Thank you! While I haven't tested this at work, I re-created the same situation at home using multiple databases and was not able to EXEC any stored procedures until I SET TRUSTWORTHY ON for both databases.

Jeremiah Peschka
Microsoft SQL Server MVP
Managing Director - Brent Ozar PLF, LLC

Post #578774

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions