December 16, 2004 at 6:08 am
I can't seem to make cross-database ownership chains function as documented for SQL2K SP3. In Db1 owned by sa, dbo.sp1 references dbo.view1 which references Db2.dbo.table1 through tablen. Db2 is also owned by sa.
exec sp_configure 'cross db ownership chaining', 1; reconfigure
has been run, which should allow chaining to pre-SP3 levels. But I still receive
SELECT permission denied on Db2.dbo.table1
for each table in Db2.
The probable cause is that I dumped and loaded all non-system databases from a dev system that had non-sa database owners and the database chaining option set off. At this point, I can't re-dump and load as the production data has changed. Permissions were set more permissively in the dev environment and thus we only ran into this issue when we went to prod.
Things I've tried:
1. DB level chaining options. Shouldn't need these if sp_configure option above set to 1.
exec sp_dboption 'db1', 'db chaining', '1'
exec sp_dboption 'db2', 'db chaining', '1'
2. Reset db owner. These were set to owned by sa before the dump, but I ran this anyway.
use db1; exec sp_changedbowner 'sa';
use db2; exec sp_changedbowner 'sa';
3. Recompile db1 stored proc and view from source.
4. Drop, recreate, repopulate tables in db2.
5. Create a new SQL Server user with same permissions as problem user account. Same results.
6. Stop and Start MSSQLSERVER service.
7. Reboot box.
Things I haven't tried:
1. Transferring master.dbo.syslogins from source server. There are many accounts that I don't want on my prod box. I may be forced to try this one.
2. Transferring logins with DTS. The service run on Local System for now and prevents me from transferring (if I wanted to).
Any other suggestions?
December 20, 2004 at 8:00 am
This was removed by the editor as SPAM
December 20, 2004 at 12:31 pm
You may need to run sp_change_users_login in the database that you have moved. I can't tell from your details, but the fact that you are thinking about moving master makes me think that you are using SQL accounts and those accounts are not in sync.
sp_change_users_login 'Auto_fix'
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 20, 2004 at 12:45 pm
Kathi,
Thanks, but we aren't worried about the SQL accounts. Its the domain accounts that are the cause of the trouble. And, I've tried creating a new SQL account with identical permissions on the prod box and got the same results. That led me to believe the syncing wasn't the issue - at least for the end user accounts.
Vince
December 20, 2004 at 12:51 pm
OK, I think I solved this one. It appears that the dev box had different, domain account owners of the database. After loading the first time, I ran sp_changedbowner 'sa' and this may have worked (having trouble remembering).
I then dumped again from dev and loaded into prod, and the owner remained sa on prod even though it was not sa on dev. That's when I had the trouble mentioned above. Running sp_changedbowner 'sa' did NOT work to fix things.
I changed the db owner to sa on dev and dumped and loaded yet again. This time it worked well.
Vince
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply