Database restore

[jsfromynr]

Hi All,

I am trying to restore a database from backup on another system [my
client's] but I had to follow some weired steps.

Step 1. Restore the database from EM 's GUI.
[Users as on My machine get restored but their logins are not
activated. So I have to follow the next steps]

sp_helpuser shows me the userName but null LoginName and
DefaultDatabase

Step 2. Create a new login , grant access to the DB

Step 3. Then run the script to change the ownership from earlier DB
user to new user.

Step 4. In EM make new user as dbo.

Really weired steps.

There should be easier way to restore the database in simple and
effective manner

With warm regards
Jatinder

[Erland Sommarskog]

jsfromynr (ja************@clovertechnologies.com) writes:

I am trying to restore a database from backup on another system [my
client's] but I had to follow some weired steps.

Step 1. Restore the database from EM 's GUI.
[Users as on My machine get restored but their logins are not
activated. So I have to follow the next steps]

sp_helpuser shows me the userName but null LoginName and
DefaultDatabase

Step 2. Create a new login , grant access to the DB

Step 3. Then run the script to change the ownership from earlier DB
user to new user.

Step 4. In EM make new user as dbo.

Look at sp_change_users_login in Books Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[jsfromynr]

Thanks Erland,
It worked, I have another problem
by mistake I created all of my table in a user not as SA.so I tried
this to change the ownership

select 'EXEC sp_changeobjectowner oldusername'+
char(39)+name+char(39)+','+ char(39) +'dbo'+char(39) from sysobjects
where uid=6 {old user's ID}
It works but I am looking for some easy methods

With warm regards
Jatinder

[Erland Sommarskog]

jsfromynr (ja************@clovertechnologies.com) writes:

It worked, I have another problem
by mistake I created all of my table in a user not as SA.so I tried
this to change the ownership

select 'EXEC sp_changeobjectowner oldusername'+
char(39)+name+char(39)+','+ char(39) +'dbo'+char(39) from sysobjects
where uid=6 {old user's ID}
It works but I am looking for some easy methods

It can't get any easier than that!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp