Temporal tables are one of those new (2016+) cool features that recently came across my desk. Basically, a temporal table is a combination of auditing columns (createdate) and a history table.

Quick example using code from BOL.

CREATE TABLE Department   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)     
)   
WITH    
   (   
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory)   
   )   
;
INSERT INTO Department (DeptID, DeptName, ManagerID, ParentDeptID) VALUES (1,'test',4,2);
UPDATE Department SET ManagerID = 2;
SELECT 'Primary Table', * FROM Department;
SELECT 'History Table', * FROM DepartmentHistory;

The reason they came across my desk was (yes, you guessed it) permissions. And, working with minimal permissions here, if you have CREATE TABLE, ALTER SCHEMA then you can create a temporal table.

CREATE LOGIN CreateTemporalTable WITH PASSWORD = 'blah',CHECK_POLICY = OFF;
CREATE USER CreateTemporalTable FROM LOGIN CreateTemporalTable;
GRANT CREATE TABLE TO CreateTemporalTable;
GRANT ALTER ON SCHEMA::dbo TO CreateTemporalTable;
GO
EXECUTE AS USER = 'CreateTemporalTable';
GO
CREATE TABLE Department   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)     
)   
WITH    
   (   
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory)   
   )   
; 

No problems!

But what if I want to turn versioning off?

ALTER TABLE Department SET (SYSTEM_VERSIONING = OFF);

Msg 13538, Level 16, State 3, Line 6

You do not have the required permissions to complete the operation.

Well, that’s not good. What permissions do I need exactly? Well, again, according to BOL I need CONTROL on the table and its history table. For those that don’t know CONTROL is the top level permission for any object. You can do anything at all with it.

-- REVERT first if you are still executing as CreateTemporalTable
GRANT CONTROL ON Department TO CreateTemporalTable;
GRANT CONTROL ON DepartmentHistory TO CreateTemporalTable;
GO
EXECUTE AS USER = 'CreateTemporalTable';
GO
ALTER TABLE Department SET (SYSTEM_VERSIONING = OFF);

FYI Adding SYSTEM_VERSIONING or the temporal columns to an existing table also requires CONTROL. Which brings up an interesting problem. I can grant CONTROL to a developer on a table that already exists, but if I have a developer working on a project then they are going to need to be able to create, modify, add and remove Temporal properties. Which is going to be a problem. How do I grant someone permissions on a table that may not exist yet? db_owner will work but is far from least privilege.

So my personal preference? CONTROL on the schema. Generally, this is going to be dbo but if you are working with multiple schemas you probably need to grant it to all of them. That is a fair amount of privilege, and I wouldn’t grant it beyond a development or in rare cases test environment, but it’s not really anymore/worse than db_ddladmin.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL