Restore DB**

Hi
I've backed up my DB called DB1 in SQLServer 2000,
which include some users that owns some objects in
the database such as tables,views,...and these users
related to some logins.
now I want to restore DB1 in another Server,
but my problem is how can I define my logins and make a
relation between them and DB1'users?
(note: if I want to restore my DB1 in first server
there's no problem cause the defination of related logins exists)
or is there anyway to restore these information
without any problem?
any help would be greatly apprreciated.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Hi,
After restoration you can use the system stored procedure to fix the login
issue,
sp_change_users_login
Please refere BOL for more infomation. The only issue is you may need to
create the logins manually based on the first server. After that you can run
the above procedure with required parameters.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||This link should help you
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:
> This link should help you
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
> HTH
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:
> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>> Hi
>> I've backed up my DB called DB1 in SQLServer 2000,
>> which include some users that owns some objects in
>> the database such as tables,views,...and these users
>> related to some logins.
>> now I want to restore DB1 in another Server,
>> but my problem is how can I define my logins and make a
>> relation between them and DB1'users?
>> (note: if I want to restore my DB1 in first server
>> there's no problem cause the defination of related logins exists)
>> or is there anyway to restore these information
>> without any problem?
>> any help would be greatly apprreciated.
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

restore database users-logins mapping lost

i have a DB with DB users that are mapped to server logins
after backing the databasr and restoring on other server (with the same
logins defined)
the logins are not mapped to the sql user
wanting to do this from the security folder in enterprise manager, pressing
the login
in database access tab checking the required checkbox for the newly restored
database
(and the DB user correct name mapping appears)
pressing OK button gives the error Error 21002: [SQL-DMO]User 'appUser'
already exists.
what is the best way to achive the mapping from the login to the user
without deleteing anything and re-defining database permissions for the
user?
thanks.
My preference is to use sp_SidMap. You can get it here
http://download.microsoft.com/downlo...us/Mapsids.exe
The accompanying KB article is here
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default...;en-us;Q240872
Some other KB articles that may be useful are
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default...;en-us;Q168001
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default...;en-us;Q274188
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"z f" <nospam@.nospam.com> wrote in message
news:eJ2jd$LWGHA.4484@.TK2MSFTNGP02.phx.gbl...
>i have a DB with DB users that are mapped to server logins
> after backing the databasr and restoring on other server (with the same
> logins defined)
> the logins are not mapped to the sql user
> wanting to do this from the security folder in enterprise manager,
> pressing the login
> in database access tab checking the required checkbox for the newly
> restored database
> (and the DB user correct name mapping appears)
> pressing OK button gives the error Error 21002: [SQL-DMO]User 'appUser'
> already exists.
> what is the best way to achive the mapping from the login to the user
> without deleteing anything and re-defining database permissions for the
> user?
> thanks.
>
>
>
|||http://www.databasejournal.com/featu...le.php/2228611
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
|||Sorry meant to type a little description as well =P First link is for
migrating users from one server to another, It will create a script of
the accounts on the old server that you can run on the new server which
will create the correct sids etc.. so when you roll over a database
from the server1 to server2, u wont have orphaned users in the db on
server2 that dont have physical sql logins associated with them. That
should fix your problem. Make sure if youve added new users server2 to
remove them before running the script on server2 after u have it
created on server1 from the first link above
|||thanks
it is a killer script for me
i found something simpler:
sp_change_users_login (built in with SQL Server)
where did it hide?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23hf6DFMWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> My preference is to use sp_SidMap. You can get it here
> http://download.microsoft.com/downlo...us/Mapsids.exe
> The accompanying KB article is here
> INF: How to Resolve Permission Issues When a Database is Moved Between SQL
> Servers
> http://support.microsoft.com/default...;en-us;Q240872
> Some other KB articles that may be useful are
> PRB: User Logon and/or Permission Errors After Restoring Dump
> http://support.microsoft.com/default...;en-us;Q168001
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://support.microsoft.com/default...;en-us;Q274188
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "z f" <nospam@.nospam.com> wrote in message
> news:eJ2jd$LWGHA.4484@.TK2MSFTNGP02.phx.gbl...
>

restore database --> dropuser

Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.

Why this happens??

Regards,
ManolisIt tends to irritate the people you are asking for help when you simply repost the same question. Check your previous post (http://www.dbforums.com/t1202865.html) for an answer.

-PatP

Restore Database --> drop users

Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.

Why this happens??

Regards,
ManolisThat problem is due to the way that SQL Server implements the connection between logins and users. A simpler suggestion would be to use sp_change_users_login (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp) instead.

-PatP

Restore a single file from the database

Hello,
We have running sql2000 and we want to use a database where users can store
files in it. My question is: is it possible to restore a single file from
that database?
Thanx in advance
Marco Gorissen
marco.gorissen@.icts.unimaas.nlHi,
You can use text data type with file name as a different column. Using this
you can retive a single file from the SQL table.
select textcolumn from table where filename='filename.txt'
Did I answer your question correctly ?
Thanks
Hari
MCDBA
"Marco Gorissen" <marco.gorissen@.icts.unimaas.nl> wrote in message
news:e0R3b2c1DHA.3216@.TK2MSFTNGP11.phx.gbl...
> Hello,
> We have running sql2000 and we want to use a database where users can
store
> files in it. My question is: is it possible to restore a single file from
> that database?
> Thanx in advance
> Marco Gorissen
> marco.gorissen@.icts.unimaas.nl
>

restore

hello all
How do I get exclusive rights to the db. I tried to set it to offline but
the db refused to let me while users are connected.
Please advise
Regards
AndreAndre,
Check out the WITH ROLLBACK IMMEDIATE and ROLLBACK AFTER arguments of the
ALTER DATABASE statement.
HTH
Jerry
"Andre Gibson" <AndreGibson@.discussions.microsoft.com> wrote in message
news:708D1B6B-73C6-4D69-8405-5BD35DCC20F5@.microsoft.com...
> hello all
> How do I get exclusive rights to the db. I tried to set it to offline but
> the db refused to let me while users are connected.
> Please advise
> Regards
> Andre

restore

hello all
How do I get exclusive rights to the db. I tried to set it to offline but
the db refused to let me while users are connected.
Please advise
Regards
AndreAndre,
Check out the WITH ROLLBACK IMMEDIATE and ROLLBACK AFTER arguments of the
ALTER DATABASE statement.
HTH
Jerry
"Andre Gibson" <AndreGibson@.discussions.microsoft.com> wrote in message
news:708D1B6B-73C6-4D69-8405-5BD35DCC20F5@.microsoft.com...
> hello all
> How do I get exclusive rights to the db. I tried to set it to offline but
> the db refused to let me while users are connected.
> Please advise
> Regards
> Andre