适用场景
为了用户能够更好的使用和理解华为云 RDS for SQL Server ,rdsuser账号的权限边界如表1所示。
用户可以通过给出的脚本在rdsuser下创建子账号并进行有效管理。
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)
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