December 14, 2007 at 6:03 am
I have just split an existing system contained in one database up into two databases.
Database A contains some tables and all the stored procs / functions etc..
Database B contains just settings tables. (this DB will be shared across a demo/prod structure)
The website will always connect to A through a certain user and any sql statements that need to connect to B are all fully qualified. The user only has read access from the DB and is granted execute permission on any stored procs that do updates/inserts etc. This is all working fine.
However there is a certain stored procedure in database A that needs to have higher permissions as it maintains tables for indexing and needs to be able to truncate and re-create those tables. Therefore I created a special user e.g. user_indexer and on the stored procs in question used
with execute as 'user_indexer'
In the old system (1 database) this worked fine with no problems. However since splitting the DBs up I now get the following error:
The server principal "user_indexer" is not able to access the database "database_B" under the current security context.
I have granted this user the following permissions in BOTH databases it needs to access
db_datareader
db_datawriter
db_ddladmin
which is all it requred in the old system (1 DB) for it work with no problems.
I have even tried granting this user every single DB and Server role as possible just to see what happened but it still fails.
The same error happens when I run this proc from the website or from QA logged in as myself who is has System Administrator rights (although I am the developer).
The logic of the proc is this
Proc A is called with execute as 'user_indexer'
This proc accesses some tables within Database A (in a loop) and calls Proc B
Proc B does not have with execute as 'user_indexer' defined on it (but it doesn't make any difference if it does anyway).
As soon as Proc B tries to access some tables in Database B it errors
e.g. select col from databaseB.dbo.someTable
I have been told that cross DB ownership chaining is disabled and the actual system Administrator says he doesn't want to enable it for some reasons as he has read its a bad idea. What I have read up about it though is that I shouldn't need to enable it anyway if the user in question has the appropriate login rights in both databases concerned which it certainly does.
Does anyone have a solution or any advice regarding this problem.
Thanks in advance for any help given.
December 14, 2007 at 6:19 am
Have a look at the trustworthy database property. This is needed on 2005 for impersonating stored procedures to access items outside the database. To set it:
alter database foo set trustworthy on
Do read about the security implications in Books Online.
Regards,
Andras
December 14, 2007 at 6:52 am
Thanks that did the trick perfectly!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply