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

migrationSecuritysql-server-2012users

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?

Best Answer

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

Related Question