AWS RDS for SQL Server - 如何创建启动存储过程?

3
我们正在迁移到RDS,我们的一个应用程序需要访问tempdb,我正在尝试找出创建与RDS配合工作的启动作业的最佳方法。目前,我们可以创建一个存储过程,在主数据库中设置所需权限,并使用EXEC sp_procoption 'AddPermissionsToTempDb', 'startup', 'true'命令将其设置为在引导时启动。
但是,在RDS中,我们无法在主数据库中创建存储过程。我尝试在用户拥有的数据库中创建存储过程,但当我尝试使用EXEC sp_procoption 'mydb.dbo.AddPermissionsToTempDb', 'startup', 'true'创建启动作业时,它会说找不到存储过程或者我没有权限……是否还有其他方法在RDS上实现这个功能?

1
你可以尝试使用SQL Server Agent安排一个任务,该任务在代理启动时运行(使用sp_add_jobschedule并将@freq_type设置为64)。免责声明:我不知道这是否有效。如果有效,它将有助于使启动时正在运行的内容可见;启动过程并不是非常明显。 - Jeroen Mostert
1个回答

1

我能够找到一个基于Jeroen Mostert的评论的解决方案,所以功劳归于他们。这是我使用的完整查询,用于创建启动作业,授予一组用户在AWS RDS SQL Server实例上的tempdb上创建、控制和执行存储过程的权限:

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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接