Is there a way in SQL Server to make a table only able to insert by trigger?

The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't want the data at risk of being compromised in any way. The only thing that should ever insert on the table is the trigger I created on the active table to log its changes. In the rare case we may need to manually edit the log table I will turn off (if it exists) the "insert lock"

I am using SQL Server 2012 Enterprise with SQL Management Studio

This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via EXECUTE AS can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using the EXECUTE AS. But with module signing: the Certificate-based User cannot be Impersonated (see final test case), another module cannot be signed without knowing the password for the Certificate, and if anyone changes any module that you sign (such as the Trigger), then the signature is automatically removed, alerting you to that change and then you can decide whether to resign it with the current changes or reject the changes ;-).

Also, trapping ApplicationName / ProgramName in a Trigger is not reliable as it is easy to pass in that value in a ConnectionString.

Please note that the Audit table is in a different Schema -- Auditing -- than the main table -- in dbo -- to prevent ownership-chaining, assuming that most Stored Procedures would also be in the dbo Schema.

The Setup

USE [...];
GO

CREATE CERTIFICATE [AuditCert]
ENCRYPTION BY PASSWORD = 'Password Goes Here.'
WITH SUBJECT = 'Restrict Insert Test';
GO

CREATE USER [AuditUser]
FROM CERTIFICATE [AuditCert];
-- no DEFAULT_SCHEMA for Certificate-based Users
GO

CREATE SCHEMA [Auditing]
AUTHORIZATION [AuditUser];
GO

-- DROP TABLE [Auditing].[AuditLog];
CREATE TABLE [Auditing].[AuditLog]
(
AuditLogID INT IDENTITY(1, 1) NOT NULL,
AuditDate DATETIME2 NOT NULL
CONSTRAINT [DF_AuditLog_AuditDate] DEFAULT (SYSDATETIME()),
ImportantStuffID INT,
Column2 VARCHAR(50),
CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED (AuditLogID ASC)
);
GO

CREATE TABLE [dbo].[ImportantStuff]
(
ImportantStuffID INT IDENTITY(1, 1) NOT NULL,
Column2 VARCHAR(50),
CONSTRAINT [PK_ImportantStuff] PRIMARY KEY CLUSTERED (ImportantStuffID ASC)
);
GO

CREATE TRIGGER [dbo].[AuditImportantStuff]
ON [dbo].[ImportantStuff]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
SELECT ins.[ImportantStuffID], ins.[Column2]
FROM inserted ins;
END;
GO

ADD SIGNATURE TO [dbo].[AuditImportantStuff]
BY CERTIFICATE [AuditCert]
WITH PASSWORD = 'Password Goes Here.';
GO

CREATE PROCEDURE [dbo].[AttemptDirectInsert]
(
@ImportantStuffID INT,
@Column2 VARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
VALUES (@ImportantStuffID, @Column2);
GO

CREATE PROCEDURE [dbo].[ImportantStuff_AddData]
(
@ValueForColumn2 VARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [dbo].[ImportantStuff] ([Column2])
VALUES (@ValueForColumn2);
GO

CREATE USER [TestUser]
WITHOUT LOGIN
WITH DEFAULT_SCHEMA = [dbo];
GO

GRANT EXECUTE ON [dbo].[AttemptDirectInsert] TO [TestUser];
GRANT EXECUTE ON [dbo].[ImportantStuff_AddData] TO [TestUser];
GO


The Test

SELECT SESSION_USER, ORIGINAL_LOGIN();

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-1, 'test 1');

EXECUTE AS USER = 'TestUser';

SELECT SESSION_USER, ORIGINAL_LOGIN();

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-2, 'test 2');
-- Msg 229, Level 14, State 5, Line 102
-- The INSERT permission was denied on the object 'AuditLog', database '...',
-- schema 'Auditing'.

EXEC [dbo].[AttemptDirectInsert]
@ImportantStuffID = -3,
@Column2 = 'test 3';
-- Msg 229, Level 14, State 5, Procedure AttemptDirectInsert, Line 115
-- The INSERT permission was denied on the object 'AuditLog', database '...',
-- schema 'Auditing'.

INSERT INTO [dbo].[ImportantStuff] ([Column2]) VALUES ('test 4');
-- Msg 229, Level 14, State 5, Line 114
-- The INSERT permission was denied on the object 'ImportantStuff', database '...',
-- schema 'dbo'.

EXEC [dbo].[ImportantStuff_AddData]
@ValueForColumn2 = 'test 5';
-- woo hoo!

SELECT * FROM [Auditing].[AuditLog];
-- Msg 229, Level 14, State 5, Line 122
-- The SELECT permission was denied on the object 'AuditLog', database '...',
-- schema 'Auditing'.

REVERT;

SELECT SESSION_USER, ORIGINAL_LOGIN();

SELECT * FROM [Auditing].[AuditLog];

EXECUTE AS USER = 'AuditUser';
-- Msg 15517, Level 16, State 1, Line 143
-- Cannot execute as the database principal because the principal "AuditUser" does not
-- exist, this type of principal cannot be impersonated, or you do not have permission.


UPDATE

Additional notes:

• As @Paul mentioned in his answer, this method (as shown) does not prevent privileged Users from doing direct inserts. However, it is still possible to block DML actions not initiated from code signed with the Certificate via a Trigger on the Audit table **. But that mostly prevents accidental inserts since anyone in the db_owner fixed database role should be able to disable the Trigger, and there is potentially at least 1 work around that could be used by someone in the db_datawriter fixed database rol

StackExchange Database Administrators Q#135717, answer score: 11