We are moving to RDS and one of our apps needs access to tempdb and I am trying to figure out the best way to create a startup job that works with RDS. Currently we are able to create a stored proc that sets up the necessary permissions in the master database and use the EXEC sp_procoption 'AddPermissionsToTempDb', 'startup', 'true' command to set it to start at boot.
In RDS however we are not able to create stored procs in the master database. I tried creating the stored proc in a user-owned db but when I then try to create the startup job with EXEC sp_procoption 'mydb.dbo.AddPermissionsToTempDb', 'startup', 'true' it says it can't find the stored procedure or I do not have permission... Is there another way to accomplish this on RDS?
Was able to find a solution based on Jeroen Mostert's comment so credit goes to them. Here is the full query I used to create the startup job to grant permissions to a list of users to create, control and execute stored procedures on tempdb on an AWS RDS SQL Server instance:
USE msdb
go
declare @job_name varchar(50)
set @job_name = 'AddTempDBPermissionsOnStartup'
exec dbo.sp_delete_job @job_name = @job_name
declare @sql varchar(max)
select @sql = '
Declare @Users Table (username varchar(100) )
insert @Users(username) values (''[user1]''),
(''[user2]''),
(''[user3]'')
use tempdb
CREATE ROLE sp_executor GRANT EXECUTE TO sp_executor
CREATE ROLE sp_manipulator
GRANT CREATE PROCEDURE TO sp_manipulator
GRANT CONTROL TO sp_manipulator
DECLARE @username as NVARCHAR(100);
DECLARE User_Cursor CURSOR FOR
SELECT * from @Users
OPEN User_Cursor;
FETCH NEXT FROM User_Cursor INTO @username;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @username
IF EXISTS(SELECT * FROM [tempdb].sys.database_principals WHERE type_desc = ''SQL_USER'' AND name = @username)
PRINT '' - user already exists''
ELSE
BEGIN
PRINT '' - creating user''
DECLARE @Sql VARCHAR(MAX)
SET @Sql =
''USE Tempdb'' + char(13) +
''CREATE USER '' + @username + '' FOR LOGIN '' + @username + char(13) +
''EXEC sp_addrolemember sp_executor, '' + @username + char(13) +
''EXEC sp_addrolemember sp_manipulator, '' + @username
EXEC (@Sql)
END
FETCH NEXT FROM User_Cursor INTO @username;
END;
CLOSE User_Cursor;
DEALLOCATE User_Cursor;
GO
'
--Add a job
EXEC dbo.sp_add_job
@job_name = @job_name ;
--Add a job step to run the command
EXEC sp_add_jobstep
@job_name = @job_name,
@step_name = N'job step',
@subsystem = N'TSQL',
@command = @sql
--Schedule the job to run at startup
exec sp_add_jobschedule @job_name = @job_name,
@name = 'RunAtStartSchedule',
@freq_type=64
--Add the job to the SQL Server Server
EXEC dbo.sp_add_jobserver
@job_name = @job_name