首页 > 代码库 > SQL Server 服务器器信息备份（二）--用户权限备份
SQL Server 服务器器信息备份（二）--用户权限备份
前言
上文说到：SQL Server 服务器器信息备份（一）--login新建脚本备份
本文将说到用户权限的备份，权限的备份可分为服务器级别的备份和用户级别的备份。
权限的备份可保证服务器在完全宕机的情况下，能快速通过备份恢复原有权限。笔者根据常用权限将权限分类如下
服务器级别权限
服务器级别角色：通过此语句拼写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) = NULLAS 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) 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.type in (‘‘R‘‘) 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‘‘ ) ‘ 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( ‘‘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‘‘ ) and du.type in (‘‘R‘‘) 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 (‘‘R‘‘) and dpr1.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 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 (‘‘R‘‘) 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‘‘ ) --and suser_sname(du.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 (‘‘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 du.name not like ‘‘%SQLServer2005%‘‘ 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 du.name not like ‘‘%SQLServer2005%‘‘ 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 du.name not like ‘‘%SQLServer2005%‘‘ ‘ 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.name not like ‘‘%SQLServer2005%‘‘ and du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) 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 dpr1.name not like ‘‘%SQLServer2005%‘‘ 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 du.name not like ‘‘%SQLServer2005%‘‘ --and suser_sname(du.sid) is not null ‘ --print (@cmd) INSERT INTO @temp_file (sql_text) EXEC( @cmd) END /*轮训下一个数据库*/ FETCH NEXT FROM db_table_cursor INTO @dbname END /*关闭和删除游标*/ CLOSE db_table_cursor DEALLOCATE db_table_cursor IF @filepath IS NULL SELECT sql_text FROM @temp_file ORDER BY id ELSE --将结果导出到@bcpfilename文件中 BEGIN IF Object_id(‘tempdb..##tb_result_SQLPermissions_user‘) IS NOT NULL DROP TABLE ##tb_result_sqlpermissions_user CREATE TABLE ##tb_result_sqlpermissions_user ( id INT, sql_text VARCHAR(MAX)) INSERT INTO ##tb_result_sqlpermissions_user SELECT id, sql_text FROM @temp_file ORDER BY id SELECT @cmd = ‘master..xp_cmdshell ‘‘bcp "select sql_text from ##tb_result_SQLPermissions_user order by id" queryout ‘ + @filepath + ‘ -T -c -S‘ + @@SERVERNAME + ‘‘‘‘ EXEC( @cmd) DROP TABLE ##tb_result_sqlpermissions_user END
脚本(user)
此处数据库级别的权限，则自然包括用户数据库的权限和角色的权限；可将常用权限导出，并不能导出所有权限。
create proc [dbo].[spm_GetPermission_user] @loginame varchar(100)=null, @filepath varchar(1000)=nullasset nocount on/*表变量用于存放数据*/declare @temp_file table (id int identity(1,1) ,sql_text varchar(max))declare @cmd varchar(max)declare @dbname varchar(100) /*游标轮训系统数据库的权限*/declare db_table_cursor cursor for select name from sys.databaseswhere name not in (‘master‘,‘msdb‘,‘model‘,‘tempdb‘,‘distribution‘) and state=0order by name /*打开游标*/open db_table_cursorfetch next from db_table_cursor into @dbnameWHILE @@FETCH_STATUS = 0beginif @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_ASfrom [‘+@dbname+‘].sys.database_principals dpr join [‘+@dbname+‘].sys.syslogins l on dpr.sid =l.sidwhere dpr.type in(‘‘S‘‘,‘‘U‘‘) and l.name =‘‘‘+@loginame+‘‘‘Union allselect ‘‘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_ASfrom [‘+@dbname+‘].sys.database_principals dpr join [‘+@dbname+‘].sys.syslogins l on dpr.sid =l.sidwhere dpr.type in(‘‘G‘‘) 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_ASfrom [‘+@dbname+‘].sys.database_role_members drmjoin [‘+@dbname+‘].sys.database_principals dpr on drm.role_principal_id=dpr.principal_idjoin [‘+@dbname+‘].sys.database_principals dpr1 on drm.member_principal_id=dpr1.principal_idwhere 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_ASfrom [‘+@dbname+‘].sys.all_objects o (nolock)join [‘+@dbname+‘].sys.database_permissions dp (nolock) on dp.major_id = o.object_idjoin [‘+@dbname+‘].sys.schemas s (nolock) on o.schema_id = s.schema_idjoin [‘+@dbname+‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_idwhere du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) and dp.class_desc =‘‘OBJECT_OR_COLUMN‘‘ and minor_id=0--为对象 and suser_sname(du.sid)=‘‘‘+@loginame+‘‘‘union allselect ‘‘USE [‘+@dbname+‘];‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ ON ‘‘+s.name+‘‘.[‘‘+o.name+‘‘].[‘‘+o.name+‘‘] TO [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_ASfrom [‘+@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_idjoin [‘+@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_idwhere du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) and dp.class_desc =‘‘OBJECT_OR_COLUMN‘‘ and minor_id=1--为字段and suser_sname(du.sid)=‘‘‘+@loginame+‘‘‘union allselect ‘‘use [‘+@dbname+‘] ; ‘‘+dp.state_desc+‘‘ ‘‘+dp.permission_name+‘‘ to [‘‘+du.name+‘‘]‘‘ COLLATE LATIN1_General_CI_ASFrom [‘+@dbname+‘].sys.database_permissions dp (nolock)join [‘+@dbname+‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_idwhere dp.class_desc=‘‘DATABASE‘‘ and permission_name <>‘‘CONNECT‘‘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_ASfrom [‘+@dbname+‘].sys.database_permissions dp (nolock)join [‘+@dbname+‘].sys.schemas s (nolock) on dp.major_id = s.schema_idjoin [‘+@dbname+‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_idwhere dp.class_desc=‘‘SCHEMA‘‘and du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) and suser_sname(du.sid)=‘‘‘+@loginame+‘‘‘union allselect ‘‘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_ASfrom [‘+@dbname+‘].sys.database_permissions dpejoin [‘+@dbname+‘].sys.database_principals dpr on dpe.major_id=dpr.principal_idjoin [‘+@dbname+‘].sys.database_principals dpr1 on dpe.grantee_principal_id=dpr1.principal_idwhere dpr.type in(‘‘S‘‘,‘‘R‘‘) and dpe.class_desc=‘‘DATABASE_PRINCIPAL‘‘ and dpr1.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘)and suser_sname(dpr1.sid)=‘‘‘+@loginame+‘‘‘union allselect ‘‘use [‘+@dbname+‘] ;‘‘+dp.state_desc + ‘‘ ‘‘ + dp.permission_name + ‘‘ on TYPE::[‘‘+ s.name + ‘‘].[‘‘ + o.name + ‘‘] to [‘‘ + du.name + ‘‘]‘‘ COLLATE LATIN1_General_CI_ASfrom [‘+@dbname+‘].sys.database_permissions dp (nolock) join [‘+@dbname+‘].sys.types o (nolock) on dp.major_id = o.user_type_idjoin [‘+@dbname+‘].sys.database_principals du (nolock) on dp.grantee_principal_id = du.principal_idjoin [‘+@dbname+‘].sys.schemas s (nolock) on o.schema_id = s.schema_idwhere class_desc=‘‘TYPE‘‘ and du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) and suser_sname(du.sid)=‘‘‘+@loginame+‘‘‘--and suser_sname(du.sid) is not null‘insert into @temp_file(sql_text)exec (@cmd)end /*******若不指定用户*****/elsebegin /*数据库级别角色*/ 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) 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.type in (‘‘R‘‘) 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‘‘ ) ‘ 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( ‘‘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‘‘ ) and du.type in (‘‘R‘‘) 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 (‘‘R‘‘) and dpr1.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 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 (‘‘R‘‘) 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‘‘ ) --and suser_sname(du.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 (‘‘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 du.name not like ‘‘%SQLServer2005%‘‘ 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 du.name not like ‘‘%SQLServer2005%‘‘ 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 du.name not like ‘‘%SQLServer2005%‘‘ ‘ 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.name not like ‘‘%SQLServer2005%‘‘ and du.type in (‘‘S‘‘,‘‘U‘‘,‘‘G‘‘) 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 dpr1.name not like ‘‘%SQLServer2005%‘‘ 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 du.name not like ‘‘%SQLServer2005%‘‘ --and suser_sname(du.sid) is not null ‘ --print (@cmd) insert into @temp_file(sql_text) exec (@cmd)end/*轮训下一个数据库*/fetch next from db_table_cursor into @dbnameend/*关闭和删除游标*/CLOSE db_table_cursorDEALLOCATE db_table_cursorif @filepath is null select sql_text from @temp_file order by idelse --将结果导出到@bcpfilename文件中 begin if object_id(‘tempdb..##tb_result_SQLPermissions_user‘) is not null drop table ##tb_result_SQLPermissions_user create table ##tb_result_SQLPermissions_user(id int ,sql_text varchar(max)) insert into ##tb_result_SQLPermissions_user select id,sql_text from @temp_file order by id select @cmd=‘master..xp_cmdshell ‘‘bcp "select sql_text from ##tb_result_SQLPermissions_user order by id" queryout ‘+@filepath+‘ -T -c -S‘+@@servername+‘‘‘‘ exec (@cmd) drop table ##tb_result_SQLPermissions_userend
SQL Server 服务器器信息备份（二）--用户权限备份
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。