Correct Way to Reboot a SQL Server

When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!We have always just used the system shut down from the
start or ctl alt del option... and no problems..
>--Original Message--
>When you guys perform a regular reboot of your production
SQL Servers,
>do you just use the Start button and Shutdown/Restart?
Or do you stop
>SQL Server first, then reboot the server?
>Stopping SQL Server first, then rebooting the server, has
always
>seemed obvious to me. My boss wants to just use the
Start button and
>reboot the boxes, because this has never caused us any
problems, and
>will save the time of taking the extra steps to stop SQL
Server first.
>Now that I'm looking for it, I can't find one clear bit
of Best
>Practice documentation on this!
>Thanks much!
>.
>|||I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> > When you guys perform a regular reboot of your production SQL Servers,
> > do you just use the Start button and Shutdown/Restart? Or do you stop
> > SQL Server first, then reboot the server?
> >
> > Stopping SQL Server first, then rebooting the server, has always
> > seemed obvious to me. My boss wants to just use the Start button and
> > reboot the boxes, because this has never caused us any problems, and
> > will save the time of taking the extra steps to stop SQL Server first.
> >
> > Now that I'm looking for it, I can't find one clear bit of Best
> > Practice documentation on this!
> >
> > Thanks much!|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.com...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...
> > You can just reboot the server. When Windows does a normal shutdown, it
> > first stops all running services, including SQL server.
> >
> > "James" <throwawaythrowaway@.hotmail.com> wrote in message
> > news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> > > When you guys perform a regular reboot of your production SQL Servers,
> > > do you just use the Start button and Shutdown/Restart? Or do you stop
> > > SQL Server first, then reboot the server?
> > >
> > > Stopping SQL Server first, then rebooting the server, has always
> > > seemed obvious to me. My boss wants to just use the Start button and
> > > reboot the boxes, because this has never caused us any problems, and
> > > will save the time of taking the extra steps to stop SQL Server first.
> > >
> > > Now that I'm looking for it, I can't find one clear bit of Best
> > > Practice documentation on this!
> > >
> > > Thanks much!sql

Correct Way to Reboot a SQL Server

When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.com...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2
976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.com...|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.com...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]

Correct Way to Reboot a SQL Server

When you guys perform a regular reboot of your production SQL Servers,
do you just use the Start button and Shutdown/Restart? Or do you stop
SQL Server first, then reboot the server?
Stopping SQL Server first, then rebooting the server, has always
seemed obvious to me. My boss wants to just use the Start button and
reboot the boxes, because this has never caused us any problems, and
will save the time of taking the extra steps to stop SQL Server first.
Now that I'm looking for it, I can't find one clear bit of Best
Practice documentation on this!
Thanks much!
I tend to just reboot the servers , this has always worked fine for me on
hundreds of servers big and small.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||James,
I have never needed to do a regular reboot of a SQL Server since Windows
2000, unless I have changed some hardware or installed software that
required a reboot. NT4 was a little different...
If the server is a dedicated server and there are no other applications on
there, then Shutdown should work fine without having to stop the service.
Remember to stop application services on your application servers before
rebooting SQL Server, it will be cleaner.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||You can just reboot the server. When Windows does a normal shutdown, it
first stops all running services, including SQL server.
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
> When you guys perform a regular reboot of your production SQL Servers,
> do you just use the Start button and Shutdown/Restart? Or do you stop
> SQL Server first, then reboot the server?
> Stopping SQL Server first, then rebooting the server, has always
> seemed obvious to me. My boss wants to just use the Start button and
> reboot the boxes, because this has never caused us any problems, and
> will save the time of taking the extra steps to stop SQL Server first.
> Now that I'm looking for it, I can't find one clear bit of Best
> Practice documentation on this!
> Thanks much!
|||I know everyone does it this way (just reboots), and in hundreds of
reboots, I've never had anything happen, as far as I know. My thought
is, you can just shutdown the O/S, and let the Service Control Manager
send a shutdown request to the SQL server service (as well as all the
other services), which attempts an orderly shutdown (logins disabled,
sql statements allowed to finish, and database checkpoints). But as I
understand, if this doesn't happen in 20 seconds (with SQL Server
competing with all other services shutting down during this time), the
SCM kills the service, so on restart SQL Server not only has to
rollback uncompleted transactions, but may have torn pages.
If anybody can shoot this concern out of the water, I'll be glad to
hear it, as just rebooting the server without doing anything else is a
bit easier!
Thanks
"Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> You can just reboot the server. When Windows does a normal shutdown, it
> first stops all running services, including SQL server.
> "James" <throwawaythrowaway@.hotmail.com> wrote in message
> news:27d8ba57.0405171047.4d8b8067@.posting.google.c om...
|||You are mostly correct, but your concern about torn pages is largely
unfounded. Torn pages occur when Windows "thinks" data has been written to
disk, but in fact it has only been written to a disk cache when the power
fails. If your disk cache doesn't have a battery backup there would likely
be some pages that are partially written to disk and thus inconsistent.
I normally shut down SQL Server by itself to avoid unnecessarily long
recovery times and forcing a rollback on transactions that should be
committed etc...
"James" <throwawaythrowaway@.hotmail.com> wrote in message
news:27d8ba57.0405180616.2eb650db@.posting.google.c om...
> I know everyone does it this way (just reboots), and in hundreds of
> reboots, I've never had anything happen, as far as I know. My thought
> is, you can just shutdown the O/S, and let the Service Control Manager
> send a shutdown request to the SQL server service (as well as all the
> other services), which attempts an orderly shutdown (logins disabled,
> sql statements allowed to finish, and database checkpoints). But as I
> understand, if this doesn't happen in 20 seconds (with SQL Server
> competing with all other services shutting down during this time), the
> SCM kills the service, so on restart SQL Server not only has to
> rollback uncompleted transactions, but may have torn pages.
> If anybody can shoot this concern out of the water, I'll be glad to
> hear it, as just rebooting the server without doing anything else is a
> bit easier!
> Thanks
> "Rick White" <name=rick@.domain=whiteconsulting.TLD=biz> wrote in message
news:<u#YPFoJPEHA.2976@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]

Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure an
d
the contents. Is this possible and how can I go about it.
--
Beaversuse import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure
and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:
>

Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure and
the contents. Is this possible and how can I go about it.
Beavers
use import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>
|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:

copying table from one database to another

I would like to copy a table from one database to another. These two
databases reside in two different servers/instances? Is this possible? If so,
how can I code it in m C++ application? Thanks!
You have 2 choices, Database solution or client based solution.
Database: Have your DBA create a linked server connection to server2 on
server1. Now you can execute this statement from server1
INSERT MyDest (column_list) SELECT column_list from
server2.mydb.dbo.MySource where ...
Client based solution:
Setup ADO connection to both servers. Populate a recordset from the source db
Iterate through this recordset and insert records in destination using a
procedure that has your insert logic.
HTH
Arun
"luv2travel" wrote:

> I would like to copy a table from one database to another. These two
> databases reside in two different servers/instances? Is this possible? If so,
> how can I code it in m C++ application? Thanks!

copying table from one database to another

I would like to copy a table from one database to another. These two
databases reside in two different servers/instances? Is this possible? If so,
how can I code it in m C++ application? Thanks!You have 2 choices, Database solution or client based solution.
Database: Have your DBA create a linked server connection to server2 on
server1. Now you can execute this statement from server1
INSERT MyDest (column_list) SELECT column_list from
server2.mydb.dbo.MySource where ...
Client based solution:
Setup ADO connection to both servers. Populate a recordset from the source db
Iterate through this recordset and insert records in destination using a
procedure that has your insert logic.
HTH
Arun
"luv2travel" wrote:
> I would like to copy a table from one database to another. These two
> databases reside in two different servers/instances? Is this possible? If so,
> how can I code it in m C++ application? Thanks!

copying table from one database to another

I would like to copy a table from one database to another. These two
databases reside in two different servers/instances? Is this possible? If so
,
how can I code it in m C++ application? Thanks!You have 2 choices, Database solution or client based solution.
Database: Have your DBA create a linked server connection to server2 on
server1. Now you can execute this statement from server1
INSERT MyDest (column_list) SELECT column_list from
server2.mydb.dbo.MySource where ...
Client based solution:
Setup ADO connection to both servers. Populate a recordset from the source d
b
Iterate through this recordset and insert records in destination using a
procedure that has your insert logic.
HTH
Arun
"luv2travel" wrote:

> I would like to copy a table from one database to another. These two
> databases reside in two different servers/instances? Is this possible? If
so,
> how can I code it in m C++ application? Thanks!

Copying SQL logins to new Domain

Good day!
I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
for the time being. But I am starting to move users from that domain
to an AD domain. We are doing them bit by bit. I want the Windows
Authentication-based SQL Logins to move over so that I don't have to
re-create the logins.
What is the best way to do this, assuming I want to do them slowly?
Thanks.Have you considered creating Groups on the new domain, give the groups the
relevant SQL permissions and when you're ready to migrate a user simply add
him to the new group.
HTH
Ryan Waight, MCDBA, MCSE
"Bob" <Bob@.nospam.com> wrote in message
news:nkkfqvg1fvjnc0j8lu9l8c2i2trtvvf8jq@.4ax.com...
> Good day!
> I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
> for the time being. But I am starting to move users from that domain
> to an AD domain. We are doing them bit by bit. I want the Windows
> Authentication-based SQL Logins to move over so that I don't have to
> re-create the logins.
> What is the best way to do this, assuming I want to do them slowly?
> Thanks.
>|||I suppose I could do that. But is there a way to do what I am looking
to do?
On Tue, 4 Nov 2003 16:52:42 -0000, "Ryan Waight"
<Ryan_Waight@.nospam.hotmail.com> wrote:
>Have you considered creating Groups on the new domain, give the groups the
>relevant SQL permissions and when you're ready to migrate a user simply add
>him to the new group.|||Bob wrote:
> Good day!
> I have 2 SQL 2000 Servers, in a NT4 domain, that will be staying tehre
> for the time being. But I am starting to move users from that domain
> to an AD domain. We are doing them bit by bit. I want the Windows
> Authentication-based SQL Logins to move over so that I don't have to
> re-create the logins.
> What is the best way to do this, assuming I want to do them slowly?
you might try running sp_help_revlogin and then editing the script to your
liking.|||Hi Bob,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that you would like to transfer the Windows Authentication
Logins and passwords to a new AD domain between the instances of SQL Server. Have I fully
understood you? If there is anything I misunderstood, please feel free to let me know.
I agree with Chxxx that you can create sp_help_revlogin stored procedure on the source
server, which this procedure generates a script that you can run on the destination server to re-
create logins with the original security identification number (SID) and retain the current
passwords.
For how to transfer Logins and Passwords between SQL Server 2000, please refer to the
following step by step articles:
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/?id=274188
Bob, does this answer your question? Please apply the suggestion above and let us know if it
helps you resolve your problem. If there is anything more we can assist you with, please feel
free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||> From your description, I understand that you would like to transfer the
Windows Authentication
> Logins and passwords to a new AD domain between the instances of SQL
Server. Have I fully
> understood you? If there is anything I misunderstood, please feel free to
let me know.
If Bob is interested in the Windows authentication logins, why would he like
to transfer the passwords?
One of the problems with migrating the NT user accounts to AD how to deal
with the following scenario:
1. NT user NTDomain\myUser is migrated to ADDomain\myUser (likely with
SIDHistory)
2. The SID of NTDomain\myUser is different from ADDomain\myUser
3. The system tables sysxlogins and sysusers still store the SID for
NTDomain\myUser
4. If the NTDomain domain is completely disabled, SIDHistory will not be
useful any more
(Note, not being an AD migration expert, I don't know whether you can
migrate NT user accounts to AD without assigning them new SIDs. It's not how
we are migrating user account anyway.)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:7GUTrC0oDHA.2148@.cpmsftngxa06.phx.gbl...
> Hi Bob,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your description, I understand that you would like to transfer the
Windows Authentication
> Logins and passwords to a new AD domain between the instances of SQL
Server. Have I fully
> understood you? If there is anything I misunderstood, please feel free to
let me know.
> I agree with Chxxx that you can create sp_help_revlogin stored procedure
on the source
> server, which this procedure generates a script that you can run on the
destination server to re-
> create logins with the original security identification number (SID) and
retain the current
> passwords.
> For how to transfer Logins and Passwords between SQL Server 2000, please
refer to the
> following step by step articles:
> 246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL
Server
> http://support.microsoft.com/?id=246133
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> 274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
Incomplete
> http://support.microsoft.com/?id=274188
>
> Bob, does this answer your question? Please apply the suggestion above and
let us know if it
> helps you resolve your problem. If there is anything more we can assist
you with, please feel
> free to post it in the group.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Hi Bob,
I agree with Linchi's comments and thanks for his reminder.
If you transfer logins to an instance of SQL Server in a different domain than the source
instance of SQL Server, you should edit the script generated by the sp_help_revlogin
procedure, and REPLACE the domain name with the new domain in the sp_grantlogin
statements. Because the integrated logins granted access in the new domain will not have the
same SID as the logins in the original domain, the database users will be orphaned from
these logins.....
This is announced in the "Remarks" of the KB 246133. I recommend you review the remarks
carefully before you apply this logins transference, so that you will not miss some important
points you should pay attention to.
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/?id=246133
If there is anyting unclear or anything more I can assist you with, please feel free to post it in the
group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Copying records from 7.0 2000 with problems

Hi.

I need to copy data from one database to another, on separate servers. I've got an old production database on "server1" and its SQL Server 7.0 on NT4. This data needs to move from there over to a database on "server2" which is SQL2000/Windows2000server.

Im trying to just run test queries from "server2" to read tables on "server1" and all I can get is 'invalid object name.'

Server1 is a linked server under Server2 and is being logged into as sa.

The computername for server1 happens to be "DATABASE"
Is this a problem? From what I understand I should be able to select from <server name>.<dbname>.dbo.<tablename>

I have tried typing <server name> as [DATABASE] and "DATABASE" and any other delimiting under the sun but all I get is 'invalid object name'

I have also tried just trying to select max(helptext) from "DATABASE".master.dbo.helpsql and I still get 'invalid object'

All of my queries will work if run from query analyzer on "server1" but not from query analyzer on "server2"

thanks in advance,

-jakedo you intend to move all the data in the database??
if you are maybe you can use the dts tools or if possible restore the full backup to the server2.
I can't really remember if sql 7's backup file can be restore to sql 2k version as haven't been using for quite a long time but you can try.|||I am only moving the data from select tables. The database names are different too.

I've pretty much ruled out the possibility of a backup/restore senario.|||have u tried import/export ??|||It was a communication issue.

I Turned Mixed Protocol OFF on the old 7.0 box and turned it ON the 2K box. Go Figure. The most confusing part of this ordeal was that the login for linked server was working from both servers and you could see the master tables/views under linked server.|||sorry in that case can't help you much
hopefully somebody else can help you on this|||Wow. That was like phone tag in a message board. Don't think I've ever experienced that.

Copying files between servers

I'm trying to copy files between 2 servers on a local network from within a
SQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an access
denied message returned.

I'm able to successfully do the copy from within a command window so think
the problem has something to do with using the default SQL Server account
but as yet I don't know how to resolve.

Any help/suggestions would be much appreciated.Am guessng that you are running MS-SQL using the local SYSTEM account.
System does not have access to network devices.

Your two options are to create another account and configure MS-SQL and
agent to use that account. You may beable to get away with just
configuring agent for that but depends on how you are doing the
command.

Or to go into policy editor and allowing the system account to have
netowrk priviledges. This is a major security hole and should not be
done.

Copying DTS Packages across Servers

HI All,

Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.

I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.

moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.

Any assistance here is greatly appreciated.

Cheers
TroyHi Troy,

You can save the packages as a structure file and save them to disk and then transfer them to your new server...

Or,.. you could get cunning and write a quick app to do it. I have some asp pages that I have written that will do this and I can flick them your way if you want... they will probably need tweaking to suit your needs...

What's the weather like in Auckland today??|||Hey thanks for that.
I will go and have a play and see what I can do - I assume it is a pretty straight forward process dumping them to file? And then reimporting them?

As for the ASP page - ummm errr, would love to take you up on that offer, but sadly I am about as literate with that sort of stuff as Cinderalla with a grenade laucher. (unless it is blatantly obvious). What are your thoughts on this?

Hmmm as for the weather - well all in all okay, and it is meant to be good through the weekend. Which will make a nice change because our new pool should have a chance to dry up and return to what was our lawn :(
Hopefully better in Aust.|||It's a relatively simple asp page,... the key thing is the SQL that we use. You could probably write it up as another stored proc or something to carry out the same function... ASP is a piece of cake to understand assuming some general knowledge of scripting languages...

Have a think about it and let me know.

Weather in Sydney today is overcast and cool,... not raining yet though... I think my cousin who is visiting from Chch brought this cloud with him...

Copying databases to other servers (Backup and Restore - Detach/Attach)

I want to copy a database from one server to another so that they are
identical and I have a few questions. Also I am running the 'Simple
Recovery Model' and therefore my transaction log is minimal.
BACKUP AND RESTORE: If I perform a backup and restore will user access
rights be backed up? What if the user doesn't exist on the new server?
Should I create it manually? Lastly, in the simple recovery model do
I need to backup the transaction log?
MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
implications of stopping SQL server and manually copying the data and
log file? I could then attach it to the new server? Would this work?
DATACHING AND REATTACHING: I could detach the database, copy it to the
new server and then attach it to the original and new servers? Would
this work?
EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
tables, but this procedure would take the longest.
All your questions are answered in the following Knowledge Base article:
314546 - HOW TO Move Databases Between Computers That Are Running SQL
Server:
http://support.microsoft.com/default...;en-us;Q314546
Jacco Schalkwijk
SQL Server MVP
<war_wheelan@.yahoo.com> wrote in message
news:1114097392.448483.201600@.f14g2000cwb.googlegr oups.com...
>I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server? Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>

Copying databases to other servers (Backup and Restore - Detach/Attach)

I want to copy a database from one server to another so that they are
identical and I have a few questions. Also I am running the 'Simple
Recovery Model' and therefore my transaction log is minimal.
BACKUP AND RESTORE: If I perform a backup and restore will user access
rights be backed up? What if the user doesn't exist on the new server?
Should I create it manually? Lastly, in the simple recovery model do
I need to backup the transaction log?
MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
implications of stopping SQL server and manually copying the data and
log file? I could then attach it to the new server' Would this work?
DATACHING AND REATTACHING: I could detach the database, copy it to the
new server and then attach it to the original and new servers? Would
this work?
EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
tables, but this procedure would take the longest.All your questions are answered in the following Knowledge Base article:
314546 - HOW TO Move Databases Between Computers That Are Running SQL
Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
--
Jacco Schalkwijk
SQL Server MVP
<war_wheelan@.yahoo.com> wrote in message
news:1114097392.448483.201600@.f14g2000cwb.googlegroups.com...
>I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>|||Hi,
Using all the below approches you can copy the database to second server.
But the 3rd approch may fail ( MANUALLY COPY THE *.MDF AND *.LDF FILES) if
you have not detached the files. So it is always recomended to detach the
file and copy to destination.
If the first server is production you could use BACK DATABASE, Copy the
Backup file to second server and Restore it (RESTORE DATABASE). All the Login
user chains can be created/established using the the system stored proc
sp_change_users_login
(See books online for usage and various parameters).
If the system is not production then you can detach the database , copy the
MDF and LDF to second server , Attach the database and use system stored proc
sp_change_users_login to syncronize the logins and users.
Third approach (Export and import) may not be a solution if you have miore
tables and data. This is really time consuming.
Thanks
Hari
SQL Server MVP
"war_wheelan@.yahoo.com" wrote:
> I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"war_wheelan@.yahoo.com" wrote:
> I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>

Copying databases to other servers (Backup and Restore - Detach/Attach)

I want to copy a database from one server to another so that they are
identical and I have a few questions. Also I am running the 'Simple
Recovery Model' and therefore my transaction log is minimal.
BACKUP AND RESTORE: If I perform a backup and restore will user access
rights be backed up? What if the user doesn't exist on the new server?
Should I create it manually? Lastly, in the simple recovery model do
I need to backup the transaction log?
MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
implications of stopping SQL server and manually copying the data and
log file? I could then attach it to the new server' Would this work?
DATACHING AND REATTACHING: I could detach the database, copy it to the
new server and then attach it to the original and new servers? Would
this work?
EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
tables, but this procedure would take the longest.All your questions are answered in the following Knowledge Base article:
314546 - HOW TO Move Databases Between Computers That Are Running SQL
Server:
http://support.microsoft.com/defaul...b;en-us;Q314546
Jacco Schalkwijk
SQL Server MVP
<war_wheelan@.yahoo.com> wrote in message
news:1114097392.448483.201600@.f14g2000cwb.googlegroups.com...
>I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>

Copying databases between two SQL Servers

Hi all,
This is the situation. We have some new SQL Servers being installed. The
existing SQL servers will be retired. The existing servers run Windows2000
Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new servers
are running Windows2003 (SP1) Server (Standard Edition) with SQLServer2000
(SP3). All of the SQLServers are part of our AD domain. The SQLSERVER
service and the SQLAGENT servers run as domain accounts. These domain
accounts are members of the local administrator group on each of the SQL
servers.
The problem we are having is that when the DBA attempts to copy the
databases from the Win2000/SQLServer to one of the Win2003 servers the dts
package fails with the following
Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
Step Error Description: Unspecified Error
Step Error Code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Under more info it says
Failed to create the share OMWWIZD
We have gone through kb article 274663 which convers this but as far as we
can see we meet all the requirements for this to work, so we are a little
stumped at this point. We thought it may be something to do with Win2003
SP1, so we built a Win2003 server with no service packs and tried to copy
some databases and we received the exact same error.
I can manually create a share between the Win2000 and Win2003 servers using
the same domain account the SQLServer process use. Once manually created I
can create and delete files, so at a filesystem level the interopability
between the servers appears fine. Running out of ideas at this point.
If anyone has any suggestions they would be greatly welcomed.
regards
CraigHi,
Easy approches to copy the databases are:-
1. Backup the production database using (BACKUP DATABASE command)
2. COpy the backup file to test server
3. Restore the database in test server (RESTORE DATABASE)
See Backup and Restore commad in books online.
Note:
you can also detach and attach databases. See SP_DETACH_DB and SP_ATTACH_DB
Thanks
Hari
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> This is the situation. We have some new SQL Servers being installed. The
> existing SQL servers will be retired. The existing servers run Windows2000
> Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> servers are running Windows2003 (SP1) Server (Standard Edition) with
> SQLServer2000 (SP3). All of the SQLServers are part of our AD domain. The
> SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> domain accounts are members of the local administrator group on each of
> the SQL servers.
> The problem we are having is that when the DBA attempts to copy the
> databases from the Win2000/SQLServer to one of the Win2003 servers the dts
> package fails with the following
> Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> Step Error Description: Unspecified Error
> Step Error Code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
> Under more info it says
> Failed to create the share OMWWIZD
> We have gone through kb article 274663 which convers this but as far as we
> can see we meet all the requirements for this to work, so we are a little
> stumped at this point. We thought it may be something to do with Win2003
> SP1, so we built a Win2003 server with no service packs and tried to copy
> some databases and we received the exact same error.
> I can manually create a share between the Win2000 and Win2003 servers
> using the same domain account the SQLServer process use. Once manually
> created I can create and delete files, so at a filesystem level the
> interopability between the servers appears fine. Running out of ideas at
> this point.
> If anyone has any suggestions they would be greatly welcomed.
> regards
> Craig
>|||In additon to Hari's resonse you can also take a look at
sp_detach_db,sp_attach_db,sp_attach_sing
le_file_db
system stored procedures
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Easy approches to copy the databases are:-
> 1. Backup the production database using (BACKUP DATABASE command)
> 2. COpy the backup file to test server
> 3. Restore the database in test server (RESTORE DATABASE)
>
> See Backup and Restore commad in books online.
>
> Note:
>
> you can also detach and attach databases. See SP_DETACH_DB and
SP_ATTACH_DB
> --
> Thanks
> Hari
>
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
Windows2000[vbcol=seagreen]
The[vbcol=seagreen]
dts[vbcol=seagreen]
we[vbcol=seagreen]
little[vbcol=seagreen]
copy[vbcol=seagreen]
>|||Hi Uri and Hari,
thanks for your replies.
Yes, doing a backup/restore is one of the options I suggested to our DBA but
he is reluctant to do that because he says (and I'm no DBA) he loses all the
ownership rigts and has to recreate SQLServer accounts and so on. To me that
doesn't sound to much effort, we are not a big company so there's probably
only a hanfull of accounts, however he is the DBA so I have to go with his
recomedations pertaining to the databases.
One thing I have just found out is that when this server was originally
built it was named server-a. Later on and after Sqlserver was installed they
renamed the server to server-b. Server-a and server-b. Just wondering if
this is part of the problem?
Anyway we will explore a few other avenues and if all else fails we will
have to revert to the ol backup/restore option. I will direct our DBA to
those stored procedures you two have mentioned.
thanks for the assistance
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> In additon to Hari's resonse you can also take a look at
> sp_detach_db,sp_attach_db,sp_attach_sing
le_file_db
> system stored procedures
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> SP_ATTACH_DB
> Windows2000
> The
> dts
> we
> little
> copy
>|||Craig
Your DBA is not quite correct.
There are two stored procedures that provided by Microsoft to migrate
users/logins with their original SID
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi Uri and Hari,
> thanks for your replies.
> Yes, doing a backup/restore is one of the options I suggested to our DBA
but
> he is reluctant to do that because he says (and I'm no DBA) he loses all
the
> ownership rigts and has to recreate SQLServer accounts and so on. To me
that
> doesn't sound to much effort, we are not a big company so there's probably
> only a hanfull of accounts, however he is the DBA so I have to go with his
> recomedations pertaining to the databases.
> One thing I have just found out is that when this server was originally
> built it was named server-a. Later on and after Sqlserver was installed
they
> renamed the server to server-b. Server-a and server-b. Just wondering if
> this is part of the problem?
> Anyway we will explore a few other avenues and if all else fails we will
> have to revert to the ol backup/restore option. I will direct our DBA to
> those stored procedures you two have mentioned.
> thanks for the assistance
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
of[vbcol=seagreen]
the[vbcol=seagreen]
Package[vbcol=seagreen]
as[vbcol=seagreen]
manually[vbcol=seagreen]
>|||Hi Uri,
ok, thanks for the information. I'll pass it on to him. To be fair to him
he's really an Oracle DBA who has had SQLServer dumped on him and he hasn't
had a great deal of training on it yet.
One other thing has come to light and as of yet I am not to sure if it is
the cause or not, we still need to do some more testing. To cut a long and
boring story short we have one AD domain, let's call it oz.com.au. AD is DNS
intergrated. At some point we created some sub domains within abc.com,
namely dev.abc.com and test.abc.com. These new subdomains also have their
own unique ip address range
abc.com 192.168.1.x
dev.abc.com 192.168.2.x
test.abc.com 192.168.3.x
So the fully qualified domain names for our servers would look like this
server1.abc.com
server2.abc.com
server3.dev.abc.com
server4.dev.abc.com
server5.test.abc.com
server6.test.abc.com
however we only have one single AD domain, in this case abc.com.
What we have now found is that we can copy databases between two servers who
share the same FQDN domain part. ie. server1.abc.com to server2.abc.com
works, however copying between two servers who do not share the same domain
part does not and generates the error mentioned in the original post. ie.
server1.abc.com to server3.dev.abc.com
The odd thing is that this setup used to work under Win2000, but obviously
something in Win2003 is different. Might have to bounce this off some people
in the windows groups.
If anyone has any suggestions I'm all ears.
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Craig
> Your DBA is not quite correct.
> There are two stored procedures that provided by Microsoft to migrate
> users/logins with their original SID
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> but
> the
> that
> they
> of
> the
> Package
> as
> manually
>|||Hi all,
ok, it turned out that the fully qualified hostnames were in fact causing
the problem. As soon as we put the machines in the same internet domain name
space the DTS copies worked. I assume that something within SQLServer/DTS
assumes that if the FQDN contain different domain parts then therefore they
are in different AD domains. The fix was to simply change the
- FQDN to the same domain
- Copy the databases
- Revert the servers's FQDN back.
Thanks to all who offered various solutions. Now off to the MS support site
to see if there is something I should be configuring in AD when I do this. I
wouldn't have thought so but it looks like there is.
Regards
Craig
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%232V5e$3hFHA.3912@.tk2msftngp13.phx.gbl...
> Hi Uri,
> ok, thanks for the information. I'll pass it on to him. To be fair to him
> he's really an Oracle DBA who has had SQLServer dumped on him and he
> hasn't had a great deal of training on it yet.
> One other thing has come to light and as of yet I am not to sure if it is
> the cause or not, we still need to do some more testing. To cut a long and
> boring story short we have one AD domain, let's call it oz.com.au. AD is
> DNS intergrated. At some point we created some sub domains within abc.com,
> namely dev.abc.com and test.abc.com. These new subdomains also have their
> own unique ip address range
> abc.com 192.168.1.x
> dev.abc.com 192.168.2.x
> test.abc.com 192.168.3.x
> So the fully qualified domain names for our servers would look like this
> server1.abc.com
> server2.abc.com
> server3.dev.abc.com
> server4.dev.abc.com
> server5.test.abc.com
> server6.test.abc.com
> however we only have one single AD domain, in this case abc.com.
> What we have now found is that we can copy databases between two servers
> who share the same FQDN domain part. ie. server1.abc.com to
> server2.abc.com works, however copying between two servers who do not
> share the same domain part does not and generates the error mentioned in
> the original post. ie. server1.abc.com to server3.dev.abc.com
> The odd thing is that this setup used to work under Win2000, but obviously
> something in Win2003 is different. Might have to bounce this off some
> people in the windows groups.
> If anyone has any suggestions I'm all ears.
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>

Copying databases between two SQL Servers

Hi all,
This is the situation. We have some new SQL Servers being installed. The
existing SQL servers will be retired. The existing servers run Windows2000
Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new servers
are running Windows2003 (SP1) Server (Standard Edition) with SQLServer2000
(SP3). All of the SQLServers are part of our AD domain. The SQLSERVER
service and the SQLAGENT servers run as domain accounts. These domain
accounts are members of the local administrator group on each of the SQL
servers.
The problem we are having is that when the DBA attempts to copy the
databases from the Win2000/SQLServer to one of the Win2003 servers the dts
package fails with the following
Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
Step Error Description: Unspecified Error
Step Error Code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Under more info it says
Failed to create the share OMWWIZD
We have gone through kb article 274663 which convers this but as far as we
can see we meet all the requirements for this to work, so we are a little
stumped at this point. We thought it may be something to do with Win2003
SP1, so we built a Win2003 server with no service packs and tried to copy
some databases and we received the exact same error.
I can manually create a share between the Win2000 and Win2003 servers using
the same domain account the SQLServer process use. Once manually created I
can create and delete files, so at a filesystem level the interopability
between the servers appears fine. Running out of ideas at this point.
If anyone has any suggestions they would be greatly welcomed.
regards
CraigHi,
Easy approches to copy the databases are:-
1. Backup the production database using (BACKUP DATABASE command)
2. COpy the backup file to test server
3. Restore the database in test server (RESTORE DATABASE)
See Backup and Restore commad in books online.
Note:
you can also detach and attach databases. See SP_DETACH_DB and SP_ATTACH_DB
--
Thanks
Hari
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> This is the situation. We have some new SQL Servers being installed. The
> existing SQL servers will be retired. The existing servers run Windows2000
> Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> servers are running Windows2003 (SP1) Server (Standard Edition) with
> SQLServer2000 (SP3). All of the SQLServers are part of our AD domain. The
> SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> domain accounts are members of the local administrator group on each of
> the SQL servers.
> The problem we are having is that when the DBA attempts to copy the
> databases from the Win2000/SQLServer to one of the Win2003 servers the dts
> package fails with the following
> Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> Step Error Description: Unspecified Error
> Step Error Code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
> Under more info it says
> Failed to create the share OMWWIZD
> We have gone through kb article 274663 which convers this but as far as we
> can see we meet all the requirements for this to work, so we are a little
> stumped at this point. We thought it may be something to do with Win2003
> SP1, so we built a Win2003 server with no service packs and tried to copy
> some databases and we received the exact same error.
> I can manually create a share between the Win2000 and Win2003 servers
> using the same domain account the SQLServer process use. Once manually
> created I can create and delete files, so at a filesystem level the
> interopability between the servers appears fine. Running out of ideas at
> this point.
> If anyone has any suggestions they would be greatly welcomed.
> regards
> Craig
>|||In additon to Hari's resonse you can also take a look at
sp_detach_db,sp_attach_db,sp_attach_single_file_db
system stored procedures
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Easy approches to copy the databases are:-
> 1. Backup the production database using (BACKUP DATABASE command)
> 2. COpy the backup file to test server
> 3. Restore the database in test server (RESTORE DATABASE)
>
> See Backup and Restore commad in books online.
>
> Note:
>
> you can also detach and attach databases. See SP_DETACH_DB and
SP_ATTACH_DB
> --
> Thanks
> Hari
>
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > This is the situation. We have some new SQL Servers being installed. The
> > existing SQL servers will be retired. The existing servers run
Windows2000
> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> > servers are running Windows2003 (SP1) Server (Standard Edition) with
> > SQLServer2000 (SP3). All of the SQLServers are part of our AD domain.
The
> > SQLSERVER service and the SQLAGENT servers run as domain accounts. These
> > domain accounts are members of the local administrator group on each of
> > the SQL servers.
> >
> > The problem we are having is that when the DBA attempts to copy the
> > databases from the Win2000/SQLServer to one of the Win2003 servers the
dts
> > package fails with the following
> >
> > Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
> > Step Error Description: Unspecified Error
> > Step Error Code: 80004005
> > Step Error Help File:sqldts80.hlp
> > Step Error Help Context ID:1100
> >
> > Under more info it says
> >
> > Failed to create the share OMWWIZD
> >
> > We have gone through kb article 274663 which convers this but as far as
we
> > can see we meet all the requirements for this to work, so we are a
little
> > stumped at this point. We thought it may be something to do with Win2003
> > SP1, so we built a Win2003 server with no service packs and tried to
copy
> > some databases and we received the exact same error.
> >
> > I can manually create a share between the Win2000 and Win2003 servers
> > using the same domain account the SQLServer process use. Once manually
> > created I can create and delete files, so at a filesystem level the
> > interopability between the servers appears fine. Running out of ideas at
> > this point.
> >
> > If anyone has any suggestions they would be greatly welcomed.
> >
> > regards
> >
> > Craig
> >
>|||Hi Uri and Hari,
thanks for your replies.
Yes, doing a backup/restore is one of the options I suggested to our DBA but
he is reluctant to do that because he says (and I'm no DBA) he loses all the
ownership rigts and has to recreate SQLServer accounts and so on. To me that
doesn't sound to much effort, we are not a big company so there's probably
only a hanfull of accounts, however he is the DBA so I have to go with his
recomedations pertaining to the databases.
One thing I have just found out is that when this server was originally
built it was named server-a. Later on and after Sqlserver was installed they
renamed the server to server-b. Server-a and server-b. Just wondering if
this is part of the problem?
Anyway we will explore a few other avenues and if all else fails we will
have to revert to the ol backup/restore option. I will direct our DBA to
those stored procedures you two have mentioned.
thanks for the assistance
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> In additon to Hari's resonse you can also take a look at
> sp_detach_db,sp_attach_db,sp_attach_single_file_db
> system stored procedures
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Easy approches to copy the databases are:-
>> 1. Backup the production database using (BACKUP DATABASE command)
>> 2. COpy the backup file to test server
>> 3. Restore the database in test server (RESTORE DATABASE)
>>
>> See Backup and Restore commad in books online.
>>
>> Note:
>>
>> you can also detach and attach databases. See SP_DETACH_DB and
> SP_ATTACH_DB
>> --
>> Thanks
>> Hari
>>
>> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> > Hi all,
>> >
>> > This is the situation. We have some new SQL Servers being installed.
>> > The
>> > existing SQL servers will be retired. The existing servers run
> Windows2000
>> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
>> > servers are running Windows2003 (SP1) Server (Standard Edition) with
>> > SQLServer2000 (SP3). All of the SQLServers are part of our AD domain.
> The
>> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
>> > These
>> > domain accounts are members of the local administrator group on each of
>> > the SQL servers.
>> >
>> > The problem we are having is that when the DBA attempts to copy the
>> > databases from the Win2000/SQLServer to one of the Win2003 servers the
> dts
>> > package fails with the following
>> >
>> > Step Error Sourse: Microsoft Data Transformation Services (DTS) Package
>> > Step Error Description: Unspecified Error
>> > Step Error Code: 80004005
>> > Step Error Help File:sqldts80.hlp
>> > Step Error Help Context ID:1100
>> >
>> > Under more info it says
>> >
>> > Failed to create the share OMWWIZD
>> >
>> > We have gone through kb article 274663 which convers this but as far as
> we
>> > can see we meet all the requirements for this to work, so we are a
> little
>> > stumped at this point. We thought it may be something to do with
>> > Win2003
>> > SP1, so we built a Win2003 server with no service packs and tried to
> copy
>> > some databases and we received the exact same error.
>> >
>> > I can manually create a share between the Win2000 and Win2003 servers
>> > using the same domain account the SQLServer process use. Once manually
>> > created I can create and delete files, so at a filesystem level the
>> > interopability between the servers appears fine. Running out of ideas
>> > at
>> > this point.
>> >
>> > If anyone has any suggestions they would be greatly welcomed.
>> >
>> > regards
>> >
>> > Craig
>> >
>>
>|||Craig
Your DBA is not quite correct.
There are two stored procedures that provided by Microsoft to migrate
users/logins with their original SID
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi Uri and Hari,
> thanks for your replies.
> Yes, doing a backup/restore is one of the options I suggested to our DBA
but
> he is reluctant to do that because he says (and I'm no DBA) he loses all
the
> ownership rigts and has to recreate SQLServer accounts and so on. To me
that
> doesn't sound to much effort, we are not a big company so there's probably
> only a hanfull of accounts, however he is the DBA so I have to go with his
> recomedations pertaining to the databases.
> One thing I have just found out is that when this server was originally
> built it was named server-a. Later on and after Sqlserver was installed
they
> renamed the server to server-b. Server-a and server-b. Just wondering if
> this is part of the problem?
> Anyway we will explore a few other avenues and if all else fails we will
> have to revert to the ol backup/restore option. I will direct our DBA to
> those stored procedures you two have mentioned.
> thanks for the assistance
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
> > In additon to Hari's resonse you can also take a look at
> > sp_detach_db,sp_attach_db,sp_attach_single_file_db
> > system stored procedures
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> >> Hi,
> >>
> >> Easy approches to copy the databases are:-
> >>
> >> 1. Backup the production database using (BACKUP DATABASE command)
> >> 2. COpy the backup file to test server
> >> 3. Restore the database in test server (RESTORE DATABASE)
> >>
> >>
> >> See Backup and Restore commad in books online.
> >>
> >>
> >> Note:
> >>
> >>
> >> you can also detach and attach databases. See SP_DETACH_DB and
> > SP_ATTACH_DB
> >>
> >> --
> >> Thanks
> >> Hari
> >>
> >>
> >> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> >> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
> >> > Hi all,
> >> >
> >> > This is the situation. We have some new SQL Servers being installed.
> >> > The
> >> > existing SQL servers will be retired. The existing servers run
> > Windows2000
> >> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
> >> > servers are running Windows2003 (SP1) Server (Standard Edition) with
> >> > SQLServer2000 (SP3). All of the SQLServers are part of our AD domain.
> > The
> >> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
> >> > These
> >> > domain accounts are members of the local administrator group on each
of
> >> > the SQL servers.
> >> >
> >> > The problem we are having is that when the DBA attempts to copy the
> >> > databases from the Win2000/SQLServer to one of the Win2003 servers
the
> > dts
> >> > package fails with the following
> >> >
> >> > Step Error Sourse: Microsoft Data Transformation Services (DTS)
Package
> >> > Step Error Description: Unspecified Error
> >> > Step Error Code: 80004005
> >> > Step Error Help File:sqldts80.hlp
> >> > Step Error Help Context ID:1100
> >> >
> >> > Under more info it says
> >> >
> >> > Failed to create the share OMWWIZD
> >> >
> >> > We have gone through kb article 274663 which convers this but as far
as
> > we
> >> > can see we meet all the requirements for this to work, so we are a
> > little
> >> > stumped at this point. We thought it may be something to do with
> >> > Win2003
> >> > SP1, so we built a Win2003 server with no service packs and tried to
> > copy
> >> > some databases and we received the exact same error.
> >> >
> >> > I can manually create a share between the Win2000 and Win2003 servers
> >> > using the same domain account the SQLServer process use. Once
manually
> >> > created I can create and delete files, so at a filesystem level the
> >> > interopability between the servers appears fine. Running out of ideas
> >> > at
> >> > this point.
> >> >
> >> > If anyone has any suggestions they would be greatly welcomed.
> >> >
> >> > regards
> >> >
> >> > Craig
> >> >
> >>
> >>
> >
> >
>|||Hi Uri,
ok, thanks for the information. I'll pass it on to him. To be fair to him
he's really an Oracle DBA who has had SQLServer dumped on him and he hasn't
had a great deal of training on it yet.
One other thing has come to light and as of yet I am not to sure if it is
the cause or not, we still need to do some more testing. To cut a long and
boring story short we have one AD domain, let's call it oz.com.au. AD is DNS
intergrated. At some point we created some sub domains within abc.com,
namely dev.abc.com and test.abc.com. These new subdomains also have their
own unique ip address range
abc.com 192.168.1.x
dev.abc.com 192.168.2.x
test.abc.com 192.168.3.x
So the fully qualified domain names for our servers would look like this
server1.abc.com
server2.abc.com
server3.dev.abc.com
server4.dev.abc.com
server5.test.abc.com
server6.test.abc.com
however we only have one single AD domain, in this case abc.com.
What we have now found is that we can copy databases between two servers who
share the same FQDN domain part. ie. server1.abc.com to server2.abc.com
works, however copying between two servers who do not share the same domain
part does not and generates the error mentioned in the original post. ie.
server1.abc.com to server3.dev.abc.com
The odd thing is that this setup used to work under Win2000, but obviously
something in Win2003 is different. Might have to bounce this off some people
in the windows groups.
If anyone has any suggestions I'm all ears.
Craig
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Craig
> Your DBA is not quite correct.
> There are two stored procedures that provided by Microsoft to migrate
> users/logins with their original SID
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> Hi Uri and Hari,
>> thanks for your replies.
>> Yes, doing a backup/restore is one of the options I suggested to our DBA
> but
>> he is reluctant to do that because he says (and I'm no DBA) he loses all
> the
>> ownership rigts and has to recreate SQLServer accounts and so on. To me
> that
>> doesn't sound to much effort, we are not a big company so there's
>> probably
>> only a hanfull of accounts, however he is the DBA so I have to go with
>> his
>> recomedations pertaining to the databases.
>> One thing I have just found out is that when this server was originally
>> built it was named server-a. Later on and after Sqlserver was installed
> they
>> renamed the server to server-b. Server-a and server-b. Just wondering if
>> this is part of the problem?
>> Anyway we will explore a few other avenues and if all else fails we will
>> have to revert to the ol backup/restore option. I will direct our DBA to
>> those stored procedures you two have mentioned.
>> thanks for the assistance
>> Craig
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
>> > In additon to Hari's resonse you can also take a look at
>> > sp_detach_db,sp_attach_db,sp_attach_single_file_db
>> > system stored procedures
>> >
>> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> > news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
>> >> Hi,
>> >>
>> >> Easy approches to copy the databases are:-
>> >>
>> >> 1. Backup the production database using (BACKUP DATABASE command)
>> >> 2. COpy the backup file to test server
>> >> 3. Restore the database in test server (RESTORE DATABASE)
>> >>
>> >>
>> >> See Backup and Restore commad in books online.
>> >>
>> >>
>> >> Note:
>> >>
>> >>
>> >> you can also detach and attach databases. See SP_DETACH_DB and
>> > SP_ATTACH_DB
>> >>
>> >> --
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> >> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi all,
>> >> >
>> >> > This is the situation. We have some new SQL Servers being installed.
>> >> > The
>> >> > existing SQL servers will be retired. The existing servers run
>> > Windows2000
>> >> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The new
>> >> > servers are running Windows2003 (SP1) Server (Standard Edition) with
>> >> > SQLServer2000 (SP3). All of the SQLServers are part of our AD
>> >> > domain.
>> > The
>> >> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
>> >> > These
>> >> > domain accounts are members of the local administrator group on each
> of
>> >> > the SQL servers.
>> >> >
>> >> > The problem we are having is that when the DBA attempts to copy the
>> >> > databases from the Win2000/SQLServer to one of the Win2003 servers
> the
>> > dts
>> >> > package fails with the following
>> >> >
>> >> > Step Error Sourse: Microsoft Data Transformation Services (DTS)
> Package
>> >> > Step Error Description: Unspecified Error
>> >> > Step Error Code: 80004005
>> >> > Step Error Help File:sqldts80.hlp
>> >> > Step Error Help Context ID:1100
>> >> >
>> >> > Under more info it says
>> >> >
>> >> > Failed to create the share OMWWIZD
>> >> >
>> >> > We have gone through kb article 274663 which convers this but as far
> as
>> > we
>> >> > can see we meet all the requirements for this to work, so we are a
>> > little
>> >> > stumped at this point. We thought it may be something to do with
>> >> > Win2003
>> >> > SP1, so we built a Win2003 server with no service packs and tried to
>> > copy
>> >> > some databases and we received the exact same error.
>> >> >
>> >> > I can manually create a share between the Win2000 and Win2003
>> >> > servers
>> >> > using the same domain account the SQLServer process use. Once
> manually
>> >> > created I can create and delete files, so at a filesystem level the
>> >> > interopability between the servers appears fine. Running out of
>> >> > ideas
>> >> > at
>> >> > this point.
>> >> >
>> >> > If anyone has any suggestions they would be greatly welcomed.
>> >> >
>> >> > regards
>> >> >
>> >> > Craig
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Hi all,
ok, it turned out that the fully qualified hostnames were in fact causing
the problem. As soon as we put the machines in the same internet domain name
space the DTS copies worked. I assume that something within SQLServer/DTS
assumes that if the FQDN contain different domain parts then therefore they
are in different AD domains. The fix was to simply change the
- FQDN to the same domain
- Copy the databases
- Revert the servers's FQDN back.
Thanks to all who offered various solutions. Now off to the MS support site
to see if there is something I should be configuring in AD when I do this. I
wouldn't have thought so but it looks like there is.
Regards
Craig
"Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
news:%232V5e$3hFHA.3912@.tk2msftngp13.phx.gbl...
> Hi Uri,
> ok, thanks for the information. I'll pass it on to him. To be fair to him
> he's really an Oracle DBA who has had SQLServer dumped on him and he
> hasn't had a great deal of training on it yet.
> One other thing has come to light and as of yet I am not to sure if it is
> the cause or not, we still need to do some more testing. To cut a long and
> boring story short we have one AD domain, let's call it oz.com.au. AD is
> DNS intergrated. At some point we created some sub domains within abc.com,
> namely dev.abc.com and test.abc.com. These new subdomains also have their
> own unique ip address range
> abc.com 192.168.1.x
> dev.abc.com 192.168.2.x
> test.abc.com 192.168.3.x
> So the fully qualified domain names for our servers would look like this
> server1.abc.com
> server2.abc.com
> server3.dev.abc.com
> server4.dev.abc.com
> server5.test.abc.com
> server6.test.abc.com
> however we only have one single AD domain, in this case abc.com.
> What we have now found is that we can copy databases between two servers
> who share the same FQDN domain part. ie. server1.abc.com to
> server2.abc.com works, however copying between two servers who do not
> share the same domain part does not and generates the error mentioned in
> the original post. ie. server1.abc.com to server3.dev.abc.com
> The odd thing is that this setup used to work under Win2000, but obviously
> something in Win2003 is different. Might have to bounce this off some
> people in the windows groups.
> If anyone has any suggestions I'm all ears.
> Craig
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MVyg$2hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> Craig
>> Your DBA is not quite correct.
>> There are two stored procedures that provided by Microsoft to migrate
>> users/logins with their original SID
>>
>> USE master
>> GO
>> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
>> DROP PROCEDURE sp_hexadecimal
>> GO
>> CREATE PROCEDURE sp_hexadecimal
>> @.binvalue varbinary(256),
>> @.hexvalue varchar(256) OUTPUT
>> AS
>> DECLARE @.charvalue varchar(256)
>> DECLARE @.i int
>> DECLARE @.length int
>> DECLARE @.hexstring char(16)
>> SELECT @.charvalue = '0x'
>> SELECT @.i = 1
>> SELECT @.length = DATALENGTH (@.binvalue)
>> SELECT @.hexstring = '0123456789ABCDEF'
>> WHILE (@.i <= @.length)
>> BEGIN
>> DECLARE @.tempint int
>> DECLARE @.firstint int
>> DECLARE @.secondint int
>> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
>> SELECT @.firstint = FLOOR(@.tempint/16)
>> SELECT @.secondint = @.tempint - (@.firstint*16)
>> SELECT @.charvalue = @.charvalue +
>> SUBSTRING(@.hexstring, @.firstint+1, 1) +
>> SUBSTRING(@.hexstring, @.secondint+1, 1)
>> SELECT @.i = @.i + 1
>> END
>> SELECT @.hexvalue = @.charvalue
>> GO
>> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
>> DROP PROCEDURE sp_help_revlogin
>> GO
>> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
>> DECLARE @.name sysname
>> DECLARE @.xstatus int
>> DECLARE @.binpwd varbinary (256)
>> DECLARE @.txtpwd sysname
>> DECLARE @.tmpstr varchar (256)
>> DECLARE @.SID_varbinary varbinary(85)
>> DECLARE @.SID_string varchar(256)
>> IF (@.login_name IS NULL)
>> DECLARE login_curs CURSOR FOR
>> SELECT sid, name, xstatus, password FROM master..sysxlogins
>> WHERE srvid IS NULL AND name <> 'sa'
>> ELSE
>> DECLARE login_curs CURSOR FOR
>> SELECT sid, name, xstatus, password FROM master..sysxlogins
>> WHERE srvid IS NULL AND name = @.login_name
>> OPEN login_curs
>> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
>> IF (@.@.fetch_status = -1)
>> BEGIN
>> PRINT 'No login(s) found.'
>> CLOSE login_curs
>> DEALLOCATE login_curs
>> RETURN -1
>> END
>> SET @.tmpstr = '/* sp_help_revlogin script '
>> PRINT @.tmpstr
>> SET @.tmpstr = '** Generated '
>> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
>> PRINT @.tmpstr
>> PRINT ''
>> PRINT 'DECLARE @.pwd sysname'
>> WHILE (@.@.fetch_status <> -1)
>> BEGIN
>> IF (@.@.fetch_status <> -2)
>> BEGIN
>> PRINT ''
>> SET @.tmpstr = '-- Login: ' + @.name
>> PRINT @.tmpstr
>> IF (@.xstatus & 4) = 4
>> BEGIN -- NT authenticated account/group
>> IF (@.xstatus & 1) = 1
>> BEGIN -- NT login is denied access
>> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
>> PRINT @.tmpstr
>> END
>> ELSE BEGIN -- NT login has access
>> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
>> PRINT @.tmpstr
>> END
>> END
>> ELSE BEGIN -- SQL Server authentication
>> IF (@.binpwd IS NOT NULL)
>> BEGIN -- Non-null password
>> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
>> IF (@.xstatus & 2048) = 2048
>> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
>> ELSE
>> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
>> PRINT @.tmpstr
>> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
>> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
>> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
>> END
>> ELSE BEGIN
>> -- Null password
>> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
>> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
>> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
>> END
>> IF (@.xstatus & 2048) = 2048
>> -- login upgraded from 6.5
>> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
>> ELSE
>> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
>> PRINT @.tmpstr
>> END
>> END
>> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
>> END
>> CLOSE login_curs
>> DEALLOCATE login_curs
>> RETURN 0
>> GO
>> sp_help_revlogin
>> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> news:%23AC4mwzhFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> Hi Uri and Hari,
>> thanks for your replies.
>> Yes, doing a backup/restore is one of the options I suggested to our DBA
>> but
>> he is reluctant to do that because he says (and I'm no DBA) he loses all
>> the
>> ownership rigts and has to recreate SQLServer accounts and so on. To me
>> that
>> doesn't sound to much effort, we are not a big company so there's
>> probably
>> only a hanfull of accounts, however he is the DBA so I have to go with
>> his
>> recomedations pertaining to the databases.
>> One thing I have just found out is that when this server was originally
>> built it was named server-a. Later on and after Sqlserver was installed
>> they
>> renamed the server to server-b. Server-a and server-b. Just wondering if
>> this is part of the problem?
>> Anyway we will explore a few other avenues and if all else fails we will
>> have to revert to the ol backup/restore option. I will direct our DBA to
>> those stored procedures you two have mentioned.
>> thanks for the assistance
>> Craig
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:Oxcc5prhFHA.2372@.TK2MSFTNGP14.phx.gbl...
>> > In additon to Hari's resonse you can also take a look at
>> > sp_detach_db,sp_attach_db,sp_attach_single_file_db
>> > system stored procedures
>> >
>> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> > news:eJz5barhFHA.3608@.TK2MSFTNGP12.phx.gbl...
>> >> Hi,
>> >>
>> >> Easy approches to copy the databases are:-
>> >>
>> >> 1. Backup the production database using (BACKUP DATABASE command)
>> >> 2. COpy the backup file to test server
>> >> 3. Restore the database in test server (RESTORE DATABASE)
>> >>
>> >>
>> >> See Backup and Restore commad in books online.
>> >>
>> >>
>> >> Note:
>> >>
>> >>
>> >> you can also detach and attach databases. See SP_DETACH_DB and
>> > SP_ATTACH_DB
>> >>
>> >> --
>> >> Thanks
>> >> Hari
>> >>
>> >>
>> >> "Craig Matchan" <cwigster@.spammenot-swiftdsl.com.au> wrote in message
>> >> news:ukO7n%23qhFHA.320@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi all,
>> >> >
>> >> > This is the situation. We have some new SQL Servers being
>> >> > installed.
>> >> > The
>> >> > existing SQL servers will be retired. The existing servers run
>> > Windows2000
>> >> > Standard Edition with SP4 and obviously SQLServer2000 (SP3). The
>> >> > new
>> >> > servers are running Windows2003 (SP1) Server (Standard Edition)
>> >> > with
>> >> > SQLServer2000 (SP3). All of the SQLServers are part of our AD
>> >> > domain.
>> > The
>> >> > SQLSERVER service and the SQLAGENT servers run as domain accounts.
>> >> > These
>> >> > domain accounts are members of the local administrator group on
>> >> > each
>> of
>> >> > the SQL servers.
>> >> >
>> >> > The problem we are having is that when the DBA attempts to copy the
>> >> > databases from the Win2000/SQLServer to one of the Win2003 servers
>> the
>> > dts
>> >> > package fails with the following
>> >> >
>> >> > Step Error Sourse: Microsoft Data Transformation Services (DTS)
>> Package
>> >> > Step Error Description: Unspecified Error
>> >> > Step Error Code: 80004005
>> >> > Step Error Help File:sqldts80.hlp
>> >> > Step Error Help Context ID:1100
>> >> >
>> >> > Under more info it says
>> >> >
>> >> > Failed to create the share OMWWIZD
>> >> >
>> >> > We have gone through kb article 274663 which convers this but as
>> >> > far
>> as
>> > we
>> >> > can see we meet all the requirements for this to work, so we are a
>> > little
>> >> > stumped at this point. We thought it may be something to do with
>> >> > Win2003
>> >> > SP1, so we built a Win2003 server with no service packs and tried
>> >> > to
>> > copy
>> >> > some databases and we received the exact same error.
>> >> >
>> >> > I can manually create a share between the Win2000 and Win2003
>> >> > servers
>> >> > using the same domain account the SQLServer process use. Once
>> manually
>> >> > created I can create and delete files, so at a filesystem level the
>> >> > interopability between the servers appears fine. Running out of
>> >> > ideas
>> >> > at
>> >> > this point.
>> >> >
>> >> > If anyone has any suggestions they would be greatly welcomed.
>> >> >
>> >> > regards
>> >> >
>> >> > Craig
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>