gAznpMDF

1SET NOCOUNT ON;
2USE tempdb;
3IF OBJECT_ID('dbo.t') IS NOT NULL
4BEGIN
5    DROP TRIGGER t_trig;
6    DROP TABLE dbo.t;
7END
8GO
9CREATE TABLE dbo.t
10(
11    ID INT NOT NULL
12    , ID2 INT NULL
13);
14GO
15CREATE TRIGGER t_trig ON dbo.t
16WITH EXECUTE AS OWNER
17AFTER INSERT
18AS
19BEGIN
20    UPDATE dbo.t
21    SET ID2 = ID
22    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.ID = dbo.t.ID);
23END
24GO
25CREATE LOGIN tLogin WITH PASSWORD = 'QWERFsdf23454%';
26CREATE USER tLogin FROM LOGIN tLogin WITH DEFAULT_SCHEMA = dbo;
27GRANT INSERT ON dbo.t TO tLogin;
28DENY UPDATE ON dbo.t TO tLogin;
29
30EXECUTE AS USER = 'tLogin';
31SELECT SUSER_SNAME();
32INSERT INTO dbo.t(ID) VALUES (1);
33REVERT
34
35SELECT *
36FROM dbo.t;
37
38DROP USER tLogin;
39DROP LOGIN tLogin;