If your users are connecting directly to the database when making changes, you could add modified_by and modified_on columns defaulting to suser_name() and sysdatetime() respectively. If you users are connecting...

You should consider writing to a logging table after the rollback, so it will persist, to track what happened and why.

USE Testing
GO
DROP TABLE IF EXISTS dbo.[SAMPLE];
GO
CREATE TABLE dbo.[SAMPLE] (
   ID_NUMERIC VARCHAR(10)
 , SAMPLED_DATE DATE
 , SAMPLING_POINT VARCHAR(15)
);
GO
DROP TABLE IF EXISTS dbo.TEST;
GO
CREATE TABLE dbo.TEST (
 ...

Please provide the sample data in the form mentioned above for the three tables in your query.

Try adding the login as a user to msdb and add them to the SQLAgentOperatorRole Database role.

• This reply was modified 5 years, 3 months ago by  Joe Torre.

That error indicates there are rows in the referenced table that aren't in the referencing table so SQL Server can't create the constraint.

You can find new columns and tables using a left join with where the column name or table name is null. Then you have to decide what to do if...

Have you looked at SQL Server Data Quality Services?https://www.google.com/url?sa=t&source=web&rct=j&url=https://docs.microsoft.com/en-us/sql/data-quality-services/data-quality-services&ved=2ahUKEwjK9KWej-TjAhUhMn0KHdL3DhcQFjAAegQIBxAC&usg=AOvVaw3ERS-iZO5QVMVmiNy9qnAb

L

SQLrdata quality services

SQL Server creates an hidden table for an indexed view. To duplicate that try creating a trigger on each of the tables that inserts or deletes rows in another table...

INSERT INTO P (...)
FROM BACKUPTABLE B
LEFT OUTER JOIN PRODTABLE P
ON B.ID = P.ID
AND P.ID IS NULL;

If the synchronization of other schemas to dbo doesn't have to be immediate, a job could be scheduled to sync periodically.

In the 2008 R2 release they introduced SSIS project deployment model which uses SSISDB. Only the package deployment model is available in the 2008 release.

• This reply was modified 5 years, 7 months ago by  Joe Torre.

A database trigger on master rolling back drop login might work as well based on role membership.

SELECT (TOP 1)

st.name ‘Table Name’,

c.name ‘Column Name’,

t.name ‘Data Type’

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

LEFT...