System versioned tables permissions

Question

Post reply

System versioned tables permissions

as_1234

SSCrazy

Points: 2859
More actions
April 20, 2023 at 11:58 am

#4176808

I have an issue with these permissions which I don't understand.
When I add a user to a database containing some system versioned tables and look at their effective permissions, using SSMS, on tables I get the following:
Ordinary tables - No permissions which is what I would expect
System versioned history tables - An insert permission which I'm not expecting
The user isn't part of any groups so I don't think it's coming from a group. It appears to be somehow related to the System Versioning.
Has anyone seen similar or know the cause? I'm wondering if it's an SSMS bug or something related to system versioned tables which I've missed in the documentation.
I think the SSMS dialog box is based on a query. Is that query available somewhere so I can try and understand why it's showing the insert permission
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

How are you adding the user? Do they have roles? I tried this in SQL 2019, and with a system versioned table, if I create user from login, I don't see any permissions added.

as_1234 SSCrazy Points: 2859 More actions · Answer 3

Sorry for my slow reply.

I created the user from login. At the server and database level they are only in the public role and I can't see anything relevant allocated to that role.

I tried creating the login both through SSMS and:

create user [username] for login [username]

and got the same results.

I'll try some more combinations of databases/servers e.t.c. and see if I can replicate the issue.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

as_1234 wrote:

Sorry for my slow reply.
I created the user from login. At the server and database level they are only in the public role and I can't see anything relevant allocated to that role.
I tried creating the login both through SSMS and:
create user [username] for login [username]
and got the same results.
I'll try some more combinations of databases/servers e.t.c. and see if I can replicate the issue.

When you ran the code, what error message did you get?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2859 More actions · Answer 5

Sorry for taking so long to reply.

I don't get an error from that command.

I did some testing on a test system. I created a table as follows:

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , ValidFrom DATETIME2 NOT NULL
  , ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory
    ON DepartmentHistory;
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
    ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

The code comes from here https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver16

When I add a user to the database and have a look at the effective permissions there are no permissions on the department table, which is what I expect, however there is an insert on the departmenthistory as shown here:

Permissions

I don't understand why that insert permission is there?

The test system is SQL Server 2017 with SSMS 19.0.2

The system that I noticed the issue on originally is 2016 with a different SSMS version so it doesn't seem to be linked to Server or SSMS versions. It seems to be consistent to the history tables.

This reply was modified 1 year, 6 months ago by as_1234.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

I think the answer is "because". 😉 I've not checked privs on system versioned temporal tables before but it may very well be that if the user has write privs on the database, the system versioned tables will be updated with INSERT privs for the user. It may be a part of the one of the DB_ level roles you assigned for the user.

That's my "guess". To know more, you'd have to do a deep dive on system versioned temporal tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

Yeah, tend to agree with Jeff and I'd be guessing as well.

One thing I thought of, if you script out the entire db from SSMS, everything checked. what permissions appear?

as_1234 SSCrazy Points: 2859 More actions · Answer 8

All,

Thanks for your replies.

Scripting out the database is a good idea, I'll try that.

I'll carry on investigating and if I find an answer or anything else that might be useful I'll add another post.

as_1234 SSCrazy Points: 2859 More actions · Answer 9

Just an update in case it's useful to anyone:

Scripting the database doesn't show any relevant permissions.

If you unversion the table the insert permission isn't shown in SSMS. If you re-version table it reappears. I've tried it in a few versions of SSMS and get the same results.