November 11, 2011 at 1:36 am
Currently planning a migration from SQL Server 2005 to 2008 R2. I've covered my bases by using Microsoft's migration script for logins and password (http://support.microsoft.com/kb/918992) and scripted out all jobs. All databases will be physically moved by doing a detach/attach. But I've realized that certain users have membership in server roles. How can I easily move these or the memberships to the new server?
/Andy.l
November 11, 2011 at 5:56 am
Use sp_help_revlogin to reverse engineer the instance's logins (see ms website)
I use this to generate the statements to reapply the server roles to logins.
select 'exec master..sp_addsrvrolemember @rolename = N''' + r.name + ''', @loginame = N''' + p.name + '''', p.name, r.name
from sys.server_principals p
inner join sys.server_role_members m on p.principal_id = m.member_principal_id
inner join sys.server_principals r on m.role_principal_id = r.principal_id
November 11, 2011 at 6:20 am
Thanks, that just what i needed:-)
/A
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply