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
