I'm attempting a cross-server DB copy, and I _think_ I'm following all the rules from http://msdn.microsoft.com/en-us/library/ff951624.aspx, however I am seeing a "login failed" message
when attempting the copy.
I have two servers (in order to protect the innocent, I'll call them "source" and "destination" here)
In order to answer the permission questions that I will inevitably be asked, here is a sequence of queries running against my servers to show that I meet the criteria outlined on the above page:
1)Both servers must have the same Login (with the same password)
SELECT SERVERPROPERTY('ServerName') 'ServerName', db.Name 'DbName', logins.Name 'Owner Login'
FROM sys.databases db
INNER JOIN sys.sql_logins logins
ON logins.sid = db.owner_sid
Execute this in "master" on "source":
ServerName DbName Login
source MyDb sourceadmin
execute this in "master" on "destination":
SELECT SERVERPROPERTY('ServerName') 'ServerName', logins.Name 'Login'
FROM sys.sql_logins logins
In order to ensure both servers have the same password, I re-executed in master on both servers:
ALTER LOGIN sourceadmin WITH PASSWORD = 'xxxxxxxx'
2) On destination server, login must be have dbmanager role:
select SERVERPROPERTY('ServerName') 'ServerName', dp2.Name 'RoleName', dp.name 'Member'
from sys.database_role_members drm
inner join sys.database_principals dp
on dp.principal_id = drm.member_principal_id
inner join sys.database_principals dp2
on dp2.principal_id = drm.role_principal_id
ServerName RoleName Member
destination dbmanager sourceadmin
destination db_owner dbo
3) On the source server, the login must be the DBO of the db (same query from 1) above shows that sourceadmin is the DBO of the DB MyDb)
With that established, I execute the following in the destination.master, while logged in as sourceadmin:
CREATE DATABASE MyDb AS COPY OF source.MyDb
Only to get the error message:
Msg 40532, Level 11, State 1, Line 1
Cannot open server "source" requested by the login. The login failed.
I can't figure out what I'm doing wrong?
Do you know of the Affinity Group? I think for what you're doing, you may be able to benefit from it.
Microsoft is conducting an online survey to understand your opinion of the Msdn Web site. If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.
Would you like to participate?