After Restore Master DB, User can't logon

Dear all,
My Lab has one SQL Server which used by ApplicationA, during
the installation process of ApplicationA, ApplicationA will
add New Logins for one local user group, and add one domain
useraccount to be a member of this local user group, i want
to simulate disaster rescovery of ApplicationA, so i do the
steps listed bellow:
(1) backup all related SQL database, inclued Master DB Full Backup
(2) Install one new Windows 2003 Server
(3) create local user group used by ApplicationA
(4) Install SQL Server and SP3a in this new Server, and every
installation options is the same as old server
(5) Install ApplicationA
(6) Restore all Database from backup media
(7) make sure no error happend when restore
after restore database, domain user account which is a member
of that local user group can't logon go SQL Server, when use SQL
Query Analyzer, error message appear:
can't connect to server SQLServerName:
Server:Message18456,Level16,Status1
Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
if i delete SQL logins for that local user group, and then new logins
for that local user group, the domain user account can logon successfully,
so i think although the name of local user gorup is the same, but
the sid of local user gorup is different, because this is the new Windows
2003 Server.
Am i right? if i'm right, anything i can do to resolve this problem
thanks for any reply
Rico
Problems about orphaned users ?
http://msdn.microsoft.com/library/de...tabse_0ttf.asp
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Rico" <rico_chang@.msn.com> schrieb im Newsbeitrag
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right? if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>
|||Hi
Yes, the SID is different.
You are in a domain, so why are you using local groups? As you see, in a DR
scenario, it becomes difficult to work with. If this was a cluster, you
would have the same problems.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rico" <rico_chang@.msn.com> wrote in message
news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
> Dear all,
> My Lab has one SQL Server which used by ApplicationA, during
> the installation process of ApplicationA, ApplicationA will
> add New Logins for one local user group, and add one domain
> useraccount to be a member of this local user group, i want
> to simulate disaster rescovery of ApplicationA, so i do the
> steps listed bellow:
> (1) backup all related SQL database, inclued Master DB Full Backup
> (2) Install one new Windows 2003 Server
> (3) create local user group used by ApplicationA
> (4) Install SQL Server and SP3a in this new Server, and every
> installation options is the same as old server
> (5) Install ApplicationA
> (6) Restore all Database from backup media
> (7) make sure no error happend when restore
> after restore database, domain user account which is a member
> of that local user group can't logon go SQL Server, when use SQL
> Query Analyzer, error message appear:
> can't connect to server SQLServerName:
> Server:Message18456,Level16,Status1
> Microsoft][ODBC SQL Server][SQL Server]User'Test\testuser' logon failed
> if i delete SQL logins for that local user group, and then new logins
> for that local user group, the domain user account can logon successfully,
> so i think although the name of local user gorup is the same, but
> the sid of local user gorup is different, because this is the new Windows
> 2003 Server.
> Am i right? if i'm right, anything i can do to resolve this problem
> thanks for any reply
> Rico
>
|||Dear Mike,
thanks for your reply, in fact, ApplicationA means MOM Server 2005,
MOM Server will create some local groups
Rico
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> glsD:uKfxmfhWFHA.3584@.TK2MSFTNGP14.phx.g bl...
> Hi
> Yes, the SID is different.
> You are in a domain, so why are you using local groups? As you see, in a
> DR scenario, it becomes difficult to work with. If this was a cluster, you
> would have the same problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rico" <rico_chang@.msn.com> wrote in message
> news:%23MSOGZhWFHA.1152@.tk2msftngp13.phx.gbl...
>