Time-bound role membership

A user’s membership in a role can be limited to a period of time. The period is written as an extended property of the login, and Querona keeps the membership in line with it: the user is added to the role when the period starts and removed when it ends, so nobody has to remember to revoke the role.

The period can be set from the portal, on the same screens that assign roles, or written over SQL as an extended property of the login.

Setting a period in the portal

The screens that assign roles also carry the period. In ADMINISTER ‣ User management edit an account and open the roles step: every role the account can hold is listed with the period its membership is limited to. The same column appears on the members step of ADMINISTER ‣ Role management, seen from the role’s side.

Use Grant temporarily on a row to limit one membership, Change period to move the dates of one that is already limited, and Grant again to repeat a grant whose period has ended. Above the list, Bulk grant roles temporarily gives several roles the same period in one gesture - Bulk add members temporarily on the role screen does the same for several accounts. Whatever is ticked in the list comes through to that screen already ticked.

The editor asks for the two bounds under their own headings. Leave From now selected for a period that is already in force, or No end date for one that has no end; at least one bound must be given. Dates and times are collected separately, read and written in the time zone of the browser, which the information mark beside each heading names. A sentence under the fields states what will be stored, and a second one says what the end means: access stops at the instant named, not at the end of that day.

A period takes effect when it is saved, not when the wizard is finished: saving it can grant the role at once, or take it away and close that account’s open sessions. The editor therefore saves on its own, and before removing a period it says what the removal will do - revoke a role now, cancel one that was only scheduled, or clear a period that has already ended.

Rows that cannot be ticked

A membership that a period owns cannot be ticked or unticked by hand: the period decides it, and the reason appears when the pointer rests on the row. Change or remove the period instead. A row is also fixed for the public role, which every account holds, for accounts Querona owns itself, and for an account whose roles the signed-in user has no permission to change. Setting a period is a change to the role and answers to ALTER ANY ROLE, while ticking the membership is a change to the account and answers to ALTER ANY LOGIN, so a row can offer one and not the other.

Periods are shown read-only on the roles tab of an account and the members tab of a role in the lists themselves. Memberships whose period has ended are grouped and collapsed there, so a finished period does not hide what is in force.

Granting a role for a period over SQL

The property is named Querona_TimeBoundRole:<role id> and its value is the period, written as <from>/<to>:

EXEC sp_addextendedproperty
    @name = N'Querona_TimeBoundRole:100',
    @value = N'2026-09-01T00:00:00Z/2026-12-31T00:00:00Z',
    @level0type = N'USER',
    @level0name = N'jkowalski';

The number after the colon is the role’s id, which sys.server_principals reports:

SELECT principal_id, name FROM sys.server_principals WHERE type = 'R';

Both bounds are instants with an explicit time-zone offset, and the period includes its start and excludes its end. Either bound may be left out - /2026-12-31T00:00:00Z means “until”, 2026-09-01T00:00:00Z/ means “from now on” - but not both. A period that has already ended is refused.

If the user is already a member of the role, the period must be open at the moment it is written; a period that starts later is refused, because storing it would take the existing membership away. Remove the member first, or write a period that is already in force.

Changing and revoking

sp_updateextendedproperty replaces the period, and sp_dropextendedproperty removes it. Removing the period removes the membership with it: as long as the property exists, it - and not the role editor - decides whether the user is a member.

EXEC sp_dropextendedproperty
    @name = N'Querona_TimeBoundRole:100',
    @level0type = N'USER',
    @level0name = N'jkowalski';

While a period is in force, ALTER SERVER ROLE ... ADD MEMBER and DROP MEMBER for the same user and role are refused whenever they would contradict it. Shorten or remove the period instead.

When a period ends

Querona removes the membership and closes every open session of that user, so a session that started while the role was still held cannot keep using it. The removal is recorded in the audit log like any other membership change, with the internal system account as its author. Writing, changing or removing the period itself is recorded separately, under the account that did it, whether it came from the portal or over SQL.

Permissions

Writing, changing or removing a period takes the same permission as adding the user to that role by hand: ALTER ANY ROLE, and for a built-in role, membership of that role as well.

Reading the periods in force

SELECT r.name AS role_name,
       l.name AS login_name,
       CAST(p.value AS NVARCHAR(MAX)) AS validity_period
FROM sys.extended_properties p
JOIN sys.server_principals l ON l.principal_id = p.major_id AND l.type = 'S'
JOIN sys.server_principals r ON r.type = 'R'
    AND p.name = 'Querona_TimeBoundRole:' + CAST(r.principal_id AS NVARCHAR(11))
WHERE p.class = 4;

How often the periods are checked

Besides acting the moment a period is written, changed or removed, Querona re-checks every period on a schedule set by Engine settings (“Time-bound role membership reconcile interval”), one hour by default and never shorter than one minute. A period therefore takes effect within that interval of its start or end, and every period is re-checked when the engine starts, so one that ended while the engine was down is enforced before the first client statement.