Moving Databases, Logins and Roles

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tuned - 28 Dec 2007 15:16 GMT

We are currently doing a project in which one of our SQL servers databases
and logins need to be moved to a new server that will eventually be renamed.
We have a 3rd party backup application that can restore the databases to the
new server without a problem, but it doesn't do the logins. I found a script
on microsofts website that copies all the logins and works, but it doesn't
modify the roles of the users. Is there a way to do this short of
reassigning all the roles?

Both servers are running W2k3 w/ SQL 2000 w/sp3 installed.

Reply to this Message

John Bell - 28 Dec 2007 15:51 GMT

Hi

The database roles will be transfered with the database backup! If this is
database roles then it sounds like you are dropping the database users
rather than matching the orphaned users to the logins!

I assume the script you have for the logins is from
http://support.microsoft.com/kb/246133/
Also see http://support.microsoft.com/kb/240872/ and
http://support.microsoft.com/kb/274188/

If you are somehow wanting logins to be in server roles then look at the
sysxlogins and spt_values
tables in master or run sp_helpsrvrolemember (or look at the code!) to
produce a script to create the memberships.

John

> We are currently doing a project in which one of our SQL servers databases
> and logins need to be moved to a new server that will eventually be
[quoted text clipped - 8 lines]
>
> Both servers are running W2k3 w/ SQL 2000 w/sp3 installed.

Reply to this Message

tuned - 28 Dec 2007 18:23 GMT

Thanks for the reply. I did run the script that's outlined under Method A in
the first link. I ran it against the master database and it returned all the
users. When I then input that output into the Query Analyzer on the
destination server all the users were put in w/ a default database of Master,
even though that wasn't the case on the old server. I also tried running it
against a different database just to be on the safe side but it yielded the
same result.

I just checked and the users do in fact have access to the databases, at
least as per SQL. As far as the default database on the primary screen,
would those just have to be reset manually?

> Hi
>
[quoted text clipped - 26 lines]
> >
> > Both servers are running W2k3 w/ SQL 2000 w/sp3 installed.

Reply to this Message

John Bell - 28 Dec 2007 23:34 GMT

Hi

Default databases are given in syslogins so you could extend to script to
add the default database parameter of sp_addlogin or add a call to
sp_defaultdb.

John

> Thanks for the reply. I did run the script that's outlined under Method A
> in
[quoted text clipped - 47 lines]
>> >
>> > Both servers are running W2k3 w/ SQL 2000 w/sp3 installed.

Reply to this Message