April 12, 2019 at 12:02 am
Hi,
I have a solution that allows the application team to drop and recreate tables (with the same name) in the database (this feature is necessary and wanted) The problem is I lose permissions set on the table when they perform this operation. The only permissions that I have set at the table level is grant View Change Tracking for certain users.
Is it possible to grant View Change Tracking permission on all tables without applying the permission on each table ? In other words, at the database level.
If it is not possible, is there a creative way I could let the user to grant itself View Change Tracking permissions? For example, a pre-created Stored Procure that could be executed by that said user?
Thank you
April 12, 2019 at 7:49 pm
If you grant users at the top level Security\Logins, Under User mapping, select the DB to map and grant the access as db_datareader (read only permission to the DB). Regardless if the application would delete and re-create a table. The user has access as data reader to the assigned mapped DB. Hope this helps.
April 12, 2019 at 9:00 pm
OK I am clear with what you need now. The only way that you can do this in one shot is to assigned the rights View Change Tracking permission on all tables for the user account, before hitting OK use the script at the top of the window and script out the process. Create a SP or create a SQL job with it and use it to run on demand or automate when needing to re-set the permission. This will allow you to do this in one step process. Hope this helps.
April 15, 2019 at 12:22 am
Hi
I think you understood my case correctly. So you believe the only way to achieve that is create and SP or create and run a scheduled SQL job ?
Thank you
April 15, 2019 at 1:56 am
Yes since the view track changes is at the dB level and the specific needs is that. If you are able to give read access at the sql level then there is no need for it. I would script out the rights initially and create a SP and job schedule to automate. You can also run the job on demand as needed too.
March 18, 2021 at 9:46 pm
Just stumbled across this while googling Change Tracking so I thought I would add that you can grant View Change Tracking at the schema level.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply