March 27, 2002 at 5:24 pm
I got confused when I try to export data between two databases. The confusion is that the EM appends my user name to the objects which I try to export. For example, EM puts SOURCE="[databaseA].[dbo].[objectname]", DESTINATION="[databaseB]..[objectname]" even though the "user" has got the "db owner" right. If I want to have: SOURCE="[databaseA].[dbo].[objectname]", DESTINATION="[databaseB].[dbo].[username], what should I do? What kind of security I should choose for the logins used in both the source database and the destination database in the "Export Data" wizard?
I tried a bit further as below.
I change to use "sa" as login in the destination database, it seems it is working the way I want. Does it mean that I have to assign "system Administators" server role to the user in databaseB in order to tell the "Export" that I want to keep [dbo] as the transferred object's owner? Should I do the same for the login used in source database?
Here is my side-question. If I create a object to a database, how can I make sure that it shows "dbo" as the owner of this object? It seems to me that only has a "db owner" right does not enforce this.
April 1, 2002 at 9:14 am
It may be due to the account you are using to connect to the servers in EM. Check your registration properties for how you are connection to both and make sure if you use a specific account that it has db_owner access to the database you are exporting to.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 1, 2002 at 4:28 pm
Antare:
I paid special attention to the data access of the login I use to connect to the 2 databases. All the experienced I have, the only thing I can enforce the owner of the transferred object is to use a login which has "System Administrator" server role. Only "db owner" is not good enough for this purpose. But I want to make sure if this is the case.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply