SQL Server 服务器器信息备份(二)--用户权限备份

前言

上文说到:SQL Server 服务器器信息备份(一)--login新建脚本备份

本文将说到用户权限的备份,权限的备份可分为服务器级别的备份和用户级别的备份。

权限的备份可保证服务器在完全宕机的情况下,能快速通过备份恢复原有权限。笔者根据常用权限将权限分类如下

服务器级别权限

  • 服务器级别角色:通过此语句拼写EXEC master..sp_addsrvrolemember @loginame , @rolename

  • 服务器级别权限:

  1. 系统视图VIEW ANY DEFINITION查询的权限
  2. 元数据SERVER STATE查询的权限
  3. 端点权限等等

数据库级别权限

  • 新建用户连接并赋予用户相应角色和特定表的增删改查的权限。

  • 新建角色连接并赋予特定表的增删改查的权限等。

脚本(server)

服务级别权限脚本,在服务器维护之前,就应该将此权限赋予.笔者赋予的不只是服务器级别角色和一些元数据查询端点链接权限

,还包括系统数据库的数据库级别权限(用户的新建、角色新建以及特定对象的权限等)

SQL 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)
                                                                                                                                                                                                                                                                                                                       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
View Code

脚本(user)

 此处数据库级别的权限,则自然包括用户数据库的权限和角色的权限;可将常用权限导出,并不能导出所有权限。

SQL Server 服务器器信息备份(二)--用户权限备份
create proc [dbo].[spm_GetPermission_user] 
  @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)

  
/*游标轮训系统数据库的权限*/
declare   db_table_cursor cursor for 
select name from sys.databases
where name not 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 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  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  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 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 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 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.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 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 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
View Code

 

SQL Server 服务器器信息备份(二)--用户权限备份

上一篇:Android ADB命令大全(通过ADB命令查看wifi密码、MAC地址、设备信息、操作文件、查看文件、日志信息、卸载、启动和安装APK等)


下一篇:Android命令之-------ADB命令大全