April 22, 2014 at 6:27 am
Hi all
I am getting the following error when trying out DB chaining and Ownership chains
Msg 916, Level 14, State 1, Line 1
The server principal "user" is not able to access the database "Test" under the current security context.
I did run these scripts to setup DB Chaining
ALTER DATABASE [Test1] SET DB_CHAINING ON;
ALTER DATABASE [Test] SET DB_CHAINING ON;
I then setup user using a sysadmin role with select permission to a view. The View is on Test1 and points to Test database using a synonymn setup on Test1.
According to all the articles I have read this is all I should do and the view should then run. Am I just missing something silly.
Andre
April 22, 2014 at 7:34 am
Have a look at this thread sounds like a very similar situation to yours http://www.sqlservercentral.com/Forums/Topic1413413-1526-1.aspx
Sorry for the short reply, but I have to run 🙂
April 22, 2014 at 8:07 am
Thanks, will test this tomorrow, looks from what I saw in that article that it could be the ownership of the DB's. Will respond tomorrow then.
One question though on security, how much of a risk is it, I see MS do warm about the security risks. If there are other Companies DB's on the same server we just could secure it making sure our db owners are not the sql db owners but our own unique users?
April 23, 2014 at 3:53 am
The suggestion works, only one concern I have is that we need to at minimum create a user on the database even though it does not have rights on the databases.
Example
User has select rights to the one view on database A. The view points to database B where the user has been created but without any rights. Is there no way of keeping the user off database B completely?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply