'Is there any way to track changes from views in MS Sql Server?

I'm looking for how to track changes from a view in MS Sql-Server 2012. And, the role of the log-in user is Public. So, it's hard to do it.

For example, Assuming that there is the schema.

CREATE TABLE [dbo].[USER_CREDENTIAL](
    [USERID] [nvarchar](48) NOT NULL,
    [VALID_FROM] DATETIME NULL,
    [EXPIRED_AT] DATETIME NULL, 
    [CREDENTIAL_ID] int NOT NULL,
    CONSTRAINT [UNIQUE_USERID] PRIMARY KEY CLUSTERED( [USERID] ASC)
) ; 


CREATE VIEW [VIEW_OF_USER_CREDENTIAL] as 
SELECT * FROM dbo.[USER_CREDENTIAL];

It can be only permitted to access the view. The view will be changed when some data is inserted/updated/deleted from the USER_CREDENTIAL table. I will do query to the view.

I saw the document. I tried that, but the target to track should be the data table and the login user is lack of the role. I got the error message.

Object 'foo' is of a data type that is not supported by the CHANGETABLE function. The object must be a user-defined table.

I tried the following. I added the temporary table and the trigger which make changed-data be inserted to the temporary table when the view is changed. But, it was also failed because it was permission denied.

CREATE TABLE dbo.[CHANGES_FROM_A_VIEW] (
    [VERSION] [int] IDENTITY(1,1) NOT NULL, 
    [USERID] [nvarchar](48) NOT NULL, 
    CONSTRAINT [UNIQUE_VERSION] PRIMARY KEY CLUSTERED ( [VERSION] ASC) 
)

CREATE TRIGGER [SOMETHING_CHANGED] ON dbo.[VIEW_OF_USER_CREDENTIAL] ... 

ALTER DATABASE database_name
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)

ALTER TABLE [CHANGES_FROM_A_VIEW]
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

SELECT * FROM CHANGETABLE(CHANGES dbo.CHANGES_FROM_A_VIEW, 0) AS C

Anyone knows any way to solve this?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source