Copy Logins from Server1 to Server2

Hello,
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other

---

sqlServer?

quote:

---

> Thanks
> Jose.
>

---

|||There is a really good doc under Books On line, search for Log shipping...
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other

---

sqlServer?

quote:

---

> Thanks
> Jose.
>

---

Copy Logins from Server1 to Server2

Hello,
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>|||There is a really good doc under Books On line, search for Log shipping...
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>

Copy files after backup in maintenance plan

Hello,

We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?

Thanks in advance.

Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........

else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.

Thanxx

Deepak

|||

Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.

Madhu

|||

Thank you both for the fast replys....

From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?

Thanks again in advance!

|||

Hi Marty,

Go to BIDS--> create a New project -- > Open the project.

In solution explorer you can see "SSIS packages" folder --> Right click on the folder

Select "Add existing package"

Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.

|||Excellent...thank you so much

Copy files after backup in maintenance plan

Hello,

We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?

Thanks in advance.

Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........

else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.

Thanxx

Deepak

|||

Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.

Madhu

|||

Thank you both for the fast replys....

From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?

Thanks again in advance!

|||

Hi Marty,

Go to BIDS--> create a New project -- > Open the project.

In solution explorer you can see "SSIS packages" folder --> Right click on the folder

Select "Add existing package"

Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.

|||Excellent...thank you so muchsqlsql

Copy Database Wizard and Logins?

I want to copy a database (including login users) from server A to server B
using the Copy Database Wizard.
I find that a Login is created at the server level, but it is disabled. When
I enable it, it seems to be a different user than at the database level.
What is the correct procedure to get the logins copied over and working?
OlavCDW doesn't create the login using the same SID as the original login. Use sp_help_revlogin (search
KB) instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav" <x@.y.com> wrote in message news:OfD%23hsZXGHA.3444@.TK2MSFTNGP05.phx.gbl...
>I want to copy a database (including login users) from server A to server B using the Copy Database
>Wizard.
> I find that a Login is created at the server level, but it is disabled. When I enable it, it seems
> to be a different user than at the database level.
> What is the correct procedure to get the logins copied over and working?
> Olav
>

Copy Database Wizard and Logins?

I want to copy a database (including login users) from server A to server B
using the Copy Database Wizard.
I find that a Login is created at the server level, but it is disabled. When
I enable it, it seems to be a different user than at the database level.
What is the correct procedure to get the logins copied over and working?
OlavCDW doesn't create the login using the same SID as the original login. Use s
p_help_revlogin (search
KB) instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav" <x@.y.com> wrote in message news:OfD%23hsZXGHA.3444@.TK2MSFTNGP05.phx.gbl...reen">
>I want to copy a database (including login users) from server A to server B
using the Copy Database
>Wizard.
> I find that a Login is created at the server level, but it is disabled. Wh
en I enable it, it seems
> to be a different user than at the database level.
> What is the correct procedure to get the logins copied over and working?
> Olav
>sqlsql

Copy Database or Database Restore

Hi,

I am restoring one of my database from test box to production box. I don't have all the logins defined(users for copied database) in production box, What happen if user tries to access the database. I guess they will not be able as user is defined but there is no matching logins.

Will Copy Database wizard creates all logins as Restore Database does not?

Thanks

No. When you copy the database as well as when you restore the database, you only get the database. The logins have to be copied over separately. This can be done with a copy logins tasks in SSIS. Then once the logins are moved, you can remap the users to teh logins using sp_change_userslogin.

Copy database AND users in automated way?

With 2005 (although the answer is probably the same for 2000):
I want to have BAK files copied to another server, the other server import
that copy of the database, and the other server be available for
testing/development purposes with current data. The problem is the user
records.
I know how to use TSQL to make a backup BAK file of a database. I know how
to restore that file onto another server (dev server) with TSQL. So far so
good. The problem is that the backup copy of the database imports in with
bad user records that don't really exist on the new server. Even if the
same user names exist, the record ID don't point at each other. As you may
know, you have to do an sp_dropuser on those "not aligned properly" records,
and then manually add back the users to the Development SQL Server, and then
add those users to the dev database copy. But I do this user stuff through
the GUI and not through TSQL and I need a way to have the BAK files copy
over and start working automatically. I could probably add the user to the
server through TSQL and add the user to a the database, with a specified
role, but 1) I don't know that TSQL (please tell me) and 2) I don't want
to have TSQL lying around on the server with user passwords in it.
Anyone know a good solution to this problem? Ideally I would like to run
some TSQL that will line up the record IDs for the users in the database
copy to users I place into the server one time only and that way I don't
have to enter passwords more than once.
Take a look at sp_help_revlogin. It allows you to extract the credentials
from the source server and apply them to the target.
http://support.microsoft.com/kb/246133
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:1EoVf.12036$w86.3511@.tornado.socal.rr.com...
> With 2005 (although the answer is probably the same for 2000):
> I want to have BAK files copied to another server, the other server import
> that copy of the database, and the other server be available for
> testing/development purposes with current data. The problem is the user
> records.
> I know how to use TSQL to make a backup BAK file of a database. I know
> how
> to restore that file onto another server (dev server) with TSQL. So far
> so
> good. The problem is that the backup copy of the database imports in
> with
> bad user records that don't really exist on the new server. Even if the
> same user names exist, the record ID don't point at each other. As you
> may
> know, you have to do an sp_dropuser on those "not aligned properly"
> records,
> and then manually add back the users to the Development SQL Server, and
> then
> add those users to the dev database copy. But I do this user stuff
> through
> the GUI and not through TSQL and I need a way to have the BAK files copy
> over and start working automatically. I could probably add the user to
> the
> server through TSQL and add the user to a the database, with a specified
> role, but 1) I don't know that TSQL (please tell me) and 2) I don't
> want
> to have TSQL lying around on the server with user passwords in it.
> Anyone know a good solution to this problem? Ideally I would like to run
> some TSQL that will line up the record IDs for the users in the database
> copy to users I place into the server one time only and that way I don't
> have to enter passwords more than once.
>

Copy database AND users in automated way?

With 2005 (although the answer is probably the same for 2000):
I want to have BAK files copied to another server, the other server import
that copy of the database, and the other server be available for
testing/development purposes with current data. The problem is the user
records.
I know how to use TSQL to make a backup BAK file of a database. I know how
to restore that file onto another server (dev server) with TSQL. So far so
good. The problem is that the backup copy of the database imports in with
bad user records that don't really exist on the new server. Even if the
same user names exist, the record ID don't point at each other. As you may
know, you have to do an sp_dropuser on those "not aligned properly" records,
and then manually add back the users to the Development SQL Server, and then
add those users to the dev database copy. But I do this user stuff through
the GUI and not through TSQL and I need a way to have the BAK files copy
over and start working automatically. I could probably add the user to the
server through TSQL and add the user to a the database, with a specified
role, but 1) I don't know that TSQL (please tell me) and 2) I don't want
to have TSQL lying around on the server with user passwords in it.
Anyone know a good solution to this problem? Ideally I would like to run
some TSQL that will line up the record IDs for the users in the database
copy to users I place into the server one time only and that way I don't
have to enter passwords more than once.Take a look at sp_help_revlogin. It allows you to extract the credentials
from the source server and apply them to the target.
http://support.microsoft.com/kb/246133
--
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:1EoVf.12036$w86.3511@.tornado.socal.rr.com...
> With 2005 (although the answer is probably the same for 2000):
> I want to have BAK files copied to another server, the other server import
> that copy of the database, and the other server be available for
> testing/development purposes with current data. The problem is the user
> records.
> I know how to use TSQL to make a backup BAK file of a database. I know
> how
> to restore that file onto another server (dev server) with TSQL. So far
> so
> good. The problem is that the backup copy of the database imports in
> with
> bad user records that don't really exist on the new server. Even if the
> same user names exist, the record ID don't point at each other. As you
> may
> know, you have to do an sp_dropuser on those "not aligned properly"
> records,
> and then manually add back the users to the Development SQL Server, and
> then
> add those users to the dev database copy. But I do this user stuff
> through
> the GUI and not through TSQL and I need a way to have the BAK files copy
> over and start working automatically. I could probably add the user to
> the
> server through TSQL and add the user to a the database, with a specified
> role, but 1) I don't know that TSQL (please tell me) and 2) I don't
> want
> to have TSQL lying around on the server with user passwords in it.
> Anyone know a good solution to this problem? Ideally I would like to run
> some TSQL that will line up the record IDs for the users in the database
> copy to users I place into the server one time only and that way I don't
> have to enter passwords more than once.
>

Copy database AND users in automated way?

With 2005 (although the answer is probably the same for 2000):
I want to have BAK files copied to another server, the other server import
that copy of the database, and the other server be available for
testing/development purposes with current data. The problem is the user
records.
I know how to use TSQL to make a backup BAK file of a database. I know how
to restore that file onto another server (dev server) with TSQL. So far so
good. The problem is that the backup copy of the database imports in with
bad user records that don't really exist on the new server. Even if the
same user names exist, the record ID don't point at each other. As you may
know, you have to do an sp_dropuser on those "not aligned properly" records,
and then manually add back the users to the Development SQL Server, and then
add those users to the dev database copy. But I do this user stuff through
the GUI and not through TSQL and I need a way to have the BAK files copy
over and start working automatically. I could probably add the user to the
server through TSQL and add the user to a the database, with a specified
role, but 1) I don't know that TSQL (please tell me) and 2) I don't want
to have TSQL lying around on the server with user passwords in it.
Anyone know a good solution to this problem? Ideally I would like to run
some TSQL that will line up the record IDs for the users in the database
copy to users I place into the server one time only and that way I don't
have to enter passwords more than once.Take a look at sp_help_revlogin. It allows you to extract the credentials
from the source server and apply them to the target.
http://support.microsoft.com/kb/246133
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:1EoVf.12036$w86.3511@.tornado.socal.rr.com...
> With 2005 (although the answer is probably the same for 2000):
> I want to have BAK files copied to another server, the other server import
> that copy of the database, and the other server be available for
> testing/development purposes with current data. The problem is the user
> records.
> I know how to use TSQL to make a backup BAK file of a database. I know
> how
> to restore that file onto another server (dev server) with TSQL. So far
> so
> good. The problem is that the backup copy of the database imports in
> with
> bad user records that don't really exist on the new server. Even if the
> same user names exist, the record ID don't point at each other. As you
> may
> know, you have to do an sp_dropuser on those "not aligned properly"
> records,
> and then manually add back the users to the Development SQL Server, and
> then
> add those users to the dev database copy. But I do this user stuff
> through
> the GUI and not through TSQL and I need a way to have the BAK files copy
> over and start working automatically. I could probably add the user to
> the
> server through TSQL and add the user to a the database, with a specified
> role, but 1) I don't know that TSQL (please tell me) and 2) I don't
> want
> to have TSQL lying around on the server with user passwords in it.
> Anyone know a good solution to this problem? Ideally I would like to run
> some TSQL that will line up the record IDs for the users in the database
> copy to users I place into the server one time only and that way I don't
> have to enter passwords more than once.
>

Copy a column content in another one only if empty

for MS SQL 2000
table [Users] :
[id_Users] [int] NOT NULL ,
[Name] [varchar] (25) NOT NULL,
[Alias] [varchar] (25) NULL
how can I copy the content of [Name] into the column [Alias] only if [Alias] is Empty ?
thank you--I ve consider null value also in this code.
update Users
set Alias=Name where Alias is null or ltrim(rtrim(Alias))=''|||wonderfull !

it works :-)

thanks a lot