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/features/mssql/article.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/features/mssql/article.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,
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...
> 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/features/mssql/article.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
> >
> >
> >.
> >

No comments:

Post a Comment