April 14, 2005 at 12:11 pm
Hello all. This is driving me nuts and I don't find the cause.
I have a server with a datawarehouse DB (DWDB) and another DB to store some related tables (OLDDB).
To get info from dwdb to OLDB what I do is a view with SELECT * FROM DWDB.dbo.table
This to avoid granting direct select permissions onto DWDB tables (some confidential fields) so, I decided to re-activate cross-database ownership on both DBs (no other owner than me and sa, so no risks). With this I only added the OLDDB user as a DWDB user (only has "public" role access) and works perfect.
Now, some months after, I'm creating another DB (same server) and I want to do the same process (accessing DWDB without direct table access). But isn't working!!!
I've created DB (NEWDB), created the view and activated cross-database ownership on NEWDB, created login and added user to both databases (NEWDB and DWDB) and granted select on view (onto NEWDB), but errors referring to missing permissions came up!
OLDDB is still working wihout problems, I've ever tryed the SAME view query on both DBs and only works in OLDDB database
What I'm doing wrong? I'm going crazy :-\
Thanks
April 14, 2005 at 3:08 pm
Not clear if you've checked this already... Make sure dbo maps to the same login (ideally, sa) in all databases. (All objects are owned by dbo, right?)
April 14, 2005 at 3:21 pm
I'm kind of embarrased. Your're totally right, dbo wasn't sa for the new database.
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply