September 29, 2008 at 12:02 pm
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
September 29, 2008 at 12:23 pm
You either need to give the user access to the objects in both databases, or you need to enable cross-database ownership chaining.
September 29, 2008 at 12:28 pm
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
September 29, 2008 at 12:35 pm
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.
September 29, 2008 at 1:42 pm
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
September 30, 2008 at 11:40 am
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
September 30, 2008 at 1:33 pm
Have you set your Web database to be TRUSTED?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 3:39 pm
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
September 30, 2008 at 3:47 pm
Yes, thats it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 5:52 pm
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
September 30, 2008 at 10:15 pm
Glad I could help!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply