sqlserver使用脚本迁移login和user的权限

2022-11-16 14:49 abce 阅读(209) 评论(0) 编辑收藏举报

原文地址：http://udayarumilli.com/script-login-user-permissions-sql-server/

1.实例级别的权限

--https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
/********************************************************************************************************************/
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
/********************************************************************************************************************/
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
               CASE
                    WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '
                        + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
                    ELSE ' FROM WINDOWS WITH'
                END
       +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
        ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa');
 
-- Scripting Out the Role Membership to Be Added
SELECT
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
    JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
    JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
        AND SL.name NOT LIKE '##%##'
        AND SL.name NOT LIKE 'NT AUTHORITY%'
        AND SL.name NOT LIKE 'NT SERVICE%'
        AND SL.name <> ('sa');
 
 
-- Scripting out the Permissions to Be Granted
SELECT
    CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
        THEN SrvPerm.state_desc 
        ELSE 'GRANT'
    END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
    CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
        THEN ''
        ELSE ' WITH GRANT OPTION'
    END collate database_default AS [-- Server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
    JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa');
 
SET NOCOUNT OFF

2.数据库/数据库对象级别的权限

USE Master  -- Use the required database name here
GO
SET NOCOUNT ON;
 
PRINT 'USE ['+DB_NAME()+']';
PRINT 'GO'
 
/********************************************************************************/
/**************** Create a new user and map it with login ***********************/
/********************************************************************************/
 
PRINT '/*************************************************************/'
PRINT '/************** Create User Script ***************************/'
PRINT '/*************************************************************/'
 
SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']'
FROM sys.database_principals
WHERE   [Type] IN ('U','S')
        AND
        [NAME] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')
 
GO
-- Troubleshooting User creation issues
PRINT '/***'+CHAR(10)+
'--Error 15023: User or role <XXXX> is already exists in the database.'+CHAR(10)+
'--Then Execute the below code can fix the issue'+CHAR(10)+
'EXEC sp_change_users_login ''Auto_Fix'',''<Failed User>'''+CHAR(10)+
'GO **/'
 
/************************************************************************/
/************  Script the User Role Information *************************/
/************************************************************************/
 
PRINT '/**********************************************************/'
PRINT '/************** Create User-Role Script *******************/'
PRINT '/**********************************************************/'
 
SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBP.NAME + ''''
FROM sys.database_principals DBP
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBP.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id
WHERE DBP.NAME <> 'dbo'
 
GO
 
/***************************************************************************/
/************  Script Database Level Permission ****************************/
/***************************************************************************/
 
PRINT '/*************************************************************/'
PRINT '/************** Database Level Permission ********************/'
PRINT '/*************************************************************/'
 
SELECT  CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
        + SPACE(1) + DBP.permission_name + SPACE(1)
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
        + CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';'
FROM    sys.database_permissions AS DBP
        INNER JOIN  sys.database_principals AS USR  ON DBP.grantee_principal_id = USR.principal_id
WHERE   DBP.major_id = 0 and USR.name <> 'dbo'
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC
 
 
/***************************************************************************/
/************  Script Object Level Permission ******************************/
/***************************************************************************/
 
PRINT '/*************************************************************/'
PRINT '/************** Object Level Permission **********************/'
PRINT '/*************************************************************/'
 
SELECT  CASE WHEN DBP.state <> 'W' THEN DBP.state_desc ELSE 'GRANT' END
        + SPACE(1) + DBP.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(OBJ.schema_id)) + '.' + QUOTENAME(OBJ.name) 
        + CASE WHEN CL.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(CL.name) + ')' END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USR.name) COLLATE database_default
        + CASE WHEN DBP.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';'
FROM    sys.database_permissions AS DBP
        INNER JOIN  sys.objects AS OBJ  ON DBP.major_id = OBJ.[object_id]
        INNER JOIN  sys.database_principals AS USR  ON DBP.grantee_principal_id = USR.principal_id
        LEFT JOIN   sys.columns AS CL   ON CL.column_id = DBP.minor_id AND CL.[object_id] = DBP.major_id
ORDER BY DBP.permission_name ASC, DBP.state_desc ASC
 
 
 
SET NOCOUNT OFF;

其他：权限查看

--服务器级权限
WITH CTE AS
(
SELECT u.name AS 用户名,
u.is_disabled AS 是否禁用,
g.name as 服务器角色,
'√' as 'flag'
FROM sys.server_principals u
INNER JOIN sys.server_role_members m ON u.principal_id = m.member_principal_id
INNER JOIN sys.server_principals g ON g.principal_id = m.role_principal_id
)
SELECT * FROM CTE PIVOT(MAX(flag) FOR 服务器角色 IN ([public],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin])) AS T
 
--数据库级权限
WITH CTE AS
(
SELECT u.name AS 用户名,
g.name AS 数据库角色,
'√' as 'flag'
FROM sys.database_principals u
INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id
)
SELECT * FROM CTE PIVOT(MAX(flag) FOR 数据库角色 IN ([public],
[db_owner],
[db_accessadmin],
[db_securityadmin],
[db_ddladmin],
[db_backupoperator],
[db_datareader],
[db_datawriter],
[db_denydatareader],
[db_denydatawriter])) AS T
 
--数据库级单独权限
select c.name as 用户名,b.name as 对象名,
CASE b.type
WHEN 'U' THEN 'Table'
WHEN 'P' THEN 'Procedure'
ELSE 'OTHER'
END AS 对象类型,
CASE WHEN a.ACTION = 26 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'REFERENCES',
CASE WHEN a.ACTION = 193 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'SELECT',
CASE WHEN a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'INSERT',
CASE WHEN a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'UPDATE',
CASE WHEN a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'DELETE',
CASE WHEN a.ACTION = 224 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'EXECUTE',
CASE a.PROTECTTYPE
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
ELSE 'OTHER'
END AS PROTECTTYPE
from sysprotects a inner join sysobjects b on a.id = b.id
inner join sysusers c on a.uid = c.uid