克隆SQL Server 用户及权限

最新推荐文章于 2020-07-22 10:06:09 发布

三空道人

最新推荐文章于 2020-07-22 10:06:09 发布

阅读量1.3k

点赞数

分类专栏： SQL Server 可用性 SQL Server SQL Server 安全管理文章标签： create user GRANT sp_addrolemember create database deny

本文链接：https://blog.csdn.net/zhoujunah/article/details/103786896

版权

SQL Server 同时被 3 个专栏收录

63 篇文章 11 订阅

订阅专栏

SQL Server 安全管理

41 篇文章 3 订阅

订阅专栏

SQL Server 可用性

27 篇文章 3 订阅

订阅专栏

《克隆 SQL Server 登陆名及权限》、《SQL Server 登陆账户权限克隆——sp_DBA_LoginClone 升级版》两文介绍了如何生成一个账户的创建脚本及权限赋予脚本，《SQL Server 登录账户权限异步同步》给出了登陆账户及权限克隆的一个应用。通过自动化管理，极大的减轻了DBA的负担，同时也降低了管理时间成本。接下来的脚本给出了数据库用户（包括包含用户）、数据库角色、应用角色的创建和权限赋予脚本的生成。

USE [master]
GO
/*
      功能:克隆数据库中用户、角色应用角色创建脚本及权限赋予脚本
      样例：生成用户Jack 的创建脚本、权限赋予脚本
      exec sp_DBA_userPermisionsClone test,Jack
     
      样例：按照用户Jack模型克隆用户Jack_new
      exec sp_DBA_userPermisionsClone test,Jack,Jack_new
     
      样例：按照包含用户Jack模型克隆包含用户Jack_new
      exec sp_DBA_userPermisionsClone @dbName=test,@userName=Jack,@newUserName=Jack_new,@pwd='Password'
     
      样例：生成数据库角色的创建脚本及权限赋脚本
      exec sp_DBA_userPermisionsClone @dbName=test,@userName=testRoleIntest
     
      样例：生成应用角色的创建脚本及权限赋予脚本
      exec sp_DBA_userPermisionsClone @dbName=test,@userName=AppRole
      用户类型type
            A = 应用程序角色
            C = 映射到证书的用户
            G = Windows 组
            K = 映射到非对称密钥的用户
            R = 数据库角色
            S = SQL 用户
            U = Windows 用户
     
     
      授权类型authentication_type
            适用于：SQL Server 2012 (11.x) 及更高版本。
            指示身份验证类型。下面是可能的值及其说明。
            0：无身份验证
            1：实例身份验证
            2：数据库身份验证
            3：Windows 身份验证
            对应的authentication_type_desc
            NONE : 0
            INSTANCE : 1
            DATABASE : 2
            WINDOWS : 3
 
      数据库对象包括如下:
      --0 = 数据库
      --1 = 对象或列
      --3 = 架构
      --4 = 数据库主体
      --5 = 程序集适用范围:SQL Server 2008通过SQL Server 2019。
      --6 = 类型
      --10 = XML 架构集合的适用范围：SQL Server 2008 到SQL Server 2019。
      --15 = 消息类型适用范围:SQL Server 2008通过SQL Server 2019。
      --16 = 服务约定适用范围:SQL Server 2008通过SQL Server 2019。
      --17 = 服务适用范围:SQL Server 2008通过SQL Server 2019。
      --18 = 远程服务绑定适用范围:SQL Server 2008通过SQL Server 2019。
      --19 = 的路由适用范围:SQL Server 2008通过SQL Server 2019。
      --23 = 全文目录适用范围:SQL Server 2008通过SQL Server 2019。
      --24 = 对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
      --25 = 证书适用范围:SQL Server 2008通过SQL Server 2019。
      --26 = 非对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
     
*/
CREATE PROC [dbo].[sp_DBA_userPermisionsClone]
      @dbName sysname
      ,@userName sysname
      ,@newUserName sysname=NULL
      ,@newLoginName sysname=NULL
      ,@pwd VARCHAR(25) =NULL
AS
BEGIN
     
      /*
            default_language_name   sysname    
            适用于：SQL Server 2012 (11.x) 及更高版本。
            指示此主体的默认语言。
           
            default_language_lcid   int  
            适用于：SQL Server 2012 (11.x) 及更高版本。
            指示此主体的默认LCID。
           
            allow_encrypted_value_modifications bit  
            适用于：SQL Server 2016 (13.x) 及更高版本、SQL 数据库。
            取消在大容量复制操作期间对服务器进行加密元数据检查。
            这样，用户便可以使用Always Encrypted、在表或数据库之间对数据进行大容量复制，而无需解密数据。默认为OFF。
      */
      SET NOCOUNT ON
      DECLARE @type char(1)
            ,@authType INT    --授权类型
            ,@sid varbinary(85)
            ,@default_language_lcid INT
            ,@allow_encrypted_value_modifications BIT
            ,@defSchemaName sysname
      DECLARE @versionNum AS INT    --SQL Server 的版本号
    SET @versionNum = SUBSTRING(@@VERSION, 22, 4)
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @j INT= NULL
            --生成创建数据库用户脚本
      IF @versionNum >= 2012
      BEGIN
            SET @sql = N'select @i=1,@type=type
                  ,@authType=authentication_type,@sid=sid
                  ,@default_language_lcid=default_language_lcid
                  ,@defSchemaName=default_schema_name from '
                  + QUOTENAME(@dbName,']') + N'.sys.database_principals
                  where name=''' + @userName + ''''
            EXEC sp_executesql @sql
                  , N'@i int output
                        ,@type char(1) output
                        ,@authType int output
                        ,@sid varbinary(85) output
                        ,@default_language_lcid INT output
                        ,@defSchemaName sysname output'
                  ,@i = @j OUTPUT
                  ,@type=@type OUTPUT
                  ,@authType=@authType OUTPUT
                  ,@sid=@sid OUTPUT
                  ,@default_language_lcid=@default_language_lcid OUTPUT
                  ,@defSchemaName=@defSchemaName OUTPUT
      END
      ELSE IF @versionNum >= 2016
      BEGIN
            SET @sql = N'select @i=1,@type=type
                  ,@authType=authentication_type,@sid=sid
                  ,@default_language_lcid=default_language_lcid
                  ,@allow_encrypted_value_modifications=allow_encrypted_value_modifications
                  ,@defSchemaName=default_schema_name from '
                  + QUOTENAME(@dbName,']') + N'.sys.database_principals
                  where name=''' + @userName + ''''
            EXEC sp_executesql @sql
                  , N'@i int output
                        ,@type char(1) output
                        ,@authType int output
                        ,@sid varbinary(85) output
                        ,@default_language_lcid INT output
                        ,@allow_encrypted_value_modifications BIT output
                        ,@defSchemaName sysname output'
                  ,@i = @j OUTPUT
                  ,@type=@type OUTPUT
                  ,@authType=@authType OUTPUT
                  ,@sid=@sid OUTPUT
                  ,@default_language_lcid=@default_language_lcid OUTPUT
                  ,@allow_encrypted_value_modifications=@allow_encrypted_value_modifications OUTPUT
                  ,@defSchemaName=@defSchemaName OUTPUT
      END
    ELSE
    begin
            SET @sql = N'select @i=1,@type=type,@sid=sid
                  ,@defSchemaName=default_schema_name from '
                  + QUOTENAME(@dbName,']') + N'.sys.database_principals
                  where name=''' + @userName + ''''
            EXEC sp_executesql @sql
                  , N'@i int output
                        ,@type char(1) output
                        ,@sid varbinary(85) output
                        ,@defSchemaName sysname output'
                  ,@i = @j OUTPUT
                  ,@type=@type OUTPUT
                  ,@sid=@sid OUTPUT
                  ,@defSchemaName=@defSchemaName OUTPUT
      END
    IF ( @j IS NULL )   --数据库不存在用户
        BEGIN
            PRINT '数据库'+@dbName+ ' 中不存在用户'+@userName
            RETURN
        END
    IF ISNULL(@authType,0)=2 AND @pwd IS NULL AND @newUserName IS NOT NULL
    BEGIN
            PRINT '创建新包含用户'+@userName+ ' 需要密码'
        RETURN
    END
      IF @newUserName IS NULL
            SET @newUserName=@userName
      IF @newLoginName IS NULL AND ISNULL(@authType,0)<>2
            SET @newLoginName=@userName
      PRINT 'USE ' + QUOTENAME(@dbName,']')
    PRINT 'GO'
    DECLARE @allowEVMDesc VARCHAR(3)
    SELECT @allowEVMDesc=CASE @allow_encrypted_value_modifications WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' ELSE '' END
 
      IF ISNULL(@authType,0)=2      --包含用户
      BEGIN
            PRINT 'CREATE USER [' + @newUserName + '] WITH PASSWORD ='+''''+ISNULL(@pwd,'')+''''
                  +',DEFAULT_SCHEMA ='+QUOTENAME(@defSchemaName,']')
                  +CASE WHEN @default_language_lcid IS NULL THEN '' ELSE ',DEFAULT_LANGUAGE = '+CONVERT(VARCHAR(10),@default_language_lcid) END
                  +CASE WHEN @userName=@newUserName or @newUserName IS NULL THEN ',SID = '+CONVERT(VARCHAR(85),@sid,1) ELSE '' END
                  +CASE WHEN @allow_encrypted_value_modifications IS NOT NULL
                        THEN ',ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = '+@allowEVMDesc ELSE '' END
            PRINT 'GO'
            --DEFAULT_SCHEMA = schema_name
              --  | DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }
              --  | SID = sid 
              --  | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]
      END  
      ELSE IF ISNULL(@authType,0)<>2 AND @type='S'
      begin
            PRINT 'CREATE USER [' + @newUserName + '] FOR LOGIN [' + @newLoginName + '] '
                  +'WITH DEFAULT_SCHEMA ='+QUOTENAME(@defSchemaName,']')
                  +CASE WHEN @allow_encrypted_value_modifications IS NOT NULL
                        THEN ',ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = '+@allowEVMDesc ELSE '' END
            PRINT 'GO'
      END
      ELSE IF  @type IN('U','G')    --Windows/Windows 组用户
      BEGIN
            IF EXISTS (SELECT 1 FROM sys.server_principals
                        WHERE name=@UserName)
            BEGIN
                  PRINT 'CREATE USER [' + @newUserName + '] FOR LOGIN [' + @newLoginName + ']'
                        +'WITH DEFAULT_SCHEMA ='+QUOTENAME(@defSchemaName,']')
                        +CASE WHEN @allow_encrypted_value_modifications IS NOT NULL
                              THEN ',ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = '+@allowEVMDesc ELSE '' END
                  PRINT 'GO'
            END
            ELSE
            BEGIN
                  PRINT 'CREATE USER [' + @newUserName + ']'
                        +'WITH DEFAULT_SCHEMA ='+QUOTENAME(@defSchemaName,']')
                        +CASE WHEN @default_language_lcid IS NULL THEN '' ELSE ',DEFAULT_LANGUAGE = '+CONVERT(VARCHAR(10),@default_language_lcid) END
                        +',SID = '+CONVERT(VARCHAR(85),@sid,1)
                        +CASE WHEN @allow_encrypted_value_modifications IS NOT NULL
                              THEN ',ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = '+@allowEVMDesc ELSE '' END
                  PRINT 'GO'
            END
    END
    ELSE IF @type='C'
    BEGIN
            PRINT '--映射到证书的用户'
            PRINT 'CREATE USER '+QUOTENAME(@newUserName,'] ')+'FOR      CERTIFICATE '+''''''
            PRINT 'GO'
      END
      ELSE IF @type='K'
      BEGIN
            PRINT '--映射到非对称密钥的用户'
            PRINT 'CREATE USER '+ QUOTENAME(@newUserName,'] ')+'FROM ASYMMETRIC KEY '+ ''''''
            PRINT 'GO'
      END
      ELSE IF @type='R'
      BEGIN
            PRINT '--数据库角色克隆'
            PRINT 'CREATE ROLE '+ QUOTENAME(@newUserName,']')
      END
      ELSE IF @type='A'
      BEGIN
            PRINT '--应用角色克隆'
            PRINT 'CREATE APPLICATION ROLE '+ QUOTENAME(@newUserName,']')
                  +'WITH PASSWORD = '''+ISNULL(@pwd,'')+'''  , DEFAULT_SCHEMA = '+QUOTENAME(@defSchemaName,']')
      END
--赋予用户数据库权限
    IF OBJECT_ID('tempdb..#privileges', N'U') IS NOT NULL
        DROP TABLE #privileges
    CREATE TABLE #privileges
        (
         permission_name VARCHAR(100)
        ,[state] CHAR(1)
        )
    INSERT  INTO #privileges
            EXEC
                ( 'select p.permission_name,p.state from ['
                  + @dbName
                  + '].sys.database_permissions p left join ['
                  + @dbName
                  + '].sys.database_principals pp on p.grantee_principal_id=pp.principal_id where name='''
                  + @userName + ''' and class=0'
                )
--exec sp_helprotect null,@userName
    --SELECT  *
    --FROM    #privileges
    DECLARE @action VARCHAR(100)
    ,   @state CHAR(1)
    DECLARE actions CURSOR
    FOR
    SELECT  permission_name, [state]
    FROM    #privileges
    OPEN actions
    FETCH NEXT FROM actions INTO @action, @state
    WHILE @@FETCH_STATUS = 0
        BEGIN
            --PRINT 'USE ' + @dbName
            --PRINT 'GO'
            IF @state = 'G'
                BEGIN
                    PRINT 'GRANT ' + @action + ' to [' + @newUserName + ']'
                    PRINT 'GO'
                END
            IF @state = 'D'
                BEGIN
                    PRINT 'DENY ' + @action + ' to [' + @newUserName + ']'
                    PRINT 'GO'
                END
            IF @state = 'R'
                BEGIN
                    PRINT 'REVOKE ' + @action + ' to [' + @newUserName + ']'
                    PRINT 'GO'
                END
            IF @state = 'W'
                BEGIN
                    PRINT 'GRANT ' + @action + ' to [' + @newUserName + '] WITH GRANT OPTION'
                    PRINT 'GO'
                END
            FETCH NEXT FROM actions INTO @action, @state
        END
    CLOSE actions
    DEALLOCATE actions
  
    --增加用户角色
    IF OBJECT_ID('tempdb..#roles', N'U') IS NOT NULL
        DROP TABLE #roles
    CREATE TABLE #roles ( roleName VARCHAR(50) )
    INSERT  INTO #roles
    EXEC( 'select
            case when (r.principal_id is null) then ''public''  else r.name end  roles
            from [' + @dbName + '].sys.database_principals u
            left join [' + @dbName + '].sys.database_role_members m
            join [' + @dbName
        + '].sys.database_principals r on m.role_principal_id = r.principal_id on u.principal_id = m.member_principal_id
            left join [' + @dbName + '].sys.server_principals l on u.sid = l.sid
            where u.name = ''' + @userName + ''' and u.type <> ''R'''
                )
--exec sp_helpuser @userName
    DECLARE @role VARCHAR(100)
    DECLARE roles CURSOR
    FOR
    SELECT  roleName
    FROM    #roles
    OPEN roles
    FETCH NEXT FROM roles INTO @role
    WHILE @@FETCH_STATUS = 0
        BEGIN
            --PRINT 'USE ' + @dbName
            --PRINT 'GO'
            PRINT 'EXEC sp_addrolemember N''' + @role + ''',N''' + @newUserName + ''''
            PRINT 'GO'
            FETCH NEXT FROM roles INTO @role
        END
    CLOSE roles
    DEALLOCATE roles
  
    --克隆用户的对象权限
    IF OBJECT_ID('tempdb..#text') IS NOT NULL
            DROP TABLE #text
           
    CREATE TABLE #text( grantsql VARCHAR(max))
  
    --1 = 对象或列
    SET @sql=
      'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON OBJECT::''+QUOTENAME(s.name,''['')+''.''
            +quotename(o.name,''['')
            +CASE WHEN c.NAME IS NOT NULL THEN ''(''+QUOTENAME(c.name,''['')+'') ''
                  ELSE '''' END
            +'' TO ''
            +QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION'' ELSE '''' END
      FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
      +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
      +'ON dp.grantee_principal_id=ds.principal_id  '
      +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.objects o '
      +'ON dp.major_id=o.object_id '
      +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.schemas s '
      +'ON o.schema_id=s.schema_id '
      +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.columns c ON dp.minor_id=c.column_id '
      +'    AND dp.major_id=c.object_id '
      +'WHERE ds.name='''+@userName+''' AND class=1'
     
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
  
      --3 = 架构
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
                  ELSE ''GRANT '' END
                  +permission_name COLLATE Chinese_PRC_90_CI_AI
                  +'' ON SCHEMA::''
                  +QUOTENAME(s.name,''['')
                  +'' TO ''
                  +QUOTENAME('''+@newLoginName+''',''['')
                  +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION'' ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds ON dp.grantee_principal_id=ds.principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.schemas s ON dp.major_id=s.schema_id '
            +'WHERE class=3 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --4 = 数据库主体
      --[ USER :: database_user ]
      --     | [ ROLE :: database_role ]
      --     | [ APPLICATION ROLE :: application_role ]
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
                        ELSE ''GRANT '' END
                        +permission_name COLLATE Chinese_PRC_90_CI_AI
                        +CASE s.type WHEN ''U'' THEN ''  ON USER::''
                        WHEN ''R'' THEN  '' ON ROLE::''
                        WHEN ''A'' THEN '' ON APPLICATION ROLE::'' END
                        +QUOTENAME(s.name,''['')
                        +'' TO ''
                        +QUOTENAME('''+@newLoginName+''',''['')
                        +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                              ELSE '''' END
                  FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
                  +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals s ON dp.major_id=s.principal_id '
                  +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals d ON dp.grantee_principal_id=d.principal_id '
                  +'WHERE class=4 AND d.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --5 = 程序集适用范围:SQL Server 2008通过SQL Server 2019。
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
                  ELSE ''GRANT '' END
                  +permission_name COLLATE Chinese_PRC_90_CI_AI
                  +'' ON ASSEMBLY::''
                  +QUOTENAME(ab.name,''['')
                  +'' TO ''
                  +QUOTENAME('''+@newLoginName+''',''['')
                  +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                        ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON dp.grantee_principal_id=ds.principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.assemblies ab '
            +'ON dp.major_id=ab.assembly_id '
            +'WHERE class=5 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --6 = 类型
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON TYPE::''
            +QUOTENAME(s.name,''['')+''.''
            +QUOTENAME(t.name,''['')
            +'' TO ''
            +QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON dp.grantee_principal_id=ds.principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.types t '
            +'ON dp.major_id=t.user_type_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.schemas s ON t.schema_id=s.schema_id '
            +'WHERE class=6 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --10 = XML 架构集合的适用范围：SQL Server 2008 到SQL Server 2019。
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON XML SCHEMA COLLECTION::''
            +QUOTENAME(xmlsc.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.xml_schema_collections xmlsc '
            +'ON dp.major_id=xmlsc.xml_collection_id '
            +'WHERE class=10 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --15 = 消息类型适用范围:SQL Server 2008通过SQL Server 2019。
      --授予对Service Broker 消息类型的权限。
      --SELECT * FROM sys.service_message_types
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON message type::''
            +QUOTENAME(m.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.service_message_types m '
            +'ON dp.major_id=m.message_type_id '
            +'WHERE class=15 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --16 = 服务约定适用范围:SQL Server 2008通过SQL Server 2019。
      --SELECT * FROM sys.service_contracts
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON Contract::''
            +QUOTENAME(s.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.service_contracts s '
            +'ON dp.major_id=s.service_contract_id '
            +'WHERE class=16 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --17 = 服务适用范围:SQL Server 2008通过SQL Server 2019。
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON Service::''
            +QUOTENAME(s.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.services s '
            +'ON dp.major_id=s.service_id '
            +'WHERE class=17 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --18 = 远程服务绑定适用范围:SQL Server 2008通过SQL Server 2019。
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON REMOTE SERVICE BINDING::''
            +QUOTENAME(s.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.remote_service_bindings s '
            +'ON dp.major_id=s.remote_service_binding_id '
            +'WHERE class=18 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --19 = 的路由适用范围:SQL Server 2008通过SQL Server 2019。
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON Route::''
            +QUOTENAME(r.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.routes r '
            +'ON dp.major_id=r.route_id '
            +'WHERE class=19 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --23 = 全文目录适用范围:SQL Server 2008通过SQL Server 2019。
      --SELECT * FROM sys.fulltext_catalogs
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON fulltext catalog::''
            +QUOTENAME(f.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.fulltext_catalogs  f '
            +'ON dp.major_id=f.fulltext_catalog_id '
            +'WHERE class=23 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --29 = 全文目录停止列表适用范围:SQL Server 2008通过SQL Server 2019。
      --SELECT * FROM sys.fulltext_stoplists
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON fulltext stoplist::''
            +QUOTENAME(f.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.fulltext_stoplists  f '
            +'ON dp.major_id=f.stoplist_id '
            +'WHERE class=29 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --24 = 对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
      --SELECT * FROM sys.symmetric_keys
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON SYMMETRIC KEY::''
            +QUOTENAME(asy.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.symmetric_keys asy '
            +'ON dp.major_id=asy.symmetric_key_id '
            +'WHERE class=24 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --25 = 证书适用范围:SQL Server 2008通过SQL Server 2019。
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON CERTIFICATE::''
            +QUOTENAME(c.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.certificates c '
            +'ON dp.major_id=c.certificate_id '
            +'WHERE class=25 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
     
      --26 = 非对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
      SET @sql=
            'SELECT CASE state WHEN ''D'' THEN ''DENY ''
            ELSE ''GRANT '' END
            +permission_name COLLATE Chinese_PRC_90_CI_AI
            +'' ON ASYMMETRIC KEY::''
            +QUOTENAME(asy.name,''['')
            +'' TO ''+QUOTENAME('''+@newLoginName+''',''['')
            +CASE WHEN state=''W'' THEN '' WITH GRANT OPTION''
                  ELSE '''' END
            FROM '+QUOTENAME(@dbName,']')+'.'+'sys.database_permissions dp '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.database_principals ds '
            +'ON ds.principal_id=dp.grantee_principal_id '
            +'LEFT JOIN '+QUOTENAME(@dbName,']')+'.'+'sys.asymmetric_keys asy '
            +'ON dp.major_id=asy.asymmetric_key_id '
            +'WHERE class=26 AND ds.name='''+@userName+''''
      INSERT INTO #text ( grantsql )
      EXEC(@sql)
   
    DECLARE cur CURSOR
    FOR
    SELECT  *
    FROM    #text
    OPEN cur
    FETCH NEXT FROM cur INTO @sql
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @sql
        FETCH NEXT FROM cur INTO @sql
    END
    CLOSE cur
    DEALLOCATE cur
    DROP TABLE #text
        --END    
END
GO
EXECUTE sys.sp_MS_marksystemobject 'sp_DBA_userPermisionsClone';
GO

sp_DBA_userPermisionsClone功能测试

为检验sp_DBA_userPermisionsClone的可用性，接下来打算创建测试环境，并对功能进行测试。

创建测试环境

--SQL  Server  2008 测试环境
-- 创建两个数据库
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'test')
  DROP DATABASE test;
 
CREATE DATABASE test;
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'test1')
  DROP DATABASE test1;
 
CREATE DATABASE test1;
GO
--创建登陆帐户
USE master;
GO
IF EXISTS ( SELECT  *
            FROM    sys.server_principals
            WHERE   name = 'Jack' )
    DROP LOGIN [Jack];
CREATE LOGIN [Jack] WITH PASSWORD = 'dMkdj!d8@j',CHECK_EXPIRATION=OFF;
GO
-- 创建用户并赋予权限
USE test;
GO
CREATE USER [Jack] FROM LOGIN [Jack];
GO
CREATE USER [Jack1] WITHOUT LOGIN;
--创建数据库角色
CREATE ROLE [testRoleIntest]
--赋予角色执行存储过程权限
GRANT EXECUTE to [testRoleIntest]
--创建应用角色
CREATE APPLICATION ROLE [AppRole]WITH PASSWORD = 'dMkdj!d8@j'
GRANT CONTROL to [AppRole]
--添加角色成员
EXEC sp_addrolemember @rolename = 'db_securityadmin', @membername = 'Jack';
CREATE ROLE testRoleIntest;
GO
EXEC sp_addrolemember @rolename = 'testRoleIntest', @membername = 'Jack';
GO
USE [test]
GO
--0 = 数据库
GRANT ALTER ANY SCHEMA to [jack];
GRANT CREATE TABLE TO [Jack];
GRANT CREATE PROCEDURE TO [Jack] WITH GRANT OPTION;
GO
--1 = 对象或列
--创建表test
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
    DROP TABLE dbo.test;
CREATE TABLE dbo.test
    (
     a INT IDENTITY
    ,b VARCHAR(30)
    ,d DATETIME DEFAULT CURRENT_TIMESTAMP
    );
go
-- 赋予Jack用户查询test的a、d两列的权限
GRANT SELECT on object::dbo.test (a, d) to [Jack];
--拒绝Jack 用户对表test 的更新
DENY UPDATE on object::dbo.test to [Jack];
GO
--3 = 架构
--为Jack用户赋予对架构dbo的查询权限
GRANT SELECT ON SCHEMA::dbo TO [Jack];
--4 = 数据库主体
GRANT ALTER ON ROLE::testRoleIntest TO Jack
GRANT CONTROL ON APPLICATION ROLE::AppRole TO Jack
GRANT ALTER ON USER::Jack1 TO Jack
--5 = 程序集适用范围:SQL Server 2008通过SQL Server 2019。
GRANT REFERENCES ON ASSEMBLY::[Microsoft.SqlServer.Types] TO Jack
--6 = 类型
CREATE TYPE [dbo].[testType] FROM [bigint] NULL
GO
GRANT TAKE OWNERSHIP ON TYPE::dbo.testType TO Jack
--15 = 消息类型适用范围:SQL Server 2008通过SQL Server 2019。
ALTER DATABASE test SET ENABLE_BROKER;
USE test
CREATE MESSAGE TYPE [//Mytest/Sample/RequestMsg] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//Mytest/Sample/ReplyMsg] VALIDATION = WELL_FORMED_XML;
GRANT REFERENCES ON MESSAGE TYPE::[//Mytest/Sample/ReplyMsg] TO [Jack]
--16 = 服务约定适用范围:SQL Server 2008通过SQL Server 2019。
CREATE CONTRACT [//Mytest/Sample/MyContract] (
[//Mytest/Sample/RequestMsg] SENT BY INITIATOR,
[//Mytest/Sample/ReplyMsg] SENT BY TARGET);
GRANT ALTER ON CONTRACT::[//Mytest/Sample/MyContract] TO [Jack]
CREATE QUEUE InitQu;
 
 --17 = 服务适用范围:SQL Server 2008通过SQL Server 2019。
CREATE SERVICE [//Mytest/Sample/InitSvc] ON QUEUE InitQu;
DENY ALTER ON SERVICE::[//Mytest/Sample/InitSvc] TO [Jack]
GRANT VIEW DEFINITION ON SERVICE::[//Mytest/Sample/InitSvc] TO [Jack]
--19 = 的路由适用范围:SQL Server 2008通过SQL Server 2019。
CREATE ROUTE ExpenseRoute WITH SERVICE_NAME=  '//Mytest/Sample/InitSvc', ADDRESS='tcp://www.sqlserver.com:1234';
DENY VIEW DEFINITION ON ROUTE::ExpenseRoute TO [Jack]
GRANT ALTER ON  ROUTE::ExpenseRoute TO [Jack]
--23 = 全文目录适用范围:SQL Server 2008通过SQL Server 2019。
CREATE FULLTEXT CATALOG ftCat AS DEFAULT;
CREATE FULLTEXT STOPLIST mystopList;
GRANT ALTER ON FULLTEXT CATALOG::ftcat TO [Jack]
Deny view definition on fulltext Stoplist::myStopList to [Jack]
GRANT ALTER ON FULLTEXT Stoplist::myStopList to [Jack]
go
USE test1;
GO
CREATE USER [Jack] FROM LOGIN [Jack];
GO
GRANT IMPERSONATE ON USER::dbo TO [Jack];
GO
--1 = 对象或列
CREATE PROCEDURE dbo.SimpleProc
AS
BEGIN
  SET NOCOUNT ON;
  SELECT 'test Procedure';
END;
GO
GRANT EXECUTE ON dbo.SimpleProc TO [Jack];
DENY VIEW DEFINITION ON dbo.SimpleProc TO [Jack];
GO
Use test1
go
--10 = XML 架构集合的适用范围：SQL Server 2008 到SQL Server 2019。
CREATE XML SCHEMA COLLECTION [http://Samples/SQL/ServiceBroker/msgOperationSchema] AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" 
   xmlns          ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" 
   elementFormDefault="qualified" 
   attributeFormDefault="unqualified"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
 
    <xsd:complexType name="StepType" mixed="true" >
        <xsd:choice  minOccurs="0" maxOccurs="unbounded" > 
            <xsd:element name="tool" type="xsd:string" />
            <xsd:element name="material" type="xsd:string" />
            <xsd:element name="blueprint" type="xsd:string" />
            <xsd:element name="specs" type="xsd:string" />
            <xsd:element name="diag" type="xsd:string" />
        </xsd:choice> 
    </xsd:complexType>
 
    <xsd:element  name="root">
        <xsd:complexType mixed="true">
            <xsd:sequence>
                <xsd:element name="Location" minOccurs="1" maxOccurs="unbounded">
                    <xsd:complexType mixed="true">
                        <xsd:sequence>
                            <xsd:element name="step" type="StepType" minOccurs="1" maxOccurs="unbounded" />
                        </xsd:sequence>
                        <xsd:attribute name="LocationID" type="xsd:integer" use="required"/>
                        <xsd:attribute name="SetupHours" type="xsd:decimal" use="optional"/>
                        <xsd:attribute name="MachineHours" type="xsd:decimal" use="optional"/>
                        <xsd:attribute name="LaborHours" type="xsd:decimal" use="optional"/>
                        <xsd:attribute name="LotSize" type="xsd:decimal" use="optional"/>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>' ;
GO
GRANT ALTER ON XML SCHEMA COLLECTION::dbo.[http://Samples/SQL/ServiceBroker/msgOperationSchema] TO [Jack];
DENY TAKE OWNERSHIP ON XML SCHEMA COLLECTION::dbo.[http://Samples/SQL/ServiceBroker/msgOperationSchema] TO [Jack];
 
GO
USE test1
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0m3Str0ng!!P4ssw0rd@';
--26 = 非对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
CREATE ASYMMETRIC KEY ASymKey WITH ALGORITHM = RSA_2048;
GRANT CONTROL ON ASYMMETRIC KEY::ASymKey TO [Jack];
--24 = 对称密钥适用范围:SQL Server 2008通过SQL Server 2019。
CREATE SYMMETRIC KEY SymKey1 WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY ASymKey;
CREATE SYMMETRIC KEY SymKey2 WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE testCert;
GO
GRANT CONTROL ON SYMMETRIC KEY::SymKey1 TO [Jack];
GRANT CONTROL ON SYMMETRIC KEY::SymKey2 TO [Jack];
--25 = 证书适用范围:SQL Server 2008通过SQL Server 2019。
CREATE CERTIFICATE testCert
WITH SUBJECT = 'A test Cert to Show Permission Cloning';
GRANT VIEW DEFINITION ON CERTIFICATE::testCert TO [Jack];
--18 = 远程服务绑定适用范围:SQL Server 2008通过SQL Server 2019。
--定义消息类型
CREATE MESSAGE TYPE [http://Samples/SQL/ServiceBroker/msgOperation]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION
[http://Samples/SQL/ServiceBroker/msgOperationSchema];
--定义消息契约　
CREATE CONTRACT [http://Samples/SQL/ServiceBroker/msgOperationContract]
(
[http://Samples/SQL/ServiceBroker/msgOperation]
SENT BY INITIATOR
);
--初始方
--定义队列
CREATE QUEUE msgOperationInitQueue
WITH
STATUS = ON,
RETENTION = OFF
GO
--定义初始服务
CREATE SERVICE [http://Samples/SQL/ServiceBroker/msgOperationInitService]
ON QUEUE msgOperationInitQueue
([http://Samples/SQL/ServiceBroker/msgOperationContract]);
GO
--定义初始存储过程
CREATE PROCEDURE dbo.usp_msgOperation_SET
@msgId int,
@msgContent nvarchar(2000)
AS
declare @message_body as xml([http://Samples/SQL/ServiceBroker/msgOperationSchema]);
declare @dialog as uniqueidentifier;
--填充消息体
SET @message_body =''+cast(@msgId as varchar)+''+@msgContent+'';
BEGIN DIALOG @dialog
FROM SERVICE [http://Samples/SQL/ServiceBroker/msgOperationInitService]
TO SERVICE 'http://Samples/SQL/ServiceBroker/msgOperationProcessService'
ON CONTRACT [http://Samples/SQL/ServiceBroker/msgOperationContract];
--WITH ENCRYPTION = OFF , LIFETIME = 3600;
--发送消息
SEND ON CONVERSATION @dialog
MESSAGE TYPE [http://Samples/SQL/ServiceBroker/msgOperation] (@message_body);
END CONVERSATION @dialog;
GO
--实现ServiceBroker安全配置
--创建拥有服务的用户
CREATE USER msgOperationInitServiceUser WITHOUT LOGIN;
ALTER AUTHORIZATION ON
SERVICE::[http://Samples/SQL/ServiceBroker/msgOperationInitService]
TO
msgOperationInitServiceUser;
--创建与该用户关联的私钥证书
CREATE CERTIFICATE msgOperactionInitServiceCertPriv AUTHORIZATION msgOperationInitServiceUser
WITH SUBJECT = 'ForMsgOperactionInitService',
START_DATE = '01/01/2009',
EXPIRY_DATE = '01/01/2100';
--将公钥证书备份到文件以供目标方服务使用
BACKUP CERTIFICATE msgOperactionInitServiceCertPriv
TO FILE = 'D:\msgOperactionInitServiceCertPub.cer';
--创建调用目标服务的用户
CREATE USER msgOperationProcessServiceUser WITHOUT LOGIN;
--导入目标服务的证书并把刚才创建的用户设为所有者
CREATE CERTIFICATE msgOperactionProcessServiceCertPub AUTHORIZATION msgOperationProcessServiceUser
FROM FILE = 'D\pub\msgOperactionProcessServiceCertPub.cer';
--建立目标服务远程服务绑定
CREATE REMOTE SERVICE BINDING ToMsgOperactionProcessService
TO SERVICE 'http://Samples/SQL/ServiceBroker/msgOperationProcessService'
WITH USER = msgOperationProcessServiceUser;
GRANT ALTER ON REMOTE SERVICE BINDING::ToMsgOperactionProcessService TO Jack

测试普通用户

EXEC sp_DBA_userPermisionsClone test,jack

测试结果

USE test
GO
CREATE USER [jack] FOR LOGIN [jack] WITH DEFAULT_SCHEMA =[dbo]
GO
GRANT ALTER ANY SCHEMA to [jack]
GO
GRANT CONNECT to [jack]
GO
GRANT CREATE PROCEDURE to [jack] WITH GRANT OPTION
GO
GRANT CREATE TABLE to [jack]
GO
EXEC sp_addrolemember N'testRoleIntest',N'jack'
GO
EXEC sp_addrolemember N'db_securityadmin',N'jack'
GO
DENY UPDATE ON OBJECT::[dbo].[test] TO [jack]
GRANT SELECT ON OBJECT::[dbo].[test]([a])  TO [jack]
GRANT SELECT ON OBJECT::[dbo].[test]([d])  TO [jack]
GRANT SELECT ON SCHEMA::[dbo] TO [jack]
 
GRANT CONTROL ON APPLICATION ROLE::[AppRole] TO [jack]
GRANT ALTER ON ROLE::[testRoleIntest] TO [jack]
GRANT REFERENCES ON ASSEMBLY::[Microsoft.SqlServer.Types] TO [jack]
GRANT TAKE OWNERSHIP ON TYPE::[dbo].[testType] TO [jack]
GRANT REFERENCES ON message type::[//Mytest/Sample/ReplyMsg] TO [jack]
GRANT ALTER ON Contract::[//Mytest/Sample/MyContract] TO [jack]
DENY ALTER ON Service::[//Mytest/Sample/InitSvc] TO [jack]
GRANT VIEW DEFINITION ON Service::[//Mytest/Sample/InitSvc] TO [jack]
GRANT ALTER ON Route::[ExpenseRoute] TO [jack]
DENY VIEW DEFINITION ON Route::[ExpenseRoute] TO [jack]
GRANT ALTER ON fulltext catalog::[ftCat] TO [jack]
GRANT ALTER ON fulltext stoplist::[mystopList] TO [jack]
DENY VIEW DEFINITION ON fulltext stoplist::[mystopList] TO [jack]

EXEC sp_DBA_userPermisionsClone test1,jack

测试结果

USE test1
GO
CREATE USER [jack] FOR LOGIN [jack] WITH DEFAULT_SCHEMA =[dbo]
GO
GRANT CONNECT to [jack]
GO
EXEC sp_addrolemember N'public',N'jack'
GO
GRANT EXECUTE ON OBJECT::[dbo].[SimpleProc] TO [jack]
DENY VIEW DEFINITION ON OBJECT::[dbo].[SimpleProc] TO [jack]
GRANT IMPERSONATE  ON USER::[dbo] TO [jack]
GRANT ALTER ON XML SCHEMA COLLECTION::[http://Samples/SQL/ServiceBroker/msgOperationSchema] TO [jack]
DENY TAKE OWNERSHIP ON XML SCHEMA COLLECTION::[http://Samples/SQL/ServiceBroker/msgOperationSchema] TO [jack]
GRANT ALTER ON REMOTE SERVICE BINDING::[ToMsgOperactionProcessService] TO [jack]
GRANT CONTROL ON SYMMETRIC KEY::[SymKey1] TO [jack]
GRANT CONTROL ON SYMMETRIC KEY::[SymKey2] TO [jack]
GRANT VIEW DEFINITION ON CERTIFICATE::[testCert] TO [jack]
GRANT CONTROL ON ASYMMETRIC KEY::[ASymKey] TO [jack]

测试角色

EXEC sp_DBA_userPermisionsClone test,testRoleIntest

测试结果

USE test
GO
--数据库角色克隆
CREATE ROLE [testRoleIntest]
GRANT EXECUTE to [testRoleIntest]
GO

测试应用角色

EXEC sp_DBA_userPermisionsClone test,AppRole

测试结果

USE test
GO
--应用角色克隆
CREATE APPLICATION ROLE [AppRole]WITH PASSWORD = ''  , DEFAULT_SCHEMA = [dbo]
GRANT CONTROL to [AppRole]
GO
EXEC sp_addrolemember N'public',N'AppRole'
GO

SQL Server 2016部分包含数据库中包含用户测试

--SQL Server 2016 部分包含数据库中包含用户测试
--创建部分包含数据库
CREATE DATABASE [test2]
 CONTAINMENT = PARTIAL
 
 --创建包含用户
USE test2
GO
CREATE USER [Jack] WITH PASSWORD ='a8ea v*(Rd##+'
--赋予包含用户权限
GRANT CONNECT to [Jack]
GO
GRANT SELECT to [Jack]
GO
EXEC sp_DBA_userPermisionsClone test2,Jack

测试结果

USE test2
GO
CREATE USER [Jack] WITH PASSWORD ='',DEFAULT_SCHEMA =[dbo],SID = 0x010500000000000903000000301792D81CCF6B4093A36928EE829FF0
GO
GRANT CONNECT to [Jack]
GO
GRANT SELECT to [Jack]
GO
EXEC sp_addrolemember N'public',N'Jack'
GO

从测试结果来看，sp_DBA_userPermisionsClone完美实现了对用户（包括包含用）、角色、应用角色权限角色的克隆。

三空道人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
克隆SQL Server 用户及权限

《克隆 SQL Server 登陆名及权限》、《SQL Server 登陆账户权限克隆——sp_DBA_LoginClone 升级版》两文介绍了如何生成一个账户的创建脚本及权限赋予脚本，《SQL Server 登录账户权限异步同步》给出了登陆账户及权限克隆的一个应用。通过自动化管理，极大的减轻了DBA的负担，同时也降低了管理时间成本。接下来的脚本给出了数据库用户（包括包含用户）、数据库角色、应用...
复制链接

扫一扫