we are moving our back-end db from Informix to SQL Server. In Informix tables had a number of owners which through the Qualification tab I removed the reference to the object owner.
In SQL Server, the objects (tables) are owned by a new user that did not exist in the Informix database. When I log into the catalog using the owner account of the SQL version of the database, I can run reports. As a non-owner account, I can log into the catalog, but not run any reports.
My hunch is that I need to change the reference to the old object owners to the new object owner. I don't see a method for doing that. I tried editing the catalog in a text editor and replacing the old owner name with the new one, but then the catalog fails to open.
Is there a method or tool that will allow me to do a mass change of all the tables within the catalog to reference the new owner of the object?
As a test I recreated a small part of the catalog using the SQL Server tables and I am able to access and run reports as both the db owner and guest without a problem...so I know the guest account works. I'd hate to have to rebuild the catalog as it uses a large number of tables and joins, so I'm hoping there is an alternative method.
Thanks in advance for the help.
Hi,
I believe you need to go into SQL - Enterprise Manager and change the permissions for the account in each table, to select. That should enable the account/s to write reports.
Also, you can write a simple SQL statement to permitt access to all tables.Ã, The code looks like the following:
GRANTÃ, SELECTÃ, ON [insert table name].[here]Ã, TO [insert: account name here]
GO
I hope this helps,
Brian
I kind of did the same thing but through the editing the properties of the user account in enterprise manager...still no luck.
do you think a problem would exist if the table in question has relations set up to other tables....would that then mean all the related tables would require the permission change even if the query (report) only pulls from the one target table?
That sounds logical to me. I would go ahead and as long as it won't compramise any of your security regulations, then change the permissions on all of the tables to ensure that you don't have that problem again.