sql-server-2008 - SQL Server 2008 到 SQL Azure sp_helpuser 和 sp_send_dbmail

Question

我收到消息说 azure 不支持以下 sp_helpuser 和 sp_send_dbmail。

解决此问题的推荐方法是什么？

这里是使用这些 sp 的地方:

CREATE PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers]
    @name   sysname
    AS
    BEGIN
    CREATE TABLE #aspnet_RoleMembers
(
    Group_name      sysname,
    Group_id        smallint,
    Users_in_group  sysname,
    User_id         smallint
)

INSERT INTO #aspnet_RoleMembers
EXEC sp_helpuser @name <--here it is

DECLARE @user_id smallint
DECLARE @cmd nvarchar(500)
DECLARE c1 cursor FORWARD_ONLY FOR
    SELECT User_id FROM #aspnet_RoleMembers

OPEN c1

FETCH c1 INTO @user_id
WHILE (@@fetch_status = 0)
BEGIN
    SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
    EXEC (@cmd)
    FETCH c1 INTO @user_id
END

CLOSE c1
DEALLOCATE c1
END
GO

而另一个 sp:

CREATE PROCEDURE [dbo].[spSendMail]
@To VARCHAR(200),
@Subject VARCHAR(200),
@Body VARCHAR(MAX) = '',
@From VARCHAR(50) = 'info.pt@consultaclick.com',
@format VARCHAR(20) = 'HTML' --HTML
 AS

DECLARE
@profile VARCHAR(50)

select @profile = P.DBMailProfile from tblPaises P INNER JOIN tblParametros PR On P.Codigo = PR.Pais


EXEC MSDB..sp_send_dbmail @profile_name = @profile,
        @recipients =  'someone@somewhere.pt;someon@somehwere.pt',
        --@recipients =  @To,
        --@from_address = @From,
        @subject = @Subject,
        @Body = @Body,
        @body_format = @format
GO

Answer 1

是的，SQL Azure 不支持“sp_helpuser”，所以如果你想模拟另一个用户，你可以使用“EXECUTE AS USER”。更多详情是here .

SQL Azure 中也不支持“msdb.dbo.sp_send_dbmail”，但是这里有一个 blog which has the solution为你。也可能有其他解决方案，但您肯定可以考虑使用最重要的解决方案。