SQL Server User for SQL Source Control

BSlashW

I'm attempting to create an SQL Server User to use with SQL Source Control with the limited amount of rights to Commit Changes to Source Control but not alter the database under source control as would be required with Get Latest Changes.

I know that the minimum permissions are as follows:

• View Definition for all objects
• Permission to make all the listed changes in a commit or get latest (Intentionally excluded for the time being)
• Permission to read the default trace
• If you have encrypted stored procedures, to commit or retrieve them you need sysadmin permissions (not required because we do not have any encrypted stored procedures)
• Execute and Alter permissions on TempDB (I think this is the one I'm struggling with)
  

It properly identifies changes but I receive the error message "SQL Source Control can't tell who made these changes because the current user does not have EXECUTE permission on the stored procedure RG_WhatsChanged_v4.  You can still commit the changes as normal"

I have made multiple attempts to allow the user access to execute the stored procedure at the procedure and database level with no luck.  I continue to receive the same error.

The user has a server role of public.

Some of my attempts have included the below:

USE tempdb

GO

GRANT EXECUTE TO <user name>;

USE tempdb

GO

GRANT EXECUTE ON <database>.<schema>.RG_WhatsChanged_v4 TO <user name>;

What can I be missing?

Tianjiao_Li

I still have few more tests before I can confirm. I thought you might be interested to try it as well in the meanwhile.

I think giving the bellow permissions might help:

Users need the ALTER TRACE and VIEW SERVER STATE permissions to access the default trace. To add these permissions, your system administrator needs to go to each user's login (Security > Logins on the server level) and enable them under Securables.

If you are using the default Changelog database, you will also need EXECUTE and ALTER permissions on tempdb. Without those permissions, SQL Source Control may still show a warning on the commit page that you don't have access to the default trace.

https://documentation.red-gate.com/soc/troubleshooting/object-changed-by-unknown

Tianjiao_Li

I've got the warning disappeared by making sure the user has Execute and Alter permissions on TempDB. To grant those, please follow the steps on bellow screenshot:

BSlashW

Thank  you, those directions are very clear and the errors I was receiving are no longer showing.