文档首页 > > 最佳实践> SQL Server> 在rdsuser主帐号下创建并管理子账号

在rdsuser主帐号下创建并管理子账号

分享
更新时间:2020/06/20 GMT+08:00

适用场景

为了用户能够更好的使用和理解华为云RDS for SQL Server,rdsuser账号的权限边界如表1所示。

用户可以通过给出的脚本在rdsuser下创建子账号并进行有效管理。

rdsuser权限

表1 rdsuser权限

名称

权限分类

权限

实例级权限

实例级角色权限

[processadmin]

[setupadmin]

实例级对象权限

ALTER ANY CONNECTION

ALTER ANY LOGIN

ALTER ANY SERVER ROLE

ALTER SERVER STATE

ALTER TRACE

CONNECT ANY DATABASE

CONTROL SERVER

CONNECT SQL

CREATE ANY DATABASE

SELECT ALL USER SECURABLES

VIEW ANY DEFINITION

VIEW ANY DATABASE

VIEW SERVER STATE

数据库权限

master:public

Msdb:Public

SQLAentUserRole

Model:Public

Rdsadmin:Public

OtherDB:Db_Owner

创建子帐号

通过如下脚本,可快速的通过rdsuser创建子账号。

本脚本只适用于SQL Server 2014及以上版本,2008R2需要修改一些地方,请自行判断处理。

use [master]
 
DECLARE @DBName NVARCHAR(128)
DECLARE @SQL NVARCHAR(max)
DECLARE @Login_Name nvarchar(128)
DECLARE @Login_Password nvarchar(128)
 
set @Login_Name = 'TestLogin3'        --change your login name
set @Login_Password = '1qaz!QAZ'
 
        SET @SQL='
            USE [master]
 
CREATE LOGIN  '+@Login_name+' WITH PASSWORD=N'''+ @Login_Password +''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
           
            alter server role [processadmin] add member  '+@Login_name+'
            alter server role [setupadmin] add member '+@Login_name+'
            GRANT VIEW SERVER STATE TO '+@Login_name+' WITH GRANT OPTION
            GRANT VIEW ANY DEFINITION TO '+@Login_name+' WITH GRANT OPTION
            GRANT VIEW ANY DATABASE TO '+@Login_name+' WITH GRANT OPTION
            GRANT CREATE ANY DATABASE TO '+@Login_name+' WITH GRANT OPTION
            GRANT ALTER SERVER STATE TO '+@Login_name+' WITH GRANT OPTION
            GRANT ALTER TRACE TO '+@Login_name+' WITH GRANT OPTION
            GRANT ALTER ANY SERVER ROLE TO '+@Login_name+' WITH GRANT OPTION
            GRANT ALTER ANY LOGIN TO '+@Login_name+' WITH GRANT OPTION
            GRANT ALTER ANY CONNECTION TO '+@Login_name+' WITH GRANT OPTION
            GRANT CONNECT SQL TO '+@Login_name+' WITH GRANT OPTION
            GRANT VIEW SERVER STATE TO '+@Login_name+' WITH GRANT OPTION
        '
print @SQL
        exec (@SQL)
 
        SET @SQL='
            use [msdb]
            if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')
            begin
                ALTER USER '+@Login_name+' with login = '+@Login_name+';
            end
            else
            begin
                CREATE USER '+@Login_name+' FOR LOGIN '+@Login_name+';
            end
            ALTER ROLE [SQLAgentUserRole] ADD MEMBER '+@Login_name+'
            GRANT ALTER ON ROLE::[SQLAgentUserRole] TO '+@Login_name+' WITH GRANT OPTION
            GRANT ALTER ANY USER TO  '+@Login_name+'  WITH GRANT OPTION
            GRANT EXEC ON msdb.dbo.sp_delete_database_backuphistory TO   '+@Login_name+'    WITH GRANT OPTION
            GRANT EXEC ON msdb.dbo.sp_purge_jobhistory TO   '+@Login_name+'    WITH GRANT OPTION
 
            GRANT SELECT ON msdb.dbo.sysjobs TO '+@Login_name+'  WITH GRANT OPTION;
            GRANT SELECT ON msdb.dbo.sysschedules TO '+@Login_name+'  WITH GRANT OPTION;
            GRANT SELECT ON msdb.dbo.sysjobsteps TO '+@Login_name+'  WITH GRANT OPTION;
            GRANT SELECT ON msdb.dbo.sysjobhistory TO '+@Login_name+'  WITH GRANT OPTION;
            GRANT SELECT ON msdb.dbo.syscategories TO '+@Login_name+'  WITH GRANT OPTION;
            GRANT SELECT ON msdb.dbo.sysjobschedules TO '+@Login_name+'  WITH GRANT OPTION;
        '
print @SQL
        exec (@SQL)
       
SET @SQL='
            use [tempdb]
            if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')
            begin
                ALTER USER '+@Login_name+' with login = '+@Login_name+';
            end
            else
            begin
                CREATE USER '+@Login_name+' FOR LOGIN '+@Login_name+';
            end
            GRANT CONTROL TO  '+@Login_name+'
        '
print @SQL
exec (@SQL)
 
        declare DBName_Cursor cursor  for
        select quotename(name) from sys.databases where database_id > 4 and state = 0
        and name not like '%$%'
        and name <> 'rdsadmin'
        open DBName_Cursor
            fetch next from DBName_Cursor into @DBName
            WHILE @@FETCH_STATUS = 0
            begin
                SET @SQL='        USE ' + (@DBName) + '
                            if exists(select top 1 1 from sys.sysusers where name = '''+ @Login_Name +''')
                            begin
                                ALTER USER '+@Login_name+' with login = '+@Login_name+';
                                ALTER ROLE [db_owner] ADD MEMBER '+@Login_name+';
                            end
                            else
                            begin
                                CREATE USER '+@Login_name+' FOR LOGIN '+@Login_name+';
                                ALTER ROLE [db_owner] ADD MEMBER '+@Login_name+';
                            end
                            '
print @SQL
                EXEC (@SQL)
                fetch next from DBName_Cursor into @DBName
            end
        close DBName_Cursor
        deallocate DBName_Cursor

分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问