最新消息:ww12345678 的部落格重装上线,希望大家继续支持。

在Dynamics AX 2009同步之后,使用SQL DDL触发器以编程方式还原读取权限 / Using SQL DDL Triggers to restore read Permissions programatically after Dynamics AX 2009 Synchronization

网络文摘 William 1169浏览 0评论
Recently, a customer using Dynamics AX 2009 implemeted a web service that access a view directly in SQL Server. Therefore they created a new SQL user login and gave the user read permissions on the view.
Read permission on a view
However, when synchronizing the data dictionary in Dynamics AX 2009, the views are droped and recreated and the permission on the object is lost. Therfore the webservice call fails. One way to address this issue from a SQL perspective is to create a DDL trigger that sets the permissions on the view programmatically. Here is a small SQL script that sets read permissions for the user view_user on the the DIRPARTYVIEW after the view has been created again.
CREATE TRIGGER [VIEW_PERMISSION] 
ON DATABASE 
    FOR CREATE_VIEW
AS 
BEGIN
    DECLARE @name SYSNAME
    SELECT  @name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')

    if @name = 'DIRPARTYVIEW' begin
        GRANT SELECT ON [dbo].[DIRPARTYVIEW] TO [view_user]
    end
END
GO

ENABLE TRIGGER [VIEW_PERMISSION] ON DATABASE
GO
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址