SQL Server 服务器器信息备份（二）--用户权限备份
时间：2022-03-13 23:06
前言
上文说到：
本文将说到用户权限的备份，权限的备份可分为服务器级别的备份和用户级别的备份。
权限的备份可保证服务器在完全宕机的情况下，能快速通过备份恢复原有权限。笔者根据常用权限将权限分类如下
服务器级别权限
-
服务器级别角色：通过此语句拼写EXEC master..sp_addsrvrolemember @loginame , @rolename
-
服务器级别权限：
- 系统视图VIEW ANY DEFINITION查询的权限
- 元数据SERVER STATE查询的权限
- 端点权限等等
数据库级别权限
-
新建用户连接并赋予用户相应角色和特定表的增删改查的权限。
-
新建角色连接并赋予特定表的增删改查的权限等。
脚本(server)
服务级别权限脚本,在服务器维护之前,就应该将此权限赋予.笔者赋予的不只是服务器级别角色和一些元数据查询端点链接权限
，还包括系统数据库的数据库级别权限（用户的新建、角色新建以及特定对象的权限等）
CREATE PROC [dbo].[spm_getpermission_svr]
@loginame VARCHAR(100) = NULL,
@filepath VARCHAR(1000) = NULL
AS
SET nocount on
/*表变量用于存放数据*/
DECLARE @temp_file TABLE(
id INT IDENTITY ( 1 , 1 ),
sql_text VARCHAR(MAX)
)
DECLARE @cmd VARCHAR(MAX)
DECLARE @dbname VARCHAR(100)
IF @loginame IS NOT NULL
BEGIN
INSERT INTO @temp_file
(sql_text)
/*服务器级别角色*/
SELECT ‘EXEC master..sp_addsrvrolemember @loginame = N‘‘‘ + c.name + ‘‘‘ , @rolename = N‘‘‘ + a.name + ‘‘‘‘ COLLATE latin1_general_ci_as
FROM sys.server_principals a
JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id
JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id
WHERE c.TYPE IN (‘S‘,‘U‘,‘G‘)
AND c.name NOT LIKE ‘%SQLServer2005%‘
AND c.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
AND c.name = @loginame
UNION ALL
/*服务器级别权限*/
SELECT ‘USE master ;
‘ + a.state_desc + ‘ ‘ + a.permission_name + ‘ TO [‘ + b.name + ‘]‘ COLLATE latin1_general_ci_as
FROM sys.server_permissions a
JOIN sys.server_principals b
ON a.grantee_principal_id = b.principal_id
WHERE class_desc = ‘SERVER‘
AND b.TYPE IN (‘S‘,‘U‘,‘G‘)
AND a.permission_name <> ‘CONNECT SQL‘
AND b.name NOT LIKE ‘%SQLServer2005%‘
AND b.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
AND b.name = @loginame
UNION ALL
/*服务器级别主体权限*/
SELECT ‘USE master ;
‘ + pm.state_desc + ‘ ‘ + permission_name + ‘ ON LOGIN::[‘ + pc1.name + ‘] to [‘ + pc.name + ‘]‘ COLLATE latin1_general_ci_as
FROM sys.server_permissions pm (nolock)
JOIN sys.server_principals pc (nolock)
ON pm.grantee_principal_id = pc.principal_id
JOIN sys.server_principals pc1 (nolock)
ON pm.major_id = pc1.principal_id
WHERE pc.TYPE IN (‘S‘,‘U‘,‘G‘)
AND class_desc = ‘SERVER_PRINCIPAL‘
AND pc.name NOT LIKE ‘%SQLServer2005%‘
AND pc.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
AND pc.name = @loginame
UNION ALL
/*服务器级别端点权限*/
SELECT ‘USE master ;
‘ + pm.state_desc + ‘ ‘ + permission_name + ‘ ON ENDPOINT::[‘ + pc1.name + ‘] to [‘ + pc.name + ‘]‘ COLLATE latin1_general_ci_as
FROM sys.server_permissions pm (nolock)
JOIN sys.server_principals pc (nolock)
ON pm.grantee_principal_id = pc.principal_id
JOIN sys.server_principals pc1 (nolock)
ON pm.major_id = pc1.principal_id
WHERE pc.TYPE IN (‘S‘,‘U‘,‘G‘)
AND class_desc = ‘ENDPOINT‘
AND pc.name NOT LIKE ‘%SQLServer2005%‘
AND pc.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
AND pc.name = @loginame
END
ELSE
BEGIN
INSERT INTO @temp_file
(sql_text)
/*服务器级别角色*/
SELECT ‘EXEC master..sp_addsrvrolemember @loginame = N‘‘‘ + c.name + ‘‘‘ , @rolename = N‘‘‘ + a.name + ‘‘‘‘ COLLATE latin1_general_ci_as
FROM sys.server_principals a
JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id
JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id
WHERE c.TYPE IN (‘S‘,‘U‘,‘G‘)
AND c.name NOT LIKE ‘%SQLServer2005%‘
AND c.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
UNION ALL
/*服务器级别权限*/
SELECT ‘USE master ;
‘ + a.state_desc + ‘ ‘ + a.permission_name + ‘ TO [‘ + b.name + ‘]‘ COLLATE latin1_general_ci_as
FROM sys.server_permissions a
JOIN sys.server_principals b
ON a.grantee_principal_id = b.principal_id
WHERE class_desc = ‘SERVER‘
AND b.TYPE IN (‘S‘,‘U‘,‘G‘)
AND a.permission_name <> ‘CONNECT SQL‘
AND b.name NOT LIKE ‘%SQLServer2005%‘
AND b.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
UNION ALL
/*服务器级别主体权限*/
SELECT ‘USE master ;
‘ + pm.state_desc + ‘ ‘ + permission_name + ‘ ON LOGIN::[‘ + pc1.name + ‘] to [‘ + pc.name + ‘]‘ COLLATE latin1_general_ci_as
FROM sys.server_permissions pm (nolock)
JOIN sys.server_principals pc (nolock)
ON pm.grantee_principal_id = pc.principal_id
JOIN sys.server_principals pc1 (nolock)
ON pm.major_id = pc1.principal_id
WHERE pc.TYPE IN (‘S‘,‘U‘,‘G‘)
AND class_desc = ‘SERVER_PRINCIPAL‘
AND pc.name NOT LIKE ‘%SQLServer2005%‘
AND pc.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
UNION ALL
/*服务器级别端点权限*/
SELECT ‘USE master ;
‘ + pm.state_desc + ‘ ‘ + permission_name + ‘ ON ENDPOINT::[‘ + pc1.name + ‘] to [‘ + pc.name + ‘]‘ COLLATE latin1_general_ci_as
FROM sys.server_permissions pm (nolock)
JOIN sys.server_principals pc (nolock)
ON pm.grantee_principal_id = pc.principal_id
JOIN sys.server_principals pc1 (nolock)
ON pm.major_id = pc1.principal_id
WHERE pc.TYPE IN (‘S‘,‘U‘,‘G‘)
AND class_desc = ‘ENDPOINT‘
AND pc.name NOT LIKE ‘%SQLServer2005%‘
AND pc.name NOT IN (‘NT SERVICE\MSSQLSERVER‘,‘NT SERVICE\SQLSERVERAGENT‘,‘NT AUTHORITY\SYSTEM‘,‘##MS_PolicyEventProcessingLogin##‘,
‘##MS_PolicyTsqlExecutionLogin##‘,‘sa‘,‘BUILTIN\Administrators‘)
END
/*游标轮训系统数据库的权限*/
DECLARE db_table_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name IN (‘master‘,‘msdb‘,‘model‘,‘tempdb‘,
‘distribution‘)
AND state = 0
ORDER BY name
/*打开游标*/
OPEN db_table_cursor
FETCH NEXT FROM db_table_cursor
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @loginame IS NOT NULL
BEGIN
/*数据库用户新建*/
SELECT @cmd = ‘select
‘‘use ‘ + @dbname + ‘;
if exists (select top 1 1 from sys.schemas (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop schema [‘‘+dpr.name+‘‘];
if exists (select top 1 1 from sys.database_principals (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop user [‘‘+dpr.name+‘‘];
CREATE USER [‘‘+dpr.name+‘‘] FOR LOGIN [‘‘+l.name+‘‘] WITH DEFAULT_SCHEMA=[‘‘+dpr.default_schema_name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_principals dpr
join [‘ + @dbname + ‘].sys.syslogins l on dpr.sid =l.sid
where dpr.type in(‘‘S‘‘,‘‘U‘‘)
and dpr.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘) and l.name =‘‘‘ + @loginame + ‘‘‘
Union all
select
‘‘use ‘ + @dbname + ‘;
if exists (select top 1 1 from sys.schemas (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop schema [‘‘+dpr.name+‘‘];
if exists (select top 1 1 from sys.database_principals (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop user [‘‘+dpr.name+‘‘];
CREATE USER [‘‘+dpr.name+‘‘] FOR LOGIN [‘‘+l.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_principals dpr
join [‘ + @dbname + ‘].sys.syslogins l on dpr.sid =l.sid
where dpr.type in(‘‘G‘‘)
and dpr.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘) and l.name =‘‘‘ + @loginame + ‘‘‘‘
INSERT INTO @temp_file
(sql_text)
EXEC( @cmd)
/*用户与角色之间的关系*/
SELECT @cmd = ‘select ‘‘use ‘ + @dbname + ‘ ;
exec sp_addrolemember ‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘,‘‘‘‘‘‘+dpr1.name+‘‘‘‘‘‘‘‘ COLLATE LATIN1_General_CI_AS
from ‘ + @dbname + ‘.sys.database_role_members drm
join ‘ + @dbname + ‘.sys.database_principals dpr on drm.role_principal_id=dpr.principal_id
join ‘ + @dbname + ‘.sys.database_principals dpr1 on drm.member_principal_id=dpr1.principal_id
where dpr1.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and suser_sname(dpr1.sid) =‘‘‘ + @loginame + ‘‘‘‘
INSERT INTO @temp_file
(sql_text)
EXEC( @cmd)
/*拥有对象的权限*//*拥有对象字段的权限*//*数据库权限*/
SELECT @cmd = ‘select
‘‘USE [‘ + @dbname + ‘];
‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ ON ‘‘+s.name+‘‘.[‘‘+o.name+‘‘] TO [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.all_objects o (nolock)
join [‘ + @dbname + ‘].sys.database_permissions dp (nolock) on dp.major_id = o.object_id
join [‘ + @dbname + ‘].sys.schemas s (nolock) on o.schema_id = s.schema_id
join [‘ + @dbname + ‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_id
where du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) and dp.class_desc =‘‘OBJECT_OR_COLUMN‘‘ and minor_id=0--为对象
and du.name not in(
‘‘dbo‘‘,
‘‘##MS_PolicyEventProcessingLogin##‘‘,
‘‘##MS_AgentSigningCertificate##‘‘,
‘‘##MS_PolicyTsqlExecutionLogin##‘‘
) and suser_sname(du.sid)=‘‘‘ + @loginame + ‘‘‘
union all
select
‘‘USE [‘ + @dbname + ‘];
‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ ON ‘‘+s.name+‘‘.[‘‘+o.name+‘‘].[‘‘+o.name+‘‘] TO [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.all_objects o (nolock)
join [‘ + @dbname + ‘].sys.database_permissions dp (nolock) on dp.major_id = o.object_id--字段与对象的关系
join [‘ + @dbname + ‘].sys.schemas s (nolock) on o.schema_id = s.schema_id
join [‘ + @dbname + ‘].sys.all_columns c on c.object_id=o.object_id AND dp.minor_id=c.column_id --字段与权限的关系
join [‘ + @dbname + ‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_id
where du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) and dp.class_desc =‘‘OBJECT_OR_COLUMN‘‘ and minor_id=1--为字段
and du.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and suser_sname(du.sid)=‘‘‘ + @loginame + ‘‘‘
union all
select ‘‘use [‘ + @dbname + ‘] ;
‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ to [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
From [‘ + @dbname + ‘].sys.database_permissions dp (nolock)
join [‘ + @dbname + ‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_id
where dp.class_desc=‘‘DATABASE‘‘ and permission_name <>‘‘CONNECT‘‘
and du.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and suser_sname(du.sid)=‘‘‘ + @loginame + ‘‘‘‘
INSERT INTO @temp_file
(sql_text)
EXEC( @cmd)
/*用户拥有架构*//*用户拥有USER,role的权限*//*类型权限*/
SELECT @cmd = ‘
select ‘‘use [‘ + @dbname + ‘] ;
‘‘+dp.state_desc + ‘‘ ‘‘ + dp.permission_name + ‘‘ on SCHEMA::[‘‘+ s.name + ‘‘] to [‘‘ + du.name + ‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_permissions dp (nolock)
join [‘ + @dbname + ‘].sys.schemas s (nolock) on dp.major_id = s.schema_id
join [‘ + @dbname + ‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_id
where dp.class_desc=‘‘SCHEMA‘‘
and du.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘)
and suser_sname(du.sid)=‘‘‘ + @loginame + ‘‘‘
union all
select ‘‘use [‘ + @dbname + ‘] ;
‘‘+dpe.state_desc + ‘‘ ‘‘ + dpe.permission_name + ‘‘ on ‘‘+case when dpr.type=‘‘S‘‘ then ‘‘USER::[‘‘ else ‘‘ROLE::[‘‘ end + dpr.name + ‘‘] to [‘‘ + dpr1.name + ‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_permissions dpe
join [‘ + @dbname + ‘].sys.database_principals dpr on dpe.major_id=dpr.principal_id
join [‘ + @dbname + ‘].sys.database_principals dpr1 on dpe.grantee_principal_id=dpr1.principal_id
where dpr.type in(‘‘S‘‘,‘‘R‘‘) and dpe.class_desc=‘‘DATABASE_PRINCIPAL‘‘ and dpr1.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘)
and dpr1.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and suser_sname(dpr1.sid)=‘‘‘ + @loginame + ‘‘‘
union all
select ‘‘use [‘ + @dbname + ‘] ;
‘‘+dp.state_desc + ‘‘ ‘‘ + dp.permission_name + ‘‘ on TYPE::[‘‘+ s.name + ‘‘].[‘‘ + o.name + ‘‘] to [‘‘ + du.name + ‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_permissions dp (nolock)
join [‘ + @dbname + ‘].sys.types o (nolock) on dp.major_id = o.user_type_id
join [‘ + @dbname + ‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_id
join [‘ + @dbname + ‘].sys.schemas s (nolock) on o.schema_id = s.schema_id
where class_desc=‘‘TYPE‘‘
and du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘)
and du.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and suser_sname(du.sid)=‘‘‘ + @loginame + ‘‘‘
--and suser_sname(du.sid) is not null
‘
INSERT INTO @temp_file
(sql_text)
EXEC( @cmd)
END
/*******若不指定用户*****/
ELSE
BEGIN
/*数据库级别角色*/
SELECT @cmd = ‘select ‘‘use ‘ + @dbname + ‘;
if not exists (select top 1 1 from sys.database_principals where name=‘‘‘‘‘‘+dpe.name+‘‘‘‘‘‘)
create role [‘‘+dpe.name+‘‘] authorization [‘‘+dpr.default_schema_name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_principals dpe
join [‘ + @dbname + ‘].sys.database_principals dpr on dpe.owning_principal_id=dpr.principal_id
where dpe.is_fixed_role =0
and dpe.type=‘‘R‘‘
and dpe.name not in(
‘‘public‘‘,
‘‘TargetServersRole‘‘,
‘‘SQLAgentUserRole‘‘,
‘‘SQLAgentReaderRole‘‘,
‘‘SQLAgentOperatorRole‘‘,
‘‘DatabaseMailUserRole‘‘,
‘‘db_dtsadmin‘‘,
‘‘db_dtsltduser‘‘,
‘‘db_dtsoperator‘‘,
‘‘JOBUsers‘‘,
‘‘DTSUsers‘‘,
‘‘dbm_monitor‘‘,
‘‘replmonitor‘‘,
‘‘db_ssisadmin‘‘,
‘‘db_ssisltduser‘‘,
‘‘db_ssisoperator‘‘,
‘‘dc_operator‘‘,
‘‘dc_admin‘‘,
‘‘dc_proxy‘‘,
‘‘PolicyAdministratorRole‘‘,
‘‘ServerGroupAdministratorRole‘‘,
‘‘ServerGroupReaderRole‘‘,
‘‘UtilityCMRReader‘‘,
‘‘UtilityIMRWriter‘‘,
‘‘UtilityIMRReader‘‘
)‘
INSERT INTO @temp_file
(sql_text)
EXEC( @cmd)
/*数据库用户新建*/
SELECT @cmd = ‘select
‘‘use ‘ + @dbname + ‘;
if exists (select top 1 1 from sys.schemas (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop schema [‘‘+dpr.name+‘‘];
if exists (select top 1 1 from sys.database_principals (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop user [‘‘+dpr.name+‘‘];
CREATE USER [‘‘+dpr.name+‘‘] FOR LOGIN [‘‘+l.name+‘‘] WITH DEFAULT_SCHEMA=[‘‘+dpr.default_schema_name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_principals dpr
join [‘ + @dbname + ‘].sys.syslogins l on dpr.sid =l.sid
where dpr.type in(‘‘S‘‘,‘‘U‘‘)
and dpr.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
Union all
select
‘‘use ‘ + @dbname + ‘;
if exists (select top 1 1 from sys.schemas (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop schema [‘‘+dpr.name+‘‘];
if exists (select top 1 1 from sys.database_principals (nolock)
where name=‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘)
drop user [‘‘+dpr.name+‘‘];
CREATE USER [‘‘+dpr.name+‘‘] FOR LOGIN [‘‘+l.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.database_principals dpr
join [‘ + @dbname + ‘].sys.syslogins l on dpr.sid =l.sid
where dpr.type in(‘‘G‘‘)
and dpr.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and dpr.name not like ‘‘%SQLServer2005%‘‘‘
INSERT INTO @temp_file
(sql_text)
EXEC( @cmd)
/*用户与角色之间的关系*/
SELECT @cmd = ‘select ‘‘use ‘ + @dbname + ‘ ;
exec sp_addrolemember ‘‘‘‘‘‘+dpr.name+‘‘‘‘‘‘,‘‘‘‘‘‘+dpr1.name+‘‘‘‘‘‘‘‘ COLLATE LATIN1_General_CI_AS
from ‘ + @dbname + ‘.sys.database_role_members drm
join ‘ + @dbname + ‘.sys.database_principals dpr on drm.role_principal_id=dpr.principal_id
join ‘ + @dbname + ‘.sys.database_principals dpr1 on drm.member_principal_id=dpr1.principal_id
where dpr1.name not in(‘‘dbo‘‘,‘‘##MS_PolicyEventProcessingLogin##‘‘,‘‘##MS_AgentSigningCertificate##‘‘,‘‘##MS_PolicyTsqlExecutionLogin##‘‘)
and dpr1.name not like ‘‘%SQLServer2005%‘‘
and suser_sname(dpr1.sid) is not null‘
INSERT INTO @temp_file
(sql_text)
EXEC( @cmd)
/************角色拥有权限***************************/
/*拥有对象的权限*//*拥有对象字段的权限*//*数据库权限*/
SELECT @cmd = ‘select
‘‘USE [‘ + @dbname + ‘];
‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ ON ‘‘+s.name+‘‘.[‘‘+o.name+‘‘] TO [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.all_objects o (nolock)
join [‘ + @dbname + ‘].sys.database_permissions dp (nolock) on dp.major_id = o.object_id
join [‘ + @dbname + ‘].sys.schemas s (nolock) on o.schema_id = s.schema_id
join [‘ + @dbname + ‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_id
where du.type in (‘‘R‘‘) and dp.class_desc =‘‘OBJECT_OR_COLUMN‘‘ and minor_id=0--为对象
and du.name not in(
‘‘public‘‘,
‘‘TargetServersRole‘‘,
‘‘SQLAgentUserRole‘‘,
‘‘SQLAgentReaderRole‘‘,
‘‘SQLAgentOperatorRole‘‘,
‘‘DatabaseMailUserRole‘‘,
‘‘db_dtsadmin‘‘,
‘‘db_dtsltduser‘‘,
‘‘db_dtsoperator‘‘,
‘‘JOBUsers‘‘,
‘‘DTSUsers‘‘,
‘‘dbm_monitor‘‘,
‘‘replmonitor‘‘,
‘‘db_ssisadmin‘‘,
‘‘db_ssisltduser‘‘,
‘‘db_ssisoperator‘‘,
‘‘dc_operator‘‘,
‘‘dc_admin‘‘,
‘‘dc_proxy‘‘,
‘‘PolicyAdministratorRole‘‘,
‘‘ServerGroupAdministratorRole‘‘,
‘‘ServerGroupReaderRole‘‘,
‘‘UtilityCMRReader‘‘,
‘‘UtilityIMRWriter‘‘,
‘‘UtilityIMRReader‘‘
)
union all
select
‘‘USE [‘ + @dbname + ‘];
‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ ON ‘‘+s.name+‘‘.[‘‘+o.name+‘‘].[‘‘+o.name+‘‘] TO [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
from [‘ + @dbname + ‘].sys.all_objects o (nolock)
join [‘ + @dbname + ‘].sys.database_permissions dp (nolock) on dp.major_id = o.object_id--字段与对象的关系
join [‘ + @dbname + ‘].sys.schemas s (nolock) on o.schema_id = s.schema_id
join [‘ + @dbname + ‘].sys.all_columns c on c.object_id=o.object_id AND dp.minor_id=c.column_id --字段与权限的关系
join [‘ + @dbname + ‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_id
where du.type in (‘‘R‘‘) and dp.class_desc =‘‘OBJECT_OR_COLUMN‘‘ and minor_id=1--为字段
and du.name not in(
‘‘public‘‘,
‘‘TargetServersRole‘‘,
‘‘SQLAgentUserRole‘‘,
‘‘SQLAgentReaderRole‘‘,
‘‘SQLAgentOperatorRole‘‘,
‘‘DatabaseMailUserRole‘‘,
‘‘db_dtsadmin‘‘,
‘‘db_dtsltduser‘‘,
‘‘db_dtsoperator‘‘,
‘‘JOBUsers‘‘,
‘‘DTSUsers‘‘,
‘‘dbm_monitor‘‘,
‘‘replmonitor‘‘,
‘‘db_ssisadmin‘‘,
‘‘db_ssisltduser‘‘,
‘‘db_ssisoperator‘‘,
‘‘dc_operator‘‘,
‘‘dc_admin‘‘,
‘‘dc_proxy‘‘,
‘‘PolicyAdministratorRole‘‘,
‘‘ServerGroupAdministratorRole‘‘,
‘‘ServerGroupReaderRole‘‘,
‘‘UtilityCMRReader‘‘,
‘‘UtilityIMRWriter‘‘,
‘‘UtilityIMRReader‘‘
)
union all
select ‘‘use [‘ + @dbname + ‘] ;
‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ to [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_AS
From [‘ + @dbname + ‘].sys.database_permissions dp (nolock)
热门排行
今日推荐
-
松滋水利官方版
版本：v1.0.23
大小：25.6M
日期：2024-10-01
-
开发者联盟完整版
版本：v12.8.1.301
大小：22.9M
日期：2024-10-01
-
360视觉云平台官方版
版本：v1.4.0
大小：158.9M
日期：2024-10-01
-
考驾照帮手官方版
版本：v1.0.0
大小：36.5M
日期：2024-10-01
-
帮对作业官方版
版本：v2.0.2
大小：16.3M
日期：2024-10-01
-
笔趣阁免费全本小说在线阅读版
版本：1.0
大小：18.83MB
日期：2024-10-01
热门手游
-
梦幻西游正版
版本：v1.404.0
大小：1.90G
日期：2024-10-01
-
侠义九州爆裂GM魂环破解版
版本：v1.0.35
大小：365.5M
日期：2024-10-01
-
街机之三国战记高返免费版
版本：v1.0
大小：30.7M
日期：2024-10-01
-
暮影战神官方版
版本：v27.2.2
大小：356.2M
日期：2024-10-01
-
器灵宝塔新版
版本：v2.0.5
大小：125M
日期：2024-10-01
-
创意蛋糕店正式版
版本：1.0.0
大小：37.72MB
日期：2024-10-01