How to migrate windows login in from one sql server to another sql server on another domain

Question

We have a SQL server that is located in domain 1(DMN1). We are migrating databases in domain 1 to our new SQL Server in domain 2(DMN2).Corporate states that there will be no trust established between each domains. So whatever logins the database has in DMN1 cannot be used in DMN2 anymore. it would take a lot of time to sort out a lot of windows logins in DMN1, get their equivalent login in DMN2 and map them to the right databases. What would be an easier way of doing it? Is it possible to just copy the data in each databases without the security part, and just rebuild the security on each database manually? Can anyone share a non time consuming way of accomplishing this?

Answer 1

How to migrate windows login in from one sql server to another sql server on another domain

So whatever logins the database has in DMN1 cannot be used in DMN2 anymore. it would take a lot of time to sort out a lot of windows logins in DMN1, get their equivalent login in DMN2 and map them to the right databases. What would be an easier way of doing it

Take a look at the two below scripts I've used for similar needs in the past. You will need to run these on each DB you need to mirror permissions for the two different domain accounts.

You will need to search the results of script #1 for the principal you need to mirror the access which is changing domains, etc. Once you get the logic out you need, run it with the new value of the domain credential from the new domain and then run that logic on each DB.

Use the script #2 query before and/or after if you wish per each DB to check the records for the security principals.

---

Script 1

/* Script DB Level Permissions v2.1 Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/
DECLARE 
 @sql VARCHAR(2048)
 ,@sort INT 
DECLARE tmp CURSOR FOR
/*********************************************/ /********* DB CONTEXT STATEMENT *********/ /*********************************************/ SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
 1 AS [-- RESULT ORDER HOLDER --] UNION SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
 1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENTS --],
 2 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/ /********* DB USER CREATION *********/ /*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
 3 AS [-- RESULT ORDER HOLDER --] UNION SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
 4 AS [-- RESULT ORDER HOLDER --] FROM sys.database_principals AS rm WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
UNION
/*********************************************/ /********* DB ROLE PERMISSIONS *********/ /*********************************************/ SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
 5 AS [-- RESULT ORDER HOLDER --] UNION SELECT 'EXEC sp_addrolemember @rolename ='
 + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
 6 AS [-- RESULT ORDER HOLDER --] FROM sys.database_role_members AS rm WHERE USER_NAME(rm.member_principal_id) IN ( 
 --get user names on the database
 SELECT [name]
 FROM sys.database_principals
 WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
 and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
 )
--ORDER BY rm.role_principal_id ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
 7 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/ /********* OBJECT LEVEL PERMISSIONS *********/ /*********************************************/ SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
 8 AS [-- RESULT ORDER HOLDER --] UNION SELECT CASE 
 WHEN perm.state <> 'W' THEN perm.state_desc 
 ELSE 'GRANT'
 END
 + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
--select, execute, etc on specific objects
 + CASE
 WHEN cl.column_id IS NULL THEN SPACE(0)
 ELSE '(' + QUOTENAME(cl.name) + ')'
 END
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
 + CASE 
 WHEN perm.state <> 'W' THEN SPACE(0)
 ELSE SPACE(1) + 'WITH GRANT OPTION'
 END
 AS [-- SQL STATEMENTS --],
 9 AS [-- RESULT ORDER HOLDER --] FROM 
 sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
 10 AS [-- RESULT ORDER HOLDER --]
UNION
/*********************************************/ /********* DB LEVEL PERMISSIONS *********/ /*********************************************/ SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
 11 AS [-- RESULT ORDER HOLDER --] UNION SELECT CASE 
 WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
 ELSE 'GRANT'
 END
 + SPACE(1) + perm.permission_name --CONNECT, etc
 + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
 + CASE 
 WHEN perm.state <> 'W' THEN SPACE(0) 
 ELSE SPACE(1) + 'WITH GRANT OPTION' 
 END
 AS [-- SQL STATEMENTS --],
 12 AS [-- RESULT ORDER HOLDER --] FROM sys.database_permissions AS perm
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser
WHERE [perm].[major_id] = 0
 AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
 AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
UNION
SELECT '' AS [-- SQL STATEMENTS --],
 13 AS [-- RESULT ORDER HOLDER --]
UNION 
SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
 14 AS [-- RESULT ORDER HOLDER --] UNION SELECT CASE
 WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
 ELSE 'GRANT'
 END
 + SPACE(1) + perm.permission_name --CONNECT, etc
 + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
 + QUOTENAME(SCHEMA_NAME(major_id))
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
 + CASE
 WHEN perm.state <> 'W' THEN SPACE(0)
 ELSE SPACE(1) + 'WITH GRANT OPTION'
 END
 AS [-- SQL STATEMENTS --],
 15 AS [-- RESULT ORDER HOLDER --] from sys.database_permissions AS perm
 inner join sys.schemas s
 on perm.major_id = s.schema_id
 inner join sys.database_principals dbprin
 on perm.grantee_principal_id = dbprin.principal_id WHERE class = 3 --class 3 = schema
ORDER BY [-- RESULT ORDER HOLDER --]
OPEN tmp FETCH NEXT FROM tmp INTO @sql, @sort WHILE @@FETCH_STATUS = 0 BEGIN
 PRINT @sql
 FETCH NEXT FROM tmp INTO @sql, @sort END
CLOSE tmp DEALLOCATE tmp

_source

Script2

CREATE VIEW vwObjectPermissions
AS
SELECT schema_name(o.schema_id) AS [Schema_Name]
 ,o.NAME AS [object_name]
 ,u.NAME AS [principal_name]
 ,u.type_desc AS [principal_type]
 ,r.minor_id
 ,r.permission_name
 ,r.state_desc
 ,o.schema_id
 ,o.principal_id AS [alt_owner]
 ,o.type_desc
FROM sys.database_permissions r
LEFT JOIN sys.database_Principals u ON r.grantee_principal_id = u.principal_id
LEFT JOIN sys.all_objects o ON o.object_id = r.major_id
WHERE class_desc NOT IN ('database')
GO
--1. Check if Public or guest is granted any permission on an object (database role and server role)
SELECT *
FROM vwObjectPermissions
WHERE principal_name IN ('Public','Guest')
--2. Check if any user is granted permissions on an object rather than roles.
SELECT *
FROM vwObjectPermissions
WHERE principal_type NOT LIKE '%ROLE%'
--3. Check if a user has "with grant" previliges on an object
SELECT *
FROM vwObjectPermissions
WHERE state_desc = 'WITH GRANT' --check the spelling on this one
--4. Check who has access to extended stored procedures (which I get from select name from sysobjects where xtype='X')
SELECT *
FROM vwObjectPermissions
WHERE type_desc LIKE '%X%Proc%'
GO
DROP VIEW vwObjectPermissions;

_source

• Keith Rivera: @PUMP_JUICE_IT you just saved my long weekend. Now all I have to do is mirror the server roles on the instance level. Thank you so much!
• Keith Rivera: Quick note on this script. This does not copy the database role's owned schemas. For those who will use this, manually assign the database role's owned schema.