SQL Users, Roles and permissions Migration

Question

Are there any scripts available to migrate SQL Server users from SQL Server 2008 to SQL Server 2008 R2 along with the User Mappings, Roles and permissions please?

I am following

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

But the instructions in that link just assists in migrating the users.

The user mappings, roles and permissions don't get migrated.

Apologies if such question was already answered.

I had a quick search, but could not find a solution to my issue.

Thanks

Answer 1

Does this work?

/****** Object: StoredProcedure [dbo].[usp_get_object_permissions] Script Date: 10/07/2011 14:28:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- run this script from the user database
--exec usp_get_object_permissions
--drop proc usp_get_object_permissions
--go
CREATE PROCEDURE [dbo].[usp_get_object_permissions]
AS

set nocount on
set quoted_identifier off
declare @as_ObjectName sysname
set @as_ObjectName = NULL

--database owner info
select 'alter authorization on database::['+db_name()+'] to ['+ suser_sname(owner_sid)+']'
AS '--owner of database when script was created'
from master.sys.databases where name = db_name()


--drop and recreate users

select '-- It is not always necessary to drop and recreate the users it will depend on the circumstances under which you need to run this script'

select 'drop user [' + name + ']' from sys.database_principals 
where principal_id > 4 and owning_principal_id is NULL 
and type != 'A'
order by name

select 'CREATE USER [' + dp.name collate database_default + '] FOR LOGIN [' + sp.name + ']'+
case dp.type 
when 'G' then ' '
else
' WITH DEFAULT_SCHEMA=['+dp.default_schema_name + ']' 
end
as '-- by default Orphaned users will not be recreated'
from sys.server_principals sp 
inner join sys.database_principals dp on dp.sid = sp.sid 
where dp.principal_id > 4 and dp.owning_principal_id is NULL and sp.name <> ''
order by dp.name

-- Recreate the User defined roles
select '-- server created roles should be added by the correct processes'

select 'CREATE ROLE ['+ name + '] AUTHORIZATION ['+USER_NAME(owning_principal_id)+']'
from     sys.database_principals 
    where name != 'public' and type = 'R' and is_fixed_role = 0

-- recreate application roles

select 'CREATE APPLICATION ROLE ['+ name + '] with password = '+QUOTENAME('insertpwdhere','''')+' ,default_schema = ['+default_schema_name+']'
from     sys.database_principals 
    where type = 'A'


-- ADD ROLE MEMBERS

SELECT 'EXEC sp_addrolemember [' + dp.name + '], [' + USER_NAME(drm.member_principal_id) + '] ' AS [-- AddRolemembers]
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
where USER_NAME(drm.member_principal_id) != 'dbo'
order by drm.role_principal_id


-- CREATE GRANT Object PERMISSIONS SCRIPT

SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' ON [' 
+ OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + ']'+
case minor_id 
when 0 then ' '
else
' (['+col_name(sys.database_permissions.major_Id, sys.database_permissions.minor_id) + '])' 
end
+' TO [' + USER_NAME(grantee_principal_id)+']' +
case 
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- object/column permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class not in (0,3) and major_id = ISNULL(OBJECT_ID(@as_ObjectName), major_id)
--AND OBJECT_SCHEMA_NAME(major_id) != 'SYS'
ORDER BY USER_NAME(grantee_principal_id),OBJECT_SCHEMA_NAME(major_id), OBJECT_NAME(major_id)

--SCHEMA permissions
SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' ON SCHEMA::[' 
+ SCHEMA_NAME(major_id) + ']'+
+' TO [' + USER_NAME(grantee_principal_id)+']' +
case 
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- Schema permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class_desc = 'SCHEMA'
ORDER BY USER_NAME(grantee_principal_id),SCHEMA_NAME(major_id)

SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + 
' TO [' + USER_NAME(grantee_principal_id)+']' +
case 
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
FROM sys.database_permissions (NOLOCK)
WHERE permission_name = 'VIEW DEFINITION' and class_desc = 'database'
ORDER BY USER_NAME(grantee_principal_id)