MSDE 2000 SQL server from a peer to peer server to a server running Small
Business Server 2003 using OSQL. (In other words I’m moving the database to
a dedicated server.) I performed a RESTORE DATABASE xxx FROM DISK=
‘PATHNAME’ WITH REPLACE
OSQL responded with “RESTORE DATABASE successfully processed xxx pages” Now
I need to run some stored procedures to fix the orphaned user issue, but when
I attempt to login to the database with user “sa” no matter what password I
use I can’t get in. (I’m not sure there is a user “sa” in the restored
database). From what I’ve read, I believe a may need to run the
sp_changedbowner command, but I’m not sure what login I need to change to.
Also, I DID NOT move any "system files" ie master.mdf or model.mdf files. Do
these have to be moved as well? Any suggestions would be greatly
appreciated. Thanks in advance.
Mark G
First, you need to know the new SQL Server (on your Smaill Business
Server2003) has mixed security mode enabled (Only Windows security is
enabled by default installation). If not, you would obviously cannot use
"sa" ( and cannot use any username/password pair in that matter).
To use OSQL.exe tool, you do not have to use "sa" though, you can use -E
swicth with osql.exe to log into SQL Server with trusted connection (Windows
Authentication). Of course you need to log onto your computer as local
admin.
"Mark Grantom" <mgrantom@.swbell.net[no spam]> wrote in message
news:D218EA2E-9D0B-4372-8E22-A58E8FBA1F96@.microsoft.com...
>I am a complete neophyt to SQL. I am attempting to restore a backup from a
> MSDE 2000 SQL server from a peer to peer server to a server running Small
> Business Server 2003 using OSQL. (In other words I'm moving the database
> to
> a dedicated server.) I performed a RESTORE DATABASE xxx FROM DISK=
> 'PATHNAME' WITH REPLACE
> OSQL responded with "RESTORE DATABASE successfully processed xxx pages"
> Now
> I need to run some stored procedures to fix the orphaned user issue, but
> when
> I attempt to login to the database with user "sa" no matter what password
> I
> use I can't get in. (I'm not sure there is a user "sa" in the restored
> database). From what I've read, I believe a may need to run the
> sp_changedbowner command, but I'm not sure what login I need to change to.
> Also, I DID NOT move any "system files" ie master.mdf or model.mdf files.
> Do
> these have to be moved as well? Any suggestions would be greatly
> appreciated. Thanks in advance.
> --
> Mark G
|||hi Mark,
Mark Grantom wrote:
> I am a complete neophyt to SQL. I am attempting to restore a backup
> from a MSDE 2000 SQL server from a peer to peer server to a server
> running Small Business Server 2003 using OSQL. (In other words I'm
> moving the database to a dedicated server.) I performed a RESTORE
> DATABASE xxx FROM DISK= 'PATHNAME' WITH REPLACE
> OSQL responded with "RESTORE DATABASE successfully processed xxx
> pages" Now I need to run some stored procedures to fix the orphaned
> user issue, but when I attempt to login to the database with user
> "sa" no matter what password I use I can't get in. (I'm not sure
> there is a user "sa" in the restored database). From what I've read,
> I believe a may need to run the sp_changedbowner command, but I'm not
> sure what login I need to change to. Also, I DID NOT move any "system
> files" ie master.mdf or model.mdf files. Do these have to be moved
> as well? Any suggestions would be greatly appreciated. Thanks in
> advance.
chances are the problem is related to authentication... MSDE installs by
default only allowing trusted connections and not allowing mixed security
connections relating to SQL Server logins and password credentials.. if
this is the case, you can have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 for further
info about how to "hack" the Windows registry to allow trusted and mixed
security authenticated connections..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||OK, I forgot to say, I did log in with entrusted option to do the initial
RESTORE. I can change to the mixed login option by modifying the registry no
problem. Do I then move the "system files" or not. If so, how? Thanks
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> chances are the problem is related to authentication... MSDE installs by
> default only allowing trusted connections and not allowing mixed security
> connections relating to SQL Server logins and password credentials.. if
> this is the case, you can have a look at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;285097 for further
> info about how to "hack" the Windows registry to allow trusted and mixed
> security authenticated connections..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> OK, I forgot to say, I did log in with entrusted option to do the
> initial RESTORE. I can change to the mixed login option by modifying
> the registry no problem. Do I then move the "system files" or not.
> If so, how? Thanks
why do you like to "move" system database as well? transferring system
databases between different instances is not a very safe operation.. I'd
not...
you've better "stay" with the new instance ones... you "only" have to
re-create all the "original" desired/required logins and remap them to your
user's databases via that system stored procedure you already mentionned,
so_change_users_login..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||I went back and read my messages twice. I don't "like" to move system
databases. In fact I have NEVER moved a system database. I'm not entirely
certain WHAT a system database is although I do have my suspicions. Remember
I am a rank beginner with SQL. I did ask twice, whether I NEEDED to move
system databases. I take it from your response that the answer is NO? Also,
I checked my system and the "mixed" login mode was already enabled in the
registry. I don't believe that I have a user "sa". How do I create this
user? Thanks.
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> why do you like to "move" system database as well? transferring system
> databases between different instances is not a very safe operation.. I'd
> not...
> you've better "stay" with the new instance ones... you "only" have to
> re-create all the "original" desired/required logins and remap them to your
> user's databases via that system stored procedure you already mentionned,
> so_change_users_login..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> I went back and read my messages twice. I don't "like" to move system
> databases. In fact I have NEVER moved a system database. I'm not
> entirely certain WHAT a system database is although I do have my
> suspicions. Remember I am a rank beginner with SQL. I did ask
> twice, whether I NEEDED to move system databases. I take it from
> your response that the answer is NO?
you usually do not have to... when you installed the "new" SQL Server/MSDE
instance it installed with it's own system databases... so you are ok with
them..
> Also, I checked my system and
> the "mixed" login mode was already enabled in the registry. I don't
> believe that I have a user "sa". How do I create this user? Thanks.
"sa" login can not be created... it's created at installation time.. and you
have it for sure
what is the exact exception you are reported with when connecting using "sa"
credentials?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||I get:
Login failed for user 'sa'.
Mark G
"Andrea Montanari" wrote:
> hi Mark,
> Mark Grantom wrote:
> you usually do not have to... when you installed the "new" SQL Server/MSDE
> instance it installed with it's own system databases... so you are ok with
> them..
> "sa" login can not be created... it's created at installation time.. and you
> have it for sure
> what is the exact exception you are reported with when connecting using "sa"
> credentials?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||hi Mark,
Mark Grantom wrote:
> I get:
> Login failed for user 'sa'.
with something else, like "not associated with a trusted connection"?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
No comments:
Post a Comment