January 14, 2008 at 2:23 am
Recently we have restored our database in new serever.obejects in the databse are crearted under different userser[owner] not under dbo.when we are trying to accessing data in the objects with out specifying owner from databse.we are unable to access data.
eg: select * from table_name.
if w specify the owner name then we can access the data from the table.
eg: select * from [object_owner.table_name]
we need to acces the data with out specifying the object_owner name.
if any one come accoss this issuse please provide me solution.
Thanks
S.yugandhar
January 14, 2008 at 3:39 am
If you cannot use the same user to access the database, and want to be able to access it as dbo without prefixing the owner, you will need to change the object owners.
Have a look at 'sp_changeobjectowner'
Warning: Changing owners is mostly OK for tables, but you must check textual objects like stored procedures, views, functions, triggers, etc and fix their definition (Ideally these objects you should drop, and then create with the new owner) if they were using the owner explicitly. Also, make sure that in these textual objects you did not have explicit references that would use the two part names.
The task sounds large, but you can search the textual object definitions (see the syscomments table) assuming they were not encrypted. Dropping and creating them should not be bad either if you were not using schemabinding. Otherwise, what you could do, is to script the database, searc/replace on the script, then copy the data.
Regards,
Andras
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply