system_versioning = on/off permissions

Question

system_versioning = on/off permissions

as_1234

SSCrazy

Points: 2859
More actions
March 14, 2023 at 6:31 pm

#4160695

All,
I would like to give a user permission to set system_versioning on or off on a table. I know that one way to do this is to give them control permission on the table. However I think that also gives them the ability to grant permissions and I don't think I can set a 'deny' on their ability to grant permissions?
If I understand correctly ownership chaining only applies to DML so creating a stored procedure which makes the change and allowing the user to execute that won't work?
I thought about creating a signed stored procedure which sets system_versioning on or off and giving the user the ability to execute that but I don't think that will help as that is also DML only? It also seems a complicated solution for this issue.
Is there a way of setting this up that I am missing?
Thanks

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

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

If the proc as an appropriate "EXEC AS" clause, I would think it would work.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

as_1234 wrote:

All,
I would like to give a user permission to set system_versioning on or off on a table. I know that one way to do this is to give them control permission on the table. However I think that also gives them the ability to grant permissions and I don't think I can set a 'deny' on their ability to grant permissions?
If I understand correctly ownership chaining only applies to DML so creating a stored procedure which makes the change and allowing the user to execute that won't work?
I thought about creating a signed stored procedure which sets system_versioning on or off and giving the user the ability to execute that but I don't think that will help as that is also DML only? It also seems a complicated solution for this issue.
Is there a way of setting this up that I am missing?
Thanks

Are you talking about System Versioned Temporal Tables here?

--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 3

ScottPletcher wrote:

If the proc as an appropriate "EXEC AS" clause, I would think it would work.

Thank you for the suggestion. Sorry I forgot to mention this possibility in my original post. I am trying to avoid any type of impersonation.

Jeff Moden wrote:

Are you talking about System Versioned Temporal Tables here?

Thanks for your reply and yes I am.

This reply was modified 1 year, 8 months ago by as_1234. Reason: Trying to fix quote tags

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

With the proviso that I don't know the exact situation...

The purpose of such a table is to audit what's happening to the data, no matter what. Are you absolutely sure that no one is going to "cook the books" while it's off?

--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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Can you explain further why you want to allow this user to enable/disable system versioning? As far as I am aware - the only reasons to temporarily disable versioning is to delete or truncate data or to switch data in/out of the current or history table.

There really are not any other reasons to turn it off - so why do they need to turn it off?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

as_1234 SSCrazy Points: 2859 More actions · Answer 6

Thanks for your replies.

In this case there are valid reasons for turning it off. We don't need an unchangeable audit.

I am also looking at using other methods for creating a history table rather than using system_versioning.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

Jeffrey Williams wrote:

Can you explain further why you want to allow this user to enable/disable system versioning? As far as I am aware - the only reasons to temporarily disable versioning is to delete or truncate data or to switch data in/out of the current or history table.
There really are not any other reasons to turn it off - so why do they need to turn it off?

I believe you also need to disable system versioning if you want to modify the table (add a column).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

ScottPletcher wrote:

Jeffrey Williams wrote:
Can you explain further why you want to allow this user to enable/disable system versioning? As far as I am aware - the only reasons to temporarily disable versioning is to delete or truncate data or to switch data in/out of the current or history table.
There really are not any other reasons to turn it off - so why do they need to turn it off?
I believe you also need to disable system versioning if you want to modify the table (add a column).

Not true - you can add or drop columns without disabling system versioning. The only operations that require disabling system versioning are:

Delete or Truncate data history table
Removing data from current table without versioning
SWITCH OUT from current table
SWITCH IN into the history table

The first one falls under data retention - and there are several methods available including setting a retention policy. The other scenarios do require disabling system versioning, but those can be encapsulated in a stored procedure with appropriate execute rights - and in fact, all of the above items can be done through an appropriately permissioned procedure without having to grant additional rights to an individual or group.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

as_1234 SSCrazy Points: 2859 More actions · Answer 9

All,

Apologies for my slow reply. Thank you for your help.