Wednesday, March 7, 2012

Database Detach - orphan users

I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> > I've used the sp_'s from this article:
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> > And still got orphaned users.
> >
> > Here's what I did:
> >
> > 1. Script users on master server with sp_'s in KB article.
> > 2. Detach
> > 3. FTP
> > 4. Attach
> > 5. Load users from sp_'s output
> >
> > user databases still had no link to sql server logins
> > (not using windows auth by the way, only sql server logins)
> >
> > I had to use sp_change_users_login but I should not have had to.
> > What went wrong ?
> >
> >
> >
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Possibly logins already existed with the same name on the destination
> database. Did you get any
>> errors from step 5? That would explain it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ben" <null@.void.com> wrote in message
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>> > I've used the sp_'s from this article:
>> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
>> > And still got orphaned users.
>> >
>> > Here's what I did:
>> >
>> > 1. Script users on master server with sp_'s in KB article.
>> > 2. Detach
>> > 3. FTP
>> > 4. Attach
>> > 5. Load users from sp_'s output
>> >
>> > user databases still had no link to sql server logins
>> > (not using windows auth by the way, only sql server logins)
>> >
>> > I had to use sp_change_users_login but I should not have had to.
>> > What went wrong ?
>> >
>> >
>> >
>

No comments:

Post a Comment