Sql-Server
編寫數據庫使用者級別權限的腳本
我試圖搜尋這個並且能夠找到一些但找不到確切的方法來編寫數據庫的數據庫級別權限。
我正在遷移可以使用創建登錄名的數據庫
sp_help_revlogin，但不會複製使用者權限。我可以使用以下查詢提取權限：
SELECT ISNULL(OBJECT_NAME(major_id),'') [Objects], USER_NAME(grantee_principal_id) as [UserName], permission_name as [PermissionName] FROM sys.database_permissions p WHERE grantee_principal_id>0 ORDER BY OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name但我需要類似 rev login 的東西，它會生成一個腳本，您可以執行以獲取映射的權限。
我已經嘗試過 DBAtools，但我無法使用，因為它失敗並出現錯誤
找不到類型
$$ DbaInstanceParameter $$
我有 50 多個登錄名和大約 20 個要複製的角色。
我一直使用Script DB Level Permissions v3（由 S. Kusen 編寫）來完成您的要求。確保您在要從中獲取權限的數據庫上下文中執行：
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