Sql-Server-2012
如何將 Windows 登錄從一個 sql server 遷移到另一個域上的另一個 sql server
我們有一個位於域 1(DMN1) 中的 SQL 伺服器。我們正在將域 1 中的數據庫遷移到域 2 (DMN2) 中的新 SQL Server。公司聲明不會在每個域之間建立信任。因此，數據庫在 DMN1 中的任何登錄都不能再在 DMN2 中使用。在DMN1中整理大量Windows登錄，在DMN2中獲取它們的等效登錄並將它們映射到正確的數據庫將花費大量時間。有什麼更簡單的方法呢？是否可以在沒有安全部分的情況下僅複製每個數據庫中的數據，然後手動重建每個數據庫的安全性？任何人都可以分享一種不費時的方式來實現這一點嗎？
如何將 Windows 登錄從一個 sql server 遷移到另一個域上的另一個 sql server
因此，數據庫在 DMN1 中的任何登錄都不能再在 DMN2 中使用。在DMN1中整理大量Windows登錄，在DMN2中獲取它們的等效登錄並將它們映射到正確的數據庫將花費大量時間。什麼是更簡單的方法
看看我過去用於類似需求的以下兩個腳本。您需要在需要鏡像兩個不同域帳戶的權限的每個數據庫上執行這些。
您將需要在腳本#1 的結果中搜尋您需要鏡像正在更改域的訪問等的主體。一旦您獲得所需的邏輯，請使用來自新域的域憑據的新值執行它然後在每個數據庫上執行該邏輯。
如果您希望每個數據庫檢查安全主體的記錄，請在之前和/或之後使用腳本#2 查詢。
腳本 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腳本2
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;