[Error] The execute permission was denied ...
Agilone
Posts: 4
Hi,
First and foremost, thanks for this tool, it makes it much easier to work with SQL.
However, I am having trouble to commit a small number of databases.
I have the following error message when trying to commit some changes:
Which is strange because all users have rights to execute this?
Is there a script I have to execute on my side to fix this?
(I run Source Control version 3.10)
Many thanks for your help
First and foremost, thanks for this tool, it makes it much easier to work with SQL.
However, I am having trouble to commit a small number of databases.
I have the following error message when trying to commit some changes:
The execute permission was denied on the object "RG_Procversion", database 'tempdb', Schema dbo
Which is strange because all users have rights to execute this?
Is there a script I have to execute on my side to fix this?
(I run Source Control version 3.10)
Many thanks for your help
Comments
The permissions error is likely to be a problem with access to TempDb. You can grant the execute permission on tempdb like this:
use [tempdb]
GRANT EXECUTE
TO
sql_source_control_users
You'll need to replace 'sql_source_control_users' with a role that represents all of the users that need this permission (for example, if you have a database developers role they should use that).
Hope this solves the issue you are having.
I made sure that everyone has execute permission on tempdb. Which is now the case.
I am now getting another error message:
System.Data.SqlClient.SqlException: The user does not have permission to perform this action.
Thanks
execute functions on tempdb
alter databases linked to source control
These permissions are usually granted by default.
Permission to execute functions on tempdb:
You can test if you have this permission by running this SQL (where <dbname> is your database name):
SELECT HAS_PERMS_BY_NAME('tempdb', 'database', 'EXECUTE')
If the returned value is 1, you have this permission.
If needed, administrators can grant this permission with this SQL (where <user> is a user or role):
use tempdb
GRANT EXECUTE TO <user>
Permission to alter databases linked to source control:
You can test if you have this permission by running this SQL (where <dbname> is your database name):
SELECT HAS_PERMS_BY_NAME('<dbname>', 'database', 'ALTER')
If the returned value is 1, you have this permission.
If needed, administrators can grant this permission with this SQL (where <user> is a user or role):
USE <dbname>
GRANT ALTER TO <user>
However, we also all have the "ALTER" permission and same error.
Any workaround ?
(sysadmin is not acceptable...)
Thank you for your time
What I would add for anyone who stumbles across this, is that since tempdb is recreated every time SQL restarts, you will need to apply the required permission every time SQL is restarted. Easiest way is to have the permission applied by a SQL Agent job with a schedule of "Start automatically when SQL Agent starts".