May 15, 2009 at 3:27 pm
Hi, i recently moved my databases from server using dettach and attach moving the files throught the network, everything worked fine except for one of this databases which gives me problems while using the Cross DB Ownership Chaining on SQL 2000 SP3. Everytime i try to give permissions to an object that references this particular database (named Projects) users wont get the inherited permissions too. I don't know why this happens if ALL my databases have sa user as owner and all objects have dbo as owner, if I have a view for example which has:
CREATE VIEW dbo.v_example
AS
select * from Projects.dbo.table
And I grant select (having Cross DB enabled and running under configurations) to a user "user1" for example on v_example, users recieve the following message:
'Select permission denied on Projects.dbo.table'
While if the view had reference to a table in any other database it works, even though as I said all have sa as owner and all tables have dbo as owner.
What could be happening?
THanks!
May 19, 2009 at 11:07 am
Hi guys
No one has replied, I'm wondering if I didn't made myself clear or it is a very weird error you can't figure how to solve?
Thanks
May 19, 2009 at 11:53 am
If you have the problem with the user permissions you better use sp_change_user_login autofix option.
May 20, 2009 at 7:31 am
after dettach and attach db, even in same instance, you need to reenable cross-db ownership chaining.
May 20, 2009 at 10:50 am
After attaching the db i tried running this:
sp_configure 'Cross DB Ownership Chaining',1
RECONFIGURE WITH override
but it didn't helped, I also runed this:
sp_change_users_login 'AUTO_FIX','user'
On the user that I'm granting select permission on a view wich references with select a table on the database that has the problem but no.
May 20, 2009 at 11:18 am
i guess that in sql 2000, you need to make sure the user has select permission on underly table if it belongs to different user.
April 7, 2010 at 4:33 pm
The database owners should be same for bothe databases
Use sp_changedbowner "sa" assuming that "sa" is mapped to "dbo" and each object in your database is owned by "dbo"
Hope this helps
Sudheer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply