Hi,
I need to migrate all databases from one server to another
existing database server, at this time i know how to
migrate all users logins avoiding orphaned users but i
dont know how to migrate all existing roles and how to
assign logins to existing Server Roles on the destination
with the minimum effort.
Best regards
Hi,
As soon as you transfer the syslogins table the server wide fixed roles also
will be transferred.
Have a look into the below link , This script will provide a easy method to
quickly generate a script that will move all logins along with same id and
password from one server to other.
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:22d9501c45dd8$18b37630$a401280a@.phx.gbl...
> Hi,
> I need to migrate all databases from one server to another
> existing database server, at this time i know how to
> migrate all users logins avoiding orphaned users but i
> dont know how to migrate all existing roles and how to
> assign logins to existing Server Roles on the destination
> with the minimum effort.
> Best regards
|||Hi,
As soon as you transfer the syslogins table the server wide fixed roles also
will be transferred.
Have a look into the below link , This script will provide a easy method to
quickly generate a script that will move all logins along with same id and
password from one server to other.
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:22d9501c45dd8$18b37630$a401280a@.phx.gbl...
> Hi,
> I need to migrate all databases from one server to another
> existing database server, at this time i know how to
> migrate all users logins avoiding orphaned users but i
> dont know how to migrate all existing roles and how to
> assign logins to existing Server Roles on the destination
> with the minimum effort.
> Best regards
|||Hi Hari,
I tried and the server roles were migrated to the new
server but the roles appear with no members. The users
that belong to any existing server roles do not belong to
the destination server roles too.
I think that i must use the sp_addrolemember to assign the
users migrated to the corresponding database roles
Best regards
>--Original Message--
>Hi,
>As soon as you transfer the syslogins table the server
wide fixed roles also
>will be transferred.
>Have a look into the below link , This script will
provide a easy method to
>quickly generate a script that will move all logins along
with same id and
>password from one server to other.
>
>http://www.databasejournal.com/featu...l/article.php/
2228611
>--
>Thanks
>Hari
>MCDBA
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22d9501c45dd8$18b37630$a401280a@.phx.gbl...
another[vbcol=seagreen]
destination
>
>.
>
|||Hi Hari,
I tried and the server roles were migrated to the new
server but the roles appear with no members. The users
that belong to any existing server roles do not belong to
the destination server roles too.
I think that i must use the sp_addrolemember to assign the
users migrated to the corresponding database roles
Best regards
>--Original Message--
>Hi,
>As soon as you transfer the syslogins table the server
wide fixed roles also
>will be transferred.
>Have a look into the below link , This script will
provide a easy method to
>quickly generate a script that will move all logins along
with same id and
>password from one server to other.
>
>http://www.databasejournal.com/featu...l/article.php/
2228611
>--
>Thanks
>Hari
>MCDBA
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22d9501c45dd8$18b37630$a401280a@.phx.gbl...
another[vbcol=seagreen]
destination
>
>.
>
|||HI,
After migration, Can you execute the below command:-
select * from syslogins
and see the below column values:-
sysadmin int 1, if login is a member of the sysadmin server role.
securityadmin int 1, if login is a member of the securityadmin server
role.
serveradmin int 1, if login is a member of the serveradmin fixed
server role.
setupadmin int 1, if login is a member of the setupadmin fixed server
role.
processadmin int 1, if login is a member of the processadmin fixed
server role.
diskadmin int 1, if login is a member of the diskadmin fixed server
role.
dbcreator int 1, if login is a member of the dbcreator fixed server
role.
If you have the value 1 for the column then the role is transfered from
source server to destination.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2269701c45de4$91a25c80$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Hari,
> I tried and the server roles were migrated to the new
> server but the roles appear with no members. The users
> that belong to any existing server roles do not belong to
> the destination server roles too.
> I think that i must use the sp_addrolemember to assign the
> users migrated to the corresponding database roles
> Best regards
> wide fixed roles also
> provide a easy method to
> with same id and
> 2228611
> message
> another
> destination
|||HI,
After migration, Can you execute the below command:-
select * from syslogins
and see the below column values:-
sysadmin int 1, if login is a member of the sysadmin server role.
securityadmin int 1, if login is a member of the securityadmin server
role.
serveradmin int 1, if login is a member of the serveradmin fixed
server role.
setupadmin int 1, if login is a member of the setupadmin fixed server
role.
processadmin int 1, if login is a member of the processadmin fixed
server role.
diskadmin int 1, if login is a member of the diskadmin fixed server
role.
dbcreator int 1, if login is a member of the dbcreator fixed server
role.
If you have the value 1 for the column then the role is transfered from
source server to destination.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2269701c45de4$91a25c80$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Hari,
> I tried and the server roles were migrated to the new
> server but the roles appear with no members. The users
> that belong to any existing server roles do not belong to
> the destination server roles too.
> I think that i must use the sp_addrolemember to assign the
> users migrated to the corresponding database roles
> Best regards
> wide fixed roles also
> provide a easy method to
> with same id and
> 2228611
> message
> another
> destination
Showing posts with label tomigrate. Show all posts
Showing posts with label tomigrate. Show all posts
Wednesday, March 21, 2012
Migrate Roles
Hi,
I need to migrate all databases from one server to another
existing database server, at this time i know how to
migrate all users logins avoiding orphaned users but i
dont know how to migrate all existing roles and how to
assign logins to existing Server Roles on the destination
with the minimum effort.
Best regardsHi,
As soon as you transfer the syslogins table the server wide fixed roles also
will be transferred.
Have a look into the below link , This script will provide a easy method to
quickly generate a script that will move all logins along with same id and
password from one server to other.
http://www.databasejournal.com/feat...cle.php/2228611
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:22d9501c45dd8$18b37630$a401280a@.phx
.gbl...
> Hi,
> I need to migrate all databases from one server to another
> existing database server, at this time i know how to
> migrate all users logins avoiding orphaned users but i
> dont know how to migrate all existing roles and how to
> assign logins to existing Server Roles on the destination
> with the minimum effort.
> Best regards|||Hi Hari,
I tried and the server roles were migrated to the new
server but the roles appear with no members. The users
that belong to any existing server roles do not belong to
the destination server roles too.
I think that i must use the sp_addrolemember to assign the
users migrated to the corresponding database roles
Best regards
>--Original Message--
>Hi,
>As soon as you transfer the syslogins table the server
wide fixed roles also
>will be transferred.
>Have a look into the below link , This script will
provide a easy method to
>quickly generate a script that will move all logins along
with same id and
>password from one server to other.
>
>http://www.databasejournal.com/feat...ql/article.php/
2228611
>--
>Thanks
>Hari
>MCDBA
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:22d9501c45dd8$18b37630$a401280a@.phx
.gbl...
another[vbcol=seagreen]
destination[vbcol=seagreen]
>
>.
>|||HI,
After migration, Can you execute the below command:-
select * from syslogins
and see the below column values:-
sysadmin int 1, if login is a member of the sysadmin server role.
securityadmin int 1, if login is a member of the securityadmin server
role.
serveradmin int 1, if login is a member of the serveradmin fixed
server role.
setupadmin int 1, if login is a member of the setupadmin fixed server
role.
processadmin int 1, if login is a member of the processadmin fixed
server role.
diskadmin int 1, if login is a member of the diskadmin fixed server
role.
dbcreator int 1, if login is a member of the dbcreator fixed server
role.
If you have the value 1 for the column then the role is transfered from
source server to destination.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2269701c45de4$91a25c80$a601280a@.phx
.gbl...[vbcol=seagreen]
> Hi Hari,
> I tried and the server roles were migrated to the new
> server but the roles appear with no members. The users
> that belong to any existing server roles do not belong to
> the destination server roles too.
> I think that i must use the sp_addrolemember to assign the
> users migrated to the corresponding database roles
> Best regards
>
> wide fixed roles also
> provide a easy method to
> with same id and
> 2228611
> message
> another
> destination
I need to migrate all databases from one server to another
existing database server, at this time i know how to
migrate all users logins avoiding orphaned users but i
dont know how to migrate all existing roles and how to
assign logins to existing Server Roles on the destination
with the minimum effort.
Best regardsHi,
As soon as you transfer the syslogins table the server wide fixed roles also
will be transferred.
Have a look into the below link , This script will provide a easy method to
quickly generate a script that will move all logins along with same id and
password from one server to other.
http://www.databasejournal.com/feat...cle.php/2228611
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:22d9501c45dd8$18b37630$a401280a@.phx
.gbl...
> Hi,
> I need to migrate all databases from one server to another
> existing database server, at this time i know how to
> migrate all users logins avoiding orphaned users but i
> dont know how to migrate all existing roles and how to
> assign logins to existing Server Roles on the destination
> with the minimum effort.
> Best regards|||Hi Hari,
I tried and the server roles were migrated to the new
server but the roles appear with no members. The users
that belong to any existing server roles do not belong to
the destination server roles too.
I think that i must use the sp_addrolemember to assign the
users migrated to the corresponding database roles
Best regards
>--Original Message--
>Hi,
>As soon as you transfer the syslogins table the server
wide fixed roles also
>will be transferred.
>Have a look into the below link , This script will
provide a easy method to
>quickly generate a script that will move all logins along
with same id and
>password from one server to other.
>
>http://www.databasejournal.com/feat...ql/article.php/
2228611
>--
>Thanks
>Hari
>MCDBA
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:22d9501c45dd8$18b37630$a401280a@.phx
.gbl...
another[vbcol=seagreen]
destination[vbcol=seagreen]
>
>.
>|||HI,
After migration, Can you execute the below command:-
select * from syslogins
and see the below column values:-
sysadmin int 1, if login is a member of the sysadmin server role.
securityadmin int 1, if login is a member of the securityadmin server
role.
serveradmin int 1, if login is a member of the serveradmin fixed
server role.
setupadmin int 1, if login is a member of the setupadmin fixed server
role.
processadmin int 1, if login is a member of the processadmin fixed
server role.
diskadmin int 1, if login is a member of the diskadmin fixed server
role.
dbcreator int 1, if login is a member of the dbcreator fixed server
role.
If you have the value 1 for the column then the role is transfered from
source server to destination.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:2269701c45de4$91a25c80$a601280a@.phx
.gbl...[vbcol=seagreen]
> Hi Hari,
> I tried and the server roles were migrated to the new
> server but the roles appear with no members. The users
> that belong to any existing server roles do not belong to
> the destination server roles too.
> I think that i must use the sp_addrolemember to assign the
> users migrated to the corresponding database roles
> Best regards
>
> wide fixed roles also
> provide a easy method to
> with same id and
> 2228611
> message
> another
> destination
Migrate jobs from production to backup
The backup server is using the agent to run log shipping. If I were to
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
Jamie
Hi
"thejamie" wrote:
> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I can
> turn them off so that having them run is not an issue, migrating them one at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.
> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.
> --
> Regards,
> Jamie
John
|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
Regards,
Jamie
"John Bell" wrote:
> Hi
> "thejamie" wrote:
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled when
> you are scripting them if you don't want to change them on the destination
> server.
>
> John
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
Jamie
Hi
"thejamie" wrote:
> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I can
> turn them off so that having them run is not an issue, migrating them one at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.
> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.
> --
> Regards,
> Jamie
John
|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
Regards,
Jamie
"John Bell" wrote:
> Hi
> "thejamie" wrote:
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled when
> you are scripting them if you don't want to change them on the destination
> server.
>
> John
Migrate jobs from production to backup
The backup server is using the agent to run log shipping. If I were to
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
JamieHi
"thejamie" wrote:
> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I c
an
> turn them off so that having them run is not an issue, migrating them one
at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.
> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.
> --
> Regards,
> Jamie
John|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
--
Regards,
Jamie
"John Bell" wrote:
> Hi
> "thejamie" wrote:
>
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled wh
en
> you are scripting them if you don't want to change them on the destination
> server.
>
> John
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
JamieHi
"thejamie" wrote:
> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I c
an
> turn them off so that having them run is not an issue, migrating them one
at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.
> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.
> --
> Regards,
> Jamie
John|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
--
Regards,
Jamie
"John Bell" wrote:
> Hi
> "thejamie" wrote:
>
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled wh
en
> you are scripting them if you don't want to change them on the destination
> server.
>
> John
Subscribe to:
Posts (Atom)