SQL Source Control 5 - db permissions
testuser1
Posts: 2 Bronze 1
Hi there,
I would like to version control stored procedures, and stored procedures only, in a database.
I tried to set up version control from SSMS with SQL Source control, to a Subversion repository. When I login to the database with sysadmin privilege, there is no problem. I was able to add/modify/commit all database objects , including stored procedures, into the version control system.
But when I use the privileges of a developer, I've got error: "The user does not have permission to perform this action"
Here are the privileges for the developer, which has been sufficient for the daily stored procedure development purpose,
db_ddladmin, db_datareader, db_execproc and public
On top of it, I added the following privileges as well based on my step by step troubleshooting:
1. grant VIEW ANY DEFINITION to "theDeveloper"
2. use tempdb
GRANT EXECUTE TO "theDeveloper"
3. Use master
GRANT ALTER TRACE TO "theDeveloper"
4. grant select on sys.sql_expression_dependencies to "theDeveloper"
Since the error message isn't specific to what permission is needed, I'm stucked !
I have checked the "SQL Source Control 5 Documentation" from online as shown in the following URL, the permission section, but the information isn't clear, is there any other documentation that you know that might be helpful?
https://documentation.red-gate.com/disp ... ermissions
Thanks in advanced
TestUser1
I would like to version control stored procedures, and stored procedures only, in a database.
I tried to set up version control from SSMS with SQL Source control, to a Subversion repository. When I login to the database with sysadmin privilege, there is no problem. I was able to add/modify/commit all database objects , including stored procedures, into the version control system.
But when I use the privileges of a developer, I've got error: "The user does not have permission to perform this action"
Here are the privileges for the developer, which has been sufficient for the daily stored procedure development purpose,
db_ddladmin, db_datareader, db_execproc and public
On top of it, I added the following privileges as well based on my step by step troubleshooting:
1. grant VIEW ANY DEFINITION to "theDeveloper"
2. use tempdb
GRANT EXECUTE TO "theDeveloper"
3. Use master
GRANT ALTER TRACE TO "theDeveloper"
4. grant select on sys.sql_expression_dependencies to "theDeveloper"
Since the error message isn't specific to what permission is needed, I'm stucked !
I have checked the "SQL Source Control 5 Documentation" from online as shown in the following URL, the permission section, but the information isn't clear, is there any other documentation that you know that might be helpful?
https://documentation.red-gate.com/disp ... ermissions
Thanks in advanced
TestUser1
Comments
Just to confirm that the documentation is correct, I set up a test user account on my local instance and gave it the permissions described in the documentation. This worked correctly and I was able to commit and retrieve stored procedures.
What is db_execproc - I don't think that's a standard role. What happens if you remove the user from that role? Is it possible that you might have a DENY permission somewhere that is overriding other permissions?
Redgate Software