Nos mudamos a RDS y una de nuestras aplicaciones necesita acceso a tempdb y estoy tratando de encontrar la mejor manera de crear un trabajo de inicio que funcione con RDS. Actualmente, podemos crear un proceso almacenado que configura los permisos necesarios en la base de datos maestra y usar el EXEC sp_procoption 'AddPermissionsToTempDb', 'startup', 'true' para configurarlo para que comience en el arranque.
Sin embargo, en RDS no podemos crear procesos almacenados en la base de datos maestra. Intenté crear el proceso almacenado en una base de datos propiedad del usuario, pero cuando intento crear el trabajo de inicio con EXEC sp_procoption 'mydb.dbo.AddPermissionsToTempDb', 'startup', 'true' dice que no puede encontrar el procedimiento almacenado o no tengo permiso... ¿Hay otra forma de lograr esto en RDS?
Pude encontrar una solución basada en el comentario de Jeroen Mostert, así que el crédito es para ellos. Esta es la consulta completa que utilicé para crear el trabajo de inicio para otorgar permisos a una lista de usuarios para crear, controlar y ejecutar procedimientos almacenados en tempdb en una instancia de AWS RDS SQL Server:
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