RG_WhatsChanged_v4 in tempdb

chris_godfree

After successfully working on a database with SQL Source Control I'm suddenly faced with an error when I try to commit a change: The EXECUTE permission was denied on the object 'RG_WhatsChanged_v4', database 'tempdb', schema 'dbo'.

I haven't been specifically granted permission on this stored procedure before so I'm wanting to find out if it is necessary to do so and also what has changed to suddenly cause this?

Thanks, Chris

Andrea_Angella

Hi Chris,

Have you recently upgrade to SQL Source Control 5? What's version are you currently using?

Yes, the permission is required.

You can find more information and the solution here:
https://forum.red-gate.com/discussion/80205/execute-permissions-error
https://documentation.red-gate.com/display/SOC5/Object+changed+by+Unknown

Hope this help,
Andrea

chris_godfree

Hi Andrea,

I'm using version 5.6.1.5788

Will all users specifically require permission to execute that stored procedure? All that is in the link you quoted is ALTER TRACE and VIEW SERVER STATE permissions to access the default trace but this SP sounds like something different.

I have also seen that this SP (and the dbo.RG_AllObjects_v4 table) does not exist in other SQL Server instances where there is a database under source control - in what circumstances does the table and SP get created?

Thanks,

Chris

Andrea_Angella

Hi Chris,

Those objects are created by SQL Source Control only if you have the Global Option "Indicate changed objects in the Object Explorer and update indicators every X seconds" enabled. You can find it in the setup tab. If that option is disabled you won't find those objects in your tempdb database.



The code that update the indicators and figure out "who changed what" is contained in that stored procedure so it should be the same issue as the one I linked. The permission is needed in order to access the default trace.

I believe every user who use SQL Source Control must have the permission enabled if you want to use this functionality.

Regards,
Andrea

chris_godfree

Hi Andrea,

That's not what I'm seeing: I've currently got a few databases on different SQL instances connected to SQL Source Control and they all have the option you indicated set (looks like it is set as standard) but only one of the SQL instances has those objects in the tempdb.

Anyway, we can work around the error by giving execute rights on the SP in tempdb if necessary - perhaps one to publish in your FAQs?

Chris

Andrea_Angella

Hi Chris,

Strange. SQL Source Control does not create those objects immediately but only after the amount of time specified in the textbox in the global options (from my screenshot that is 23 seconds). I tested it with the latest version of the product.

There are two possibilities that might explain what you are seeing: tempdb has being reset after a server restart and you haven't run SQL Source Control on it afterwards or you have run SQL Source Control but checked the presence of those objects before the update script runt (you might have an higher value, it can be set up to 1 hour).

Anyway, yes giving execute rights is the best thing to do here.

We will consider adding this information to our documentation.

Thanks,
Andrea