Moving a SQL Server 2000 database that is in Standby Mode

SQL Users/DBAs,

I'm trying to move data files around for a database that is in standby
mode. I can detach/attach the database fine using
SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
sequence is broken and I can't restore any more logs. Does anyone
know if there is a way to move around data files and keep the database
in standby mode?

Thanks,

JB"jbizzy" <jbizzaro@.yahoo.com> wrote in message
news:609b4de0.0404201342.29ceb03e@.posting.google.c om...
> SQL Users/DBAs,
> I'm trying to move data files around for a database that is in standby
> mode. I can detach/attach the database fine using
> SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
> sequence is broken and I can't restore any more logs. Does anyone
> know if there is a way to move around data files and keep the database
> in standby mode?
>
> Thanks,
>
> JB

It would be useful to know why you need to do this - if you want to maintain
two standby copies of a database, then why not simply restore the log
backups to both databases, instead of restoring to only one and then trying
to copy it?

Simon|||Our database is 350 GB...we're upgrading some of our arrays...rather
than re-sync the database up using restore, we would just like to move a
few of the datafiles..the restore takes many hours..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"jbizzy" <jbizzaro@.yahoo.com> wrote in message
news:609b4de0.0404201342.29ceb03e@.posting.google.c om...
> SQL Users/DBAs,
> I'm trying to move data files around for a database that is in standby
> mode. I can detach/attach the database fine using
> SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
> sequence is broken and I can't restore any more logs. Does anyone
> know if there is a way to move around data files and keep the database
> in standby mode?
>
> Thanks,
>
> JB

It would be useful to know why you need to do this - if you want to maintain
two standby copies of a database, then why not simply restore the log
backups to both databases, instead of restoring to only one and then trying
to copy it?

Simon|||Our database is 350 GB...we're upgrading some of our arrays...rather
than re-sync the database up using restore, we would just like to move a
few of the datafiles..the restore takes many hours..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Moved SQL to the new box

Hi everybody,

I re-built SQl server 2000 on the new box. Attached and detached all DBs (master, msdb, users DBs) and moved them to the new paths (E:\mssql). Everything is fine.

The questions are:
1. The 'filename' in the master.sysdatabases for 'master' DB is still pointed to the old location (F:\) even physicaly files are on E:\ drive;

2. The 'phyname' in the master.sysdevices for all devices (DBs), including master, msdb, model are still shows the old location (F:\);
At list I will get a wrong reports of the files location.

it could be other places in the system tables that keep old information.
How and where I can correct the information?

3. All jobs, bakups, etc. that creates output files still pointed to the old locations (let say to the F:\sql2000\). F:\ drive is not exists anymore.
I will recreate all jobs and main plans to specify correct path.
But how can I change the DEFAULT locations for all backups, logs, etc. ?

Thank you,
LYOne way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps|||In the rebuild situation, I generally will not restore or reattach system databases, only the user databases. Because my past experience of having numerous troubles with the master after restoring to a new machine. Scheduled tasks and DTS can be saved and transfer to the new machine.

Good luck!|||Originally posted by dbabren
One way you could do it (sorry but its a bit long winded), is to create your dbs on the new server and restore the dbs you wish to move, using the "with move" clause on the resore database command.

By virtue of the fact you have created the dbs on the new server your paths should be OK, and not inherited by the dbs you sp_attach_db'ed

Hope this helps

=====

All user DB's are fine.
The only incorrect information for master.sysdatabases.filename (for master database) and master.sysdevices (for all devices).

Thank you.
LYsql

move users

hi all
how can i move all users from one sql instanz to another ?
with all corresponding privilegs?
i have to move 10 databases from one sql server to a second and then
delete the databases on the first one.
thankx for your help
mike schwarz
www.ctek.ch
These should get you going:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mike Schwarz" <ctek@.ctek.ch> wrote in message
news:%23cLwTDMrEHA.2776@.TK2MSFTNGP14.phx.gbl...
> hi all
> how can i move all users from one sql instanz to another ?
> with all corresponding privilegs?
> i have to move 10 databases from one sql server to a second and then
> delete the databases on the first one.
> thankx for your help
> mike schwarz
> www.ctek.ch
>
|||thank you for the reply... seems to be a very hard task, lots of options
and possibilities. it seems that the SID is the most common problem. i
will try this with the utility you gave me the link
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

move users

hi all
how can i move all users from one sql instanz to another ?
with all corresponding privilegs?
i have to move 10 databases from one sql server to a second and then
delete the databases on the first one.
thankx for your help
mike schwarz
www.ctek.chThese should get you going:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mike Schwarz" <ctek@.ctek.ch> wrote in message
news:%23cLwTDMrEHA.2776@.TK2MSFTNGP14.phx.gbl...
> hi all
> how can i move all users from one sql instanz to another ?
> with all corresponding privilegs?
> i have to move 10 databases from one sql server to a second and then
> delete the databases on the first one.
> thankx for your help
> mike schwarz
> www.ctek.ch
>sql

Move SQL Users id + pw to new box

I have everything transferred between our older stage server to it's
replacement. Is there a good way to transfer db users?
I have downloaded Sync SQL Logins from
http://www.dbmaint.com/SyncSqlLogins.asp
Is there an alternative someone has used that they like better?
From my first go with this tool, I think that I need to give every pw to
actually move the id?
TIA
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>I have everything transferred between our older stage server to it's
>replacement. Is there a good way to transfer db users?
> I have downloaded Sync SQL Logins from
> http://www.dbmaint.com/SyncSqlLogins.asp
> Is there an alternative someone has used that they like better?
> From my first go with this tool, I think that I need to give every pw to
> actually move the id?
After looking around more on the net I realized that I had the RedGate
bundle and it did what I was interested in achieving. I live in the data
compare almost daily, and forgot all about the SQL compare.
__Stephen
|||Stephen
You can also use DTS as this article explains.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
Regards
John
"__Stephen" wrote:

> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>
> After looking around more on the net I realized that I had the RedGate
> bundle and it did what I was interested in achieving. I live in the data
> compare almost daily, and forgot all about the SQL compare.
> __Stephen
>
>

Move SQL Users id + pw to new box

I have everything transferred between our older stage server to it's
replacement. Is there a good way to transfer db users?
I have downloaded Sync SQL Logins from
http://www.dbmaint.com/SyncSqlLogins.asp
Is there an alternative someone has used that they like better?
From my first go with this tool, I think that I need to give every pw to
actually move the id?
TIA"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>I have everything transferred between our older stage server to it's
>replacement. Is there a good way to transfer db users?
> I have downloaded Sync SQL Logins from
> http://www.dbmaint.com/SyncSqlLogins.asp
> Is there an alternative someone has used that they like better?
> From my first go with this tool, I think that I need to give every pw to
> actually move the id?
After looking around more on the net I realized that I had the RedGate
bundle and it did what I was interested in achieving. I live in the data
compare almost daily, and forgot all about the SQL compare.
__Stephen|||Stephen
You can also use DTS as this article explains.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
Regards
John
"__Stephen" wrote:

> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>
> After looking around more on the net I realized that I had the RedGate
> bundle and it did what I was interested in achieving. I live in the data
> compare almost daily, and forgot all about the SQL compare.
> __Stephen
>
>

Move SQL Users id + pw to new box

I have everything transferred between our older stage server to it's
replacement. Is there a good way to transfer db users?
I have downloaded Sync SQL Logins from
http://www.dbmaint.com/SyncSqlLogins.asp
Is there an alternative someone has used that they like better?
From my first go with this tool, I think that I need to give every pw to
actually move the id?
TIA"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>I have everything transferred between our older stage server to it's
>replacement. Is there a good way to transfer db users?
> I have downloaded Sync SQL Logins from
> http://www.dbmaint.com/SyncSqlLogins.asp
> Is there an alternative someone has used that they like better?
> From my first go with this tool, I think that I need to give every pw to
> actually move the id?
After looking around more on the net I realized that I had the RedGate
bundle and it did what I was interested in achieving. I live in the data
compare almost daily, and forgot all about the SQL compare.
__Stephen|||Stephen
You can also use DTS as this article explains.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
Regards
John
"__Stephen" wrote:
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ezBk71TEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> >I have everything transferred between our older stage server to it's
> >replacement. Is there a good way to transfer db users?
> >
> > I have downloaded Sync SQL Logins from
> > http://www.dbmaint.com/SyncSqlLogins.asp
> >
> > Is there an alternative someone has used that they like better?
> >
> > From my first go with this tool, I think that I need to give every pw to
> > actually move the id?
>
> After looking around more on the net I realized that I had the RedGate
> bundle and it did what I was interested in achieving. I live in the data
> compare almost daily, and forgot all about the SQL compare.
> __Stephen
>
>

Move SQL 7.0 to SQL 7.0

Hi,
moving all databases, users, logins DTS-jobs and so on from Server A to
(new) Server B.
What is the most efficient way of doing this? One example I have seen is to
copy all data and transaction-files for all databases.
Another way could be to perform Backup on Server A, and restore on Server B
(including msdb, master and so on) .
Any suggestions? Is there a way to make it simple ?
/BJI have not tryed it but try it yourself.
Install SQL Server on target machine in same path (and Service Packs).
Stop it.
Copy the SQL Server directory from source directory to target directory.
Copy all databases from source to destination keeping same location. If
databases need to be moved - you can detach it (from all tasks), move files
and attach them again.
Restart. Have fun
HTH
Bojidar Alexandrov|||I have also seen the "Server Upgrade Wizard".
Can this one be used for moving SQL7 to SQL7?
/BJ
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:ukHpDi1BEHA.464@.TK2MSFTNGP11.phx.gbl...
> I have not tryed it but try it yourself.
> Install SQL Server on target machine in same path (and Service Packs).
> Stop it.
> Copy the SQL Server directory from source directory to target directory.
> Copy all databases from source to destination keeping same location. If
> databases need to be moved - you can detach it (from all tasks), move
files
> and attach them again.
> Restart. Have fun
> HTH
> Bojidar Alexandrov
>|||> I have also seen the "Server Upgrade Wizard".
> Can this one be used for moving SQL7 to SQL7?
>
no|||OK,
you said that you have not tried this way, but what should your way be to
make it?
/BJ
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:OFpoAB2BEHA.2404@.TK2MSFTNGP11.phx.gbl...
> no
>|||As far as I know SQL Server this must works.
Try it on test machine and see for yourself - I personally do not have time
now|||here a article
http://www.databasejournal.com/feat...cle.php/1479841

move logins from a sql 2000 server to a sql 2005 server

Hi guys,
I have tried to use the sp_help_revlogin script to move logins from a sql 2000 server to a sql 2005 server. It works for users that have a password. But if the password is blank it will fail. Is there a work around for this since I can't change the users passwords?
I have also tried SSIS package but that too doesn't work because of the same reason.

Can anyone help...

Users MUST have a password in SQL 2005. You will need to change the logins to have a password.

Move DB users

Hi,
I have an already existing DB A, and I created new one B under the same SQL
Server registration. I want to transfer DB "Users" from a to B. How can I
do that? To make myself clear, I am not talking about "Logins"I assume that you are talking about moving logins from one server to
another. I have used this method many times and it works well.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> Hi,
> I have an already existing DB A, and I created new one B under the same
SQL
> Server registration. I want to transfer DB "Users" from a to B. How can
I
> do that? To make myself clear, I am not talking about "Logins"|||No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
can transfer logins...but how about Users
"Keith Kratochvil" wrote:
> I assume that you are talking about moving logins from one server to
> another. I have used this method many times and it works well.
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> > Hi,
> > I have an already existing DB A, and I created new one B under the same
> SQL
> > Server registration. I want to transfer DB "Users" from a to B. How can
> I
> > do that? To make myself clear, I am not talking about "Logins"
>|||This should serve as a start:
SELECT 'EXEC sp_grantdbaccess "' + name + '"'
FROM sysusers
WHERE isntname = 1
SELECT 'EXEC sp_adduser "' + name + '"'
FROM sysusers
WHERE issqluser = 1
Run above in source db and execute result in destination db. Above assumes same user name as login
name. If that isn't the case, join sysusers to master..syslogins and pick the login name and use as
second parameter for the sp_ call. Also, this was a quickie, run tests etc and make sure that is
work as expected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in message
news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
>> I assume that you are talking about moving logins from one server to
>> another. I have used this method many times and it works well.
>> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> http://www.support.microsoft.com/?id=246133
>> --
>> Keith
>>
>> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
>> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
>> > Hi,
>> > I have an already existing DB A, and I created new one B under the same
>> SQL
>> > Server registration. I want to transfer DB "Users" from a to B. How can
>> I
>> > do that? To make myself clear, I am not talking about "Logins"
>>|||You aren't moving users -- they already exist. You have to grant database
access. Tibor's script should work for you.
--
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
> > I assume that you are talking about moving logins from one server to
> > another. I have used this method many times and it works well.
> >
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://www.support.microsoft.com/?id=246133
> >
> > --
> > Keith
> >
> >
> > "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> > message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> > > Hi,
> > > I have an already existing DB A, and I created new one B under the
same
> > SQL
> > > Server registration. I want to transfer DB "Users" from a to B. How
can
> > I
> > > do that? To make myself clear, I am not talking about "Logins"
> >
> >|||Hi Keith, Tibor,
I do not think permissions will be proper after this.. we need to reapply
permissions script... pls confirm
regards,
bharath
"Keith Kratochvil" wrote:
> You aren't moving users -- they already exist. You have to grant database
> access. Tibor's script should work for you.
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> > No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
> we
> > can transfer logins...but how about Users
> >
> > "Keith Kratochvil" wrote:
> >
> > > I assume that you are talking about moving logins from one server to
> > > another. I have used this method many times and it works well.
> > >
> > > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > > http://www.support.microsoft.com/?id=246133
> > >
> > > --
> > > Keith
> > >
> > >
> > > "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> > > message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> > > > Hi,
> > > > I have an already existing DB A, and I created new one B under the
> same
> > > SQL
> > > > Server registration. I want to transfer DB "Users" from a to B. How
> can
> > > I
> > > > do that? To make myself clear, I am not talking about "Logins"
> > >
> > >
>|||Correct. You need to bring over permissions as well. Perhaps it is better to let EM generate scripts
for this and extract the sp_adduser and GRANT commands from the generated scripts...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:669D8883-F7FB-44CE-979B-9487FEEDCFCF@.microsoft.com...
> Hi Keith, Tibor,
> I do not think permissions will be proper after this.. we need to reapply
> permissions script... pls confirm
> regards,
> bharath
> "Keith Kratochvil" wrote:
>> You aren't moving users -- they already exist. You have to grant database
>> access. Tibor's script should work for you.
>> --
>> Keith
>>
>> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
>> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
>> > No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
>> we
>> > can transfer logins...but how about Users
>> >
>> > "Keith Kratochvil" wrote:
>> >
>> > > I assume that you are talking about moving logins from one server to
>> > > another. I have used this method many times and it works well.
>> > >
>> > > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > > http://www.support.microsoft.com/?id=246133
>> > >
>> > > --
>> > > Keith
>> > >
>> > >
>> > > "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
>> > > message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
>> > > > Hi,
>> > > > I have an already existing DB A, and I created new one B under the
>> same
>> > > SQL
>> > > > Server registration. I want to transfer DB "Users" from a to B. How
>> can
>> > > I
>> > > > do that? To make myself clear, I am not talking about "Logins"
>> > >
>> > >
>>

Move DB users

Hi,
I have an already existing DB A, and I created new one B under the same SQL
Server registration. I want to transfer DB "Users" from a to B. How can I
do that? To make myself clear, I am not talking about "Logins"
I assume that you are talking about moving logins from one server to
another. I have used this method many times and it works well.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> Hi,
> I have an already existing DB A, and I created new one B under the same
SQL
> Server registration. I want to transfer DB "Users" from a to B. How can
I
> do that? To make myself clear, I am not talking about "Logins"
|||No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
can transfer logins...but how about Users
"Keith Kratochvil" wrote:

> I assume that you are talking about moving logins from one server to
> another. I have used this method many times and it works well.
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> SQL
> I
>
|||This should serve as a start:
SELECT 'EXEC sp_grantdbaccess "' + name + '"'
FROM sysusers
WHERE isntname = 1
SELECT 'EXEC sp_adduser "' + name + '"'
FROM sysusers
WHERE issqluser = 1
Run above in source db and execute result in destination db. Above assumes same user name as login
name. If that isn't the case, join sysusers to master..syslogins and pick the login name and use as
second parameter for the sp_ call. Also, this was a quickie, run tests etc and make sure that is
work as expected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in message
news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...[vbcol=seagreen]
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
|||You aren't moving users -- they already exist. You have to grant database
access. Tibor's script should work for you.
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we[vbcol=seagreen]
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
same[vbcol=seagreen]
can[vbcol=seagreen]
|||Hi Keith, Tibor,
I do not think permissions will be proper after this.. we need to reapply
permissions script... pls confirm
regards,
bharath
"Keith Kratochvil" wrote:

> You aren't moving users -- they already exist. You have to grant database
> access. Tibor's script should work for you.
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> we
> same
> can
>
|||Correct. You need to bring over permissions as well. Perhaps it is better to let EM generate scripts
for this and extract the sp_adduser and GRANT commands from the generated scripts...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:669D8883-F7FB-44CE-979B-9487FEEDCFCF@.microsoft.com...[vbcol=seagreen]
> Hi Keith, Tibor,
> I do not think permissions will be proper after this.. we need to reapply
> permissions script... pls confirm
> regards,
> bharath
> "Keith Kratochvil" wrote:

Move DB users

Hi,
I have an already existing DB A, and I created new one B under the same SQL
Server registration. I want to transfer DB "Users" from a to B. How can I
do that? To make myself clear, I am not talking about "Logins"I assume that you are talking about moving logins from one server to
another. I have used this method many times and it works well.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> Hi,
> I have an already existing DB A, and I created new one B under the same
SQL
> Server registration. I want to transfer DB "Users" from a to B. How can
I
> do that? To make myself clear, I am not talking about "Logins"|||No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that we
can transfer logins...but how about Users
"Keith Kratochvil" wrote:

> I assume that you are talking about moving logins from one server to
> another. I have used this method many times and it works well.
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:A4F45400-3FF6-4717-A9DE-D9E0266E20B2@.microsoft.com...
> SQL
> I
>|||This should serve as a start:
SELECT 'EXEC sp_grantdbaccess "' + name + '"'
FROM sysusers
WHERE isntname = 1
SELECT 'EXEC sp_adduser "' + name + '"'
FROM sysusers
WHERE issqluser = 1
Run above in source db and execute result in destination db. Above assumes s
ame user name as login
name. If that isn't the case, join sysusers to master..syslogins and pick th
e login name and use as
second parameter for the sp_ call. Also, this was a quickie, run tests etc a
nd make sure that is
work as expected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in messa
ge
news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...[vbcol=seagreen]
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
>|||You aren't moving users -- they already exist. You have to grant database
access. Tibor's script should work for you.
Keith
"SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> No, Keith...I am trying to move Users from dB 'A' to dB 'B'. I know that
we[vbcol=seagreen]
> can transfer logins...but how about Users
> "Keith Kratochvil" wrote:
>
same[vbcol=seagreen]
can[vbcol=seagreen]|||Hi Keith, Tibor,
I do not think permissions will be proper after this.. we need to reapply
permissions script... pls confirm
regards,
bharath
"Keith Kratochvil" wrote:

> You aren't moving users -- they already exist. You have to grant database
> access. Tibor's script should work for you.
> --
> Keith
>
> "SQLServerNewbee" <SQLServerNewbee@.discussions.microsoft.com> wrote in
> message news:51D4315F-93CE-429C-A13B-5B450E33BEA6@.microsoft.com...
> we
> same
> can
>|||Correct. You need to bring over permissions as well. Perhaps it is better to
let EM generate scripts
for this and extract the sp_adduser and GRANT commands from the generated sc
ripts...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bharath" <Bharath@.discussions.microsoft.com> wrote in message
news:669D8883-F7FB-44CE-979B-9487FEEDCFCF@.microsoft.com...[vbcol=seagreen]
> Hi Keith, Tibor,
> I do not think permissions will be proper after this.. we need to reapply
> permissions script... pls confirm
> regards,
> bharath
> "Keith Kratochvil" wrote:
>