October 24, 2008 at 11:54 am
At risk of instigating numerous posts warning me of the dangers of Cross Database Ownership Chaining, here's my dilemma. This is all taking place on a SQL Server 2005 Standard Edition with SP2 installed.
I have 2 databases, called A and B.
In database A I have a stored procedure, owned by dbo, which contains just a simple SELECT statement to a table on database B. This table in database B is also owned by dbo.
Cross database ownership chaining is turned on in both databases A and B, since it is turned off at the server level.
I grant execute permission to the stored procedure in database A to User1. When the user attempts to execute the stored procedure, the following error is returned:
The server principal "username" is not able to access the database "B" under the current security context.
Regardless of the numerous paranoid theories I've tried examining for why this might be the case (including database owners, schema owners, table owners) I just can't seem to find why it's not working the way I expect it. The only way I can get the stored procedure to execute is to add the user also to database B, even though I don't grant any permissions on any objects to that user in database B.
Any ideas are appreciated.
October 24, 2008 at 12:13 pm
In order for ownership chaining to work there are 3 more things that must be setup that I have not heard you mention yet:
1) the source database must be TRUSTWORTHY
2) The Owner of the stored procedure in database A must have a Server Principal (usually a "Login") that has a database principal in B (i.e., a "User", or other access means, such as "sa") to be able to access the objects in B.
3) The Server principal of the User in Database A that is running the proc or view must have CONNECT access to database B.
For some reason they do not explain this very well in BOL.
[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]
October 24, 2008 at 12:31 pm
Thanks for the quick response. Do you have any additional thoughts when I mention the following (particularly #1 below)?
3) I probably wasn't very clear in the last paragraph of my original post. I get it to work by adding the user to database B for the login that is mapped to the user in database A with EXEC permissions on the stored procedure. This meets the GRANT CONNECT requirement. (I've verified GRANT CONNECT permissions via sys.database_permissions)
2) Both databases A and B are owned by sa, which maps to user dbo in both databases.
1) If I grant connect to a user in database B mapped to the login, I don't seem to need to have the originating database marked as TRUSTWORTHY.
October 24, 2008 at 12:35 pm
Tim Benninghoff (10/24/2008)[hr1) If I grant connect to a user in database B mapped to the login, I don't seem to need to have the originating database marked as TRUSTWORTHY.
I may be getting the Requirements for the Cross-Database Server-Setting mixed up with the Database-Setting.
[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]
October 24, 2008 at 12:40 pm
rbarryyoung (10/24/2008)
I may be getting the Requirements for the Cross-Database Server-Setting mixed up with the Database-Setting.
Good to know. Thanks for your help.
October 24, 2008 at 12:43 pm
Tim Benninghoff (10/24/2008)
3) I probably wasn't very clear in the last paragraph of my original post. I get it to work by adding the user to database B for the login that is mapped to the user in database A with EXEC permissions on the stored procedure. This meets the GRANT CONNECT requirement. (I've verified GRANT CONNECT permissions via sys.database_permissions)
OK, then the answer to your original question is: Cross-Database chaining only grants access to the objects of another database. It does NOT, however, grant access into the other database itself. And you must first be able to get into the other database, before it will check for object access.
That may seem dumb (I think so), but that is how it works and it is actually documented somewhere in BOL. Sure, all you have to give someone is CONNECT access to the database and nothing else, but you do have to do that for each user or group that you want to be able to use the Cross-Database chaining.
[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]
October 24, 2008 at 12:48 pm
In SQL Server 2000 SP3 and later, no CONNECT to the database needed to be explicitly made...or was there some other mechanism at work that I didn't know about that made it seem automatic?
October 24, 2008 at 2:54 pm
There were a lot of security changes in SQL 2005.
[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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply