December 12, 2007 at 2:55 am
Morning,
We have just changed Domains from AB to XY. I am running a Distributed Query across servers with the format select data from ServerName.Database.dbo.TableName This now produces an error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." It worked before the Domain Migration.
On looking at the Database to be queried I notice that the dbo login name is for a user who has recently left and shows the old Domain AB\ExStaffLogin. If I look at the properties of the Database I am the owner and properties shows the new Domain as well - XY\MyLogin.
My thought is that the query is trying to access the dbo login, which no longer exists. I have tried sp_changedbowner in order to change the dbo login to myself but get an error "The proposed new database owner is already a user in the database."
Is my thought process for the cause of the error correct? How can I change the login for user dbo to myself?
All thoughts/suggestions most welcome. Thank you.
Colin
December 13, 2007 at 10:51 am
Hi. From the BOL.... you have to remove the access to the DB for the id before it can become the owner.
sp_changedbowner [ @loginame = ] 'login'
[ , [ @map = ] remap_alias_flag ]
Arguments
[@loginame =] 'login'
Is the login ID of the new owner of the current database. login is sysname, with no default. login must be an already existing Microsoft® SQL Server™ login or Microsoft Windows NT® user. login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first. ]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply