This section provides a script to help you create a subaccount of rdsuser and manage it. Table 1 lists the permissions of rdsuser.

Creating a Subaccount

You can use the following script to quickly create a subaccount of rdsuser:

The following script applies only to Microsoft SQL Server 2014 and later versions. If you want to apply the script to Microsoft SQL Server 2008 R2, you need to modify the script based on your requirements.

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