[Microsoft SQL Server] RDS SQL SERVER 上主账号rdsuser的权限边界,以及他创建的子账号授权范围(新增2008R2)

适用场景:

通过诠释rdsuser账号的权限边界,让用户能够更好的使用和理解华为云RDS SQL SERVER。

同时给出了全套的授权脚本,用户可以通过使用该脚本在rdsuser下创建子账号并进行有效管理。

 

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子账号创建+授权脚本:

为了帮助大家快速的通过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)

 

        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

    

备注:本脚本只适用于SQL SERVER 2008R2 版本,2014以上版本看上面滴

        use [master]

        DECLARE @DBName NVARCHAR(128)

        DECLARE @SQL NVARCHAR(max)

        DECLARE @Login_Name nvarchar(128)

        DECLARE @Login_Password nvarchar(128)

        set @Login_Name = 'TestLogin7'        --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

        EXEC sp_addsrvrolemember '+ @Login_name +' , [processadmin]

        EXEC sp_addsrvrolemember '+ @Login_name +' , [setupadmin]     

        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 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

            EXEC sp_addrolemember [SQLAgentUserRole], '+@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)

        

        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+';

                                EXEC sp_addrolemember N''db_owner'', N'''+@Login_name+''';

                                        

                        end

                        else

                        begin

                            CREATE USER '+@Login_name+' FOR LOGIN '+@Login_name+';

                            EXEC sp_addrolemember N''db_owner'', N'''+@Login_name+''';

                        end

                        '

        print @SQL

            EXEC (@SQL)

            fetch next from DBName_Cursor into @DBName

        end

        close DBName_Cursor

        deallocate DBName_Cursor