SQL SERVER--用户权限

导出登陆

复制代码
--导login
USE master
GO
IF OBJECT_ID('sp_hexadecimal') IS NOT NULL 
    DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue VARBINARY(256) ,
    @hexvalue VARCHAR(514) OUTPUT
AS 
    DECLARE @charvalue VARCHAR(514)
    DECLARE @i INT
    DECLARE @length INT
    DECLARE @hexstring CHAR(16)
    SELECT  @charvalue = '0x'
    SELECT  @i = 1
    SELECT  @length = DATALENGTH(@binvalue)
    SELECT  @hexstring = '0123456789ABCDEF'
    WHILE ( @i <= @length ) 
        BEGIN
            DECLARE @tempint INT
            DECLARE @firstint INT
            DECLARE @secondint INT
            SELECT  @tempint = CONVERT(INT, SUBSTRING(@binvalue, @i, 1))
            SELECT  @firstint = FLOOR(@tempint / 16)
            SELECT  @secondint = @tempint - ( @firstint * 16 )
            SELECT  @charvalue = @charvalue + SUBSTRING(@hexstring,
                                                        @firstint + 1, 1)
                    + SUBSTRING(@hexstring, @secondint + 1, 1)
            SELECT  @i = @i + 1
        END

    SELECT  @hexvalue = @charvalue
GO
 
IF OBJECT_ID('sp_help_revlogin') IS NOT NULL 
    DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin
    @login_name SYSNAME = NULL
AS 
    DECLARE @name SYSNAME
    DECLARE @type VARCHAR(1)
    DECLARE @hasaccess INT
    DECLARE @denylogin INT
    DECLARE @is_disabled INT
    DECLARE @PWD_varbinary VARBINARY(256)
    DECLARE @PWD_string VARCHAR(514)
    DECLARE @SID_varbinary VARBINARY(85)
    DECLARE @SID_string VARCHAR(514)
    DECLARE @tmpstr VARCHAR(1024)
    DECLARE @is_policy_checked VARCHAR(3)
    DECLARE @is_expiration_checked VARCHAR(3)

    DECLARE @defaultdb SYSNAME
 
    IF ( @login_name IS NULL ) 
        DECLARE login_curs CURSOR
        FOR
            SELECT  p.sid ,
                    p.name ,
                    p.type ,
                    p.is_disabled ,
                    p.default_database_name ,
                    l.hasaccess ,
                    l.denylogin
            FROM    sys.server_principals p
                    LEFT JOIN sys.syslogins l ON ( l.name = p.name )
            WHERE   p.type IN ( 'S' )
                    AND p.name <> 'sa'
            ORDER BY p.name
    ELSE 
        DECLARE login_curs CURSOR
        FOR
            SELECT  p.sid ,
                    p.name ,
                    p.type ,
                    p.is_disabled ,
                    p.default_database_name ,
                    l.hasaccess ,
                    l.denylogin
            FROM    sys.server_principals p
                    LEFT JOIN sys.syslogins l ON ( l.name = p.name )
            WHERE   p.type IN ( 'S' )
                    AND p.name = @login_name
            ORDER BY p.name
    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled,
        @defaultdb, @hasaccess, @denylogin
    IF ( @@fetch_status = -1 ) 
        BEGIN
            PRINT 'No login(s) found.'
            CLOSE login_curs
            DEALLOCATE login_curs
            RETURN -1
        END
    SET @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on '
        + @@SERVERNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    WHILE ( @@fetch_status <> -1 ) 
        BEGIN
            IF ( @@fetch_status <> -2 ) 
                BEGIN
                    PRINT ''
                    SET @tmpstr = '-- Login: ' + @name
                    PRINT @tmpstr
                    IF ( @type IN ( 'G', 'U' ) ) 
                        BEGIN -- NT authenticated account/group

                            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME(@name)
                                + ' FROM WINDOWS WITH DEFAULT_DATABASE = ['
                                + @defaultdb + ']'
                        END
                    ELSE 
                        BEGIN -- SQL Server authentication
        -- obtain password and sid
                            SET @PWD_varbinary = CAST(LOGINPROPERTY(@name,
                                                              'PasswordHash') AS VARBINARY(256))
                            EXEC sp_hexadecimal @PWD_varbinary,
                                @PWD_string OUT
                            EXEC sp_hexadecimal @SID_varbinary,
                                @SID_string OUT
 
        -- obtain password policy state
                            SELECT  @is_policy_checked = CASE is_policy_checked
                                                           WHEN 1 THEN 'ON'
                                                           WHEN 0 THEN 'OFF'
                                                           ELSE NULL
                                                         END
                            FROM    sys.sql_logins
                            WHERE   name = @name
                            SELECT  @is_expiration_checked = CASE is_expiration_checked
                                                              WHEN 1 THEN 'ON'
                                                              WHEN 0
                                                              THEN 'OFF'
                                                              ELSE NULL
                                                             END
                            FROM    sys.sql_logins
                            WHERE   name = @name
 
                            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME(@name)
                                + ' WITH PASSWORD = ' + @PWD_string
                                + ' HASHED, SID = ' + @SID_string
                                + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

                            IF ( @is_policy_checked IS NOT NULL ) 
                                BEGIN
                                    SET @tmpstr = @tmpstr
                                        + ', CHECK_POLICY = '
                                        + @is_policy_checked
                                END
                            IF ( @is_expiration_checked IS NOT NULL ) 
                                BEGIN
                                    SET @tmpstr = @tmpstr
                                        + ', CHECK_EXPIRATION = '
                                        + @is_expiration_checked
                                END
                        END
                    IF ( @denylogin = 1 ) 
                        BEGIN -- login is denied access
                            SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO '
                                + QUOTENAME(@name)
                        END
                    ELSE 
                        IF ( @hasaccess = 0 ) 
                            BEGIN -- login exists but does not have access
                                SET @tmpstr = @tmpstr
                                    + '; REVOKE CONNECT SQL TO '
                                    + QUOTENAME(@name)
                            END
                    IF ( @is_disabled = 1 ) 
                        BEGIN -- login is disabled
                            SET @tmpstr = @tmpstr + '; ALTER LOGIN '
                                + QUOTENAME(@name) + ' DISABLE'
                        END
                    PRINT @tmpstr
                END

            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type,
                @is_disabled, @defaultdb, @hasaccess, @denylogin
        END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
GO



EXEC sp_help_revlogin
复制代码

导出用户

复制代码
--============================导用户===============================
---This script will output all user defined users, roles, along with the corresponding sp_addrolemember statement
IF EXISTS ( SELECT  *
            FROM    sys.database_principals u
                    LEFT JOIN sys.server_principals l ON u.sid = l.sid
            WHERE   u.type = 's'
                    AND l.sid IS NULL
                    AND LEN(u.sid) <= 16
                    AND u.sid <> 0x0 ) 
    BEGIN
        PRINT 'There are more than one orphan users in this database, please fix them '
        PRINT 'sp_change_users_login @Action=''Report'''
    END
        

PRINT '------------------------------------------DDL FOR create user----------------------------------------------------'
PRINT 'begin tran'
PRINT '  begin try'
--S = SQL 用户

--U = Windows 用户

--G = Windows 组

--A = 应用程序角色 !!!!!! none

--R = 数据库角色

--C = 映射到证书的用户

--K = 映射到非对称密钥的用户


DECLARE @sp_create_user_sqlStatement NVARCHAR(4000) 
DECLARE proc_cur CURSOR
FOR
    SELECT  CASE u.type
              WHEN 's'
              THEN  --sql login
                   CASE ISNULL(l.sid, 0) --
                     WHEN 0
                     THEN CASE WHEN LEN(u.sid) > 16
                               THEN 'create user [' + u.name
                                    + '] without login WITH DEFAULT_SCHEMA = '
                                    + u.default_schema_name
                          END
                     ELSE 'create user [' + u.name + '] for login [' + l.name
                          + '] WITH DEFAULT_SCHEMA = ' + u.default_schema_name
                   END
              WHEN 'u'
              THEN 'create user [' + u.name + '] WITH DEFAULT_SCHEMA = '
                   + u.default_schema_name
              WHEN 'g'
              THEN 'create user [' + u.name + '] WITH DEFAULT_SCHEMA = '
                   + u.default_schema_name
              WHEN 'C'
              THEN 'create user [' + u.name + '] for certificate [' + c.name
                   + ']'
              WHEN 'K'
              THEN 'create user [' + u.name + '] for asymmetric key ['
                   + a.name + ']'
            END
    FROM    sys.database_principals u
            LEFT JOIN sys.server_principals l ON u.sid = l.sid
            LEFT JOIN sys.certificates c ON u.sid = c.sid
            LEFT JOIN sys.asymmetric_keys a ON u.sid = a.sid
    WHERE   u.type <> 'r'
            AND u.principal_id > 4--eliminate user dbo,guest,INFORMATION_SCHEMA,sys
ORDER BY    u.name

OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @sp_create_user_sqlStatement
 

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @sp_create_user_sqlStatement   
        FETCH NEXT FROM proc_cur INTO @sp_create_user_sqlStatement
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  
PRINT CHAR(10) + CHAR(10)




PRINT ''


PRINT '----------------------------------------DDL FOR creating customized role----------------------------------------------------'

DECLARE @sp_create_role_sqlStatement NVARCHAR(4000) 
DECLARE proc_cur CURSOR
FOR
    SELECT  'create role [' + r.name + ']  AUTHORIZATION  [' + u.name + ']'
    FROM    sys.database_principals R
            INNER JOIN sys.database_principals u ON r.owning_principal_id = u.principal_id
    WHERE   r.type = 'r'
            AND r.IS_FIXED_ROLE = 0
            AND u.type <> 'r'
            AND r.principal_id > 0
--eliminate role 'public' 

OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @sp_create_role_sqlStatement
 

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @sp_create_role_sqlStatement   
        FETCH NEXT FROM proc_cur INTO @sp_create_role_sqlStatement
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  


PRINT ''



PRINT '----------------------------------------DDL FOR sp_addrolemember----------------------------------------------------'
DECLARE @sp_addrolemember_sqlStatement NVARCHAR(MAX) 
DECLARE proc_cur CURSOR
FOR
    SELECT  'exec sp_addrolemember ''' + g.name + ''',''' + u.name + ''''
    FROM    sys.database_principals u ,
            sys.database_principals g ,
            sys.database_role_members m
    WHERE   g.principal_id = m.role_principal_id
            AND u.principal_id = m.member_principal_id
            AND u.name <> 'dbo'

OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @sp_addrolemember_sqlStatement
 

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @sp_addrolemember_sqlStatement   
        FETCH NEXT FROM proc_cur INTO @sp_addrolemember_sqlStatement
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  



                        

PRINT ''

PRINT '    goto successed'
PRINT '  end try'
PRINT '  begin catch'
PRINT '    print ERROR_MESSAGE()'
PRINT '    goto failed'
PRINT '  end catch'
PRINT ''
PRINT 'successed:'
PRINT '  commit'
PRINT '  print ''successed'''
PRINT '  return'
PRINT ''
PRINT 'failed:'
PRINT '  rollback'
PRINT '  print ''failed'''
PRINT '  print ''rollback'''
复制代码

 

导出用户权限

复制代码
--==========================导权限===============================

--this script will list all explicit permission on object,database,database princpal and schema
PRINT '-----------------------------------------DDL for permission on object/column----------------'
PRINT 'begin tran'
PRINT '  begin try'


DECLARE @SqlStatement_Permission_object VARCHAR(1000)
DECLARE proc_cur CURSOR
FOR
    SELECT  CASE p.minor_id
              WHEN 0
              THEN--grant permission to object directly
                   CASE p.state
                     WHEN 'G'
                     THEN 'Grant  ' + p.permission_name + ' on object::['
                          + SCHEMA_NAME(o.schema_id) + '].[' + o.name
                          + '] to [' + u.name + ']'
                     WHEN 'D'
                     THEN 'Deny   ' + p.permission_name + ' on object::['
                          + SCHEMA_NAME(o.schema_id) + '].[' + o.name
                          + '] to [' + u.name + ']'
                     WHEN 'R'
                     THEN 'Revoke ' + p.permission_name + ' on object::['
                          + SCHEMA_NAME(o.schema_id) + '].[' + o.name
                          + '] to [' + u.name + ']'
                     WHEN 'W'
                     THEN 'Grant  ' + p.permission_name + ' on object::['
                          + SCHEMA_NAME(o.schema_id) + '].[' + o.name
                          + '] to [' + u.name + '] with grant option'
                   END
              ELSE --grant permission on columns
                   CASE p.state
                     WHEN 'G'
                     THEN 'Grant  ' + p.permission_name + ' ([' + c.name
                          + ']) ' + ' on object::[' + SCHEMA_NAME(o.schema_id)
                          + '].[' + o.name + '] to [' + u.name + ']'
                     WHEN 'D'
                     THEN 'Deny   ' + p.permission_name + ' ([' + c.name
                          + ']) ' + ' on object::[' + SCHEMA_NAME(o.schema_id)
                          + '].[' + o.name + '] to [' + u.name + ']'
                     WHEN 'R'
                     THEN 'Revoke ' + p.permission_name + ' ([' + c.name
                          + ']) ' + ' on object::[' + SCHEMA_NAME(o.schema_id)
                          + '].[' + o.name + '] to [' + u.name + ']'
                     WHEN 'W'
                     THEN 'Grant  ' + p.permission_name + ' ([' + c.name
                          + ']) ' + ' on object::[' + SCHEMA_NAME(o.schema_id)
                          + '].[' + o.name + '] to [' + u.name
                          + '] with grant option'
                   END
            END  COLLATE DATABASE_DEFAULT
    FROM    sys.database_permissions p
            INNER JOIN sys.database_principals u ON ( p.grantee_principal_id = u.principal_id )
            INNER JOIN sys.objects o ON ( o.object_id = p.major_id )
            LEFT JOIN sys.columns c ON ( o.object_id = c.object_id
                                         AND p.minor_id = c.column_id
                                       )
    WHERE   p.class = 1 --object/column
            AND u.name NOT IN ( 'dbo', 'INFORMATION_SCHEMA', 'sys' )
    ORDER BY o.name ,
            u.name ,
            p.State
OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @SqlStatement_Permission_object
 

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT @SqlStatement_Permission_object   
        FETCH NEXT FROM proc_cur INTO @SqlStatement_Permission_object
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  
PRINT CHAR(10) + CHAR(10)


PRINT '-----------------------------------------DDL for permission on database----------------'
DECLARE @SqlStatement_Permission_Database VARCHAR(1000)
DECLARE proc_cur CURSOR
FOR
    SELECT  CASE p.state
              WHEN 'G'
              THEN 'Grant  ' + p.permission_name + ' to [' + u.name + ']'
              WHEN 'D'
              THEN 'Deny   ' + p.permission_name + ' to [' + u.name + ']'
              WHEN 'R'
              THEN 'Revoke ' + p.permission_name + ' to [' + u.name + ']'
              WHEN 'W'
              THEN 'Grant  ' + p.permission_name + ' to [' + u.name
                   + '] with grant option'
            END COLLATE DATABASE_DEFAULT
    FROM    sys.database_permissions p
            INNER JOIN sys.database_principals u ON ( p.grantee_principal_id = u.principal_id )
    WHERE   p.class = 0 --database
            AND u.name NOT IN ( 'dbo', 'INFORMATION_SCHEMA', 'sys' )
    ORDER BY u.name ,
            p.State
OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @SqlStatement_Permission_Database

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @SqlStatement_Permission_Database   
        FETCH NEXT FROM proc_cur INTO @SqlStatement_Permission_Database
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  
PRINT CHAR(10)


PRINT '-----------------------------------------DDL for permission on database principal----------------'
DECLARE @SqlStatement_Permission_DatabasePrincipal VARCHAR(1000)
DECLARE proc_cur CURSOR
FOR
    SELECT  CASE p.state
              WHEN 'G'
              THEN 'Grant  ' + p.permission_name + ' on user::' + u2.name
                   + ' to [' + u1.name + ']'
              WHEN 'D'
              THEN 'Deny   ' + p.permission_name + ' on user::' + u2.name
                   + ' to [' + u1.name + ']'
              WHEN 'R'
              THEN 'Revoke ' + p.permission_name + ' on user::' + u2.name
                   + ' to [' + u1.name + ']'
              WHEN 'W'
              THEN 'Grant  ' + p.permission_name + ' on user::' + u2.name
                   + ' to [' + u1.name + '] with grant option'
            END COLLATE DATABASE_DEFAULT
    FROM    sys.database_permissions p
            INNER JOIN sys.database_principals u1 ON ( p.grantee_principal_id = u1.principal_id )
            INNER JOIN sys.database_principals u2 ON ( p.major_id = u2.principal_id )
    WHERE   p.class = 4 --database principal
            AND u1.name NOT IN ( 'dbo', 'INFORMATION_SCHEMA', 'sys' )
    ORDER BY u2.name ,
            u1.name ,
            p.State
OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @SqlStatement_Permission_DatabasePrincipal

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @SqlStatement_Permission_DatabasePrincipal   
        FETCH NEXT FROM proc_cur INTO @SqlStatement_Permission_DatabasePrincipal
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  
PRINT CHAR(10)



PRINT '-----------------------------------------DDL for permission on Schema----------------'
DECLARE @SqlStatement_Permission_Schema VARCHAR(1000)
DECLARE proc_cur CURSOR
FOR
    SELECT  CASE p.state
              WHEN 'G'
              THEN 'Grant  ' + p.permission_name + ' on Schema::' + s.name
                   + ' to [' + u.name + ']'
              WHEN 'D'
              THEN 'Deny   ' + p.permission_name + ' on Schema::' + s.name
                   + ' to [' + u.name + ']'
              WHEN 'R'
              THEN 'Revoke ' + p.permission_name + ' on Schema::' + s.name
                   + ' to [' + u.name + ']'
              WHEN 'W'
              THEN 'Grant  ' + p.permission_name + ' on Schema::' + s.name
                   + ' to [' + u.name + '] with grant option'
            END COLLATE DATABASE_DEFAULT
    FROM    sys.database_permissions p
            INNER JOIN sys.database_principals u ON ( p.grantee_principal_id = u.principal_id )
            INNER JOIN sys.schemas s ON ( p.major_id = s.schema_id )
    WHERE   p.class = 3 --schema
            AND u.name NOT IN ( 'dbo', 'INFORMATION_SCHEMA', 'sys' )
    ORDER BY s.name ,
            u.name ,
            p.State
OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @SqlStatement_Permission_Schema


WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @SqlStatement_Permission_Schema   
        FETCH NEXT FROM proc_cur INTO @SqlStatement_Permission_Schema
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  
PRINT CHAR(10)

--24 = 对称密钥

-- = 证书

--26 = 非对称密钥

--this script will list all explicit permission on certificate,database,database princpal and schema
PRINT '-----------------------------------------DDL for permission on symmetric key,certificate,,asymmetric key----------------'
DECLARE @SqlStatement_Permission_Key VARCHAR(1000)
DECLARE proc_cur CURSOR
FOR
    SELECT  CASE p.class
              WHEN 24
              THEN --symmetric key
                   CASE p.state
                     WHEN 'G'
                     THEN 'Grant  ' + p.permission_name
                          + ' on symmetric key::[' + s.name + '] to ['
                          + u.name + ']'
                     WHEN 'D'
                     THEN 'Deny   ' + p.permission_name
                          + ' on symmetric key::[' + s.name + '] to ['
                          + u.name + ']'
                     WHEN 'R'
                     THEN 'Revoke ' + p.permission_name
                          + ' on symmetric key::[' + s.name + '] to ['
                          + u.name + ']'
                     WHEN 'W'
                     THEN 'Grant  ' + p.permission_name
                          + ' on symmetric key::[' + s.name + '] to ['
                          + u.name + '] with grant option'
                   END
              WHEN 25
              THEN--certificate
                   CASE p.state
                     WHEN 'G'
                     THEN 'Grant  ' + p.permission_name + ' on certificate::['
                          + c.name + '] to [' + u.name + ']'
                     WHEN 'D'
                     THEN 'Deny   ' + p.permission_name + ' on certificate::['
                          + c.name + '] to [' + u.name + ']'
                     WHEN 'R'
                     THEN 'Revoke ' + p.permission_name + ' on certificate::['
                          + c.name + '] to [' + u.name + ']'
                     WHEN 'W'
                     THEN 'Grant  ' + p.permission_name + ' on certificate::['
                          + c.name + '] to [' + u.name + '] with grant option'
                   END
              WHEN 26
              THEN  --asymmetric key
                   CASE p.state
                     WHEN 'G'
                     THEN 'Grant  ' + p.permission_name
                          + ' on asymmetric key::[' + a.name + '] to ['
                          + u.name + ']'
                     WHEN 'D'
                     THEN 'Deny   ' + p.permission_name
                          + ' on asymmetric key::[' + a.name + '] to ['
                          + u.name + ']'
                     WHEN 'R'
                     THEN 'Revoke ' + p.permission_name
                          + ' on asymmetric key::[' + a.name + '] to ['
                          + u.name + ']'
                     WHEN 'W'
                     THEN 'Grant  ' + p.permission_name
                          + ' on asymmetric key::[' + a.name + '] to ['
                          + u.name + '] with grant option'
                   END
            END COLLATE DATABASE_DEFAULT
    FROM    sys.database_permissions p
            INNER JOIN sys.database_principals u ON ( p.grantee_principal_id = u.principal_id )
            LEFT JOIN sys.symmetric_keys s ON ( s.symmetric_key_id = p.major_id )
            LEFT JOIN sys.certificates c ON ( c.certificate_id = p.major_id )
            LEFT JOIN sys.asymmetric_keys a ON ( a.asymmetric_key_id = p.major_id )
    WHERE   p.class IN ( 24, 25, 26 ) --symmetric key,certificate,,asymmetric key
            AND u.name NOT IN ( 'dbo', 'INFORMATION_SCHEMA', 'sys' )
    ORDER BY a.name ,
            u.name ,
            p.State
OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @SqlStatement_Permission_Key
 

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @SqlStatement_Permission_Key   
        FETCH NEXT FROM proc_cur INTO @SqlStatement_Permission_Key
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  
PRINT CHAR(10)

PRINT '-----------------------------------------DDL for Alter authorization against object----------------'
DECLARE @SqlStatement_AlterAuthorization VARCHAR(1000)
DECLARE proc_cur CURSOR
FOR
    SELECT  'Alter authorization on [' + SCHEMA_NAME(o.schema_id) + '].['
            + o.name + '] to [' + u.name + ']'
    FROM    sys.database_principals u
            INNER JOIN sys.objects o ON ( o.principal_id = u.principal_id )
    WHERE   o.principal_id IS NOT NULL
    ORDER BY o.name ,
            u.name
OPEN proc_cur;    
FETCH NEXT FROM proc_cur    
INTO @SqlStatement_AlterAuthorization
 

WHILE @@FETCH_STATUS = 0 
    BEGIN    
        PRINT '    ' + @SqlStatement_AlterAuthorization   
        FETCH NEXT FROM proc_cur INTO @SqlStatement_AlterAuthorization
    END;    
CLOSE proc_cur;    
DEALLOCATE proc_cur;  


PRINT ''

PRINT '    goto successed'
PRINT '  end try'
PRINT '  begin catch'
PRINT '    print ERROR_MESSAGE()'
PRINT '    goto failed'
PRINT '  end catch'
PRINT ''
PRINT 'successed:'
PRINT '  commit'
PRINT '  print ''successed'''
PRINT '  return'
PRINT ''
PRINT 'failed:'
PRINT '  rollback'
PRINT '  print ''failed'''
复制代码

 

posted on   笑东风  阅读(284)  评论(0)  编辑  收藏  举报

编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 25岁的心里话
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现

导航

点击右上角即可分享
微信分享提示
AI IDE Trae