Red Gate forums :: View topic - [Error] The execute permission was denied ...
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 3
SQL Source Control 3 forum

[Error] The execute permission was denied ...

Search in SQL Source Control 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Agilone



Joined: 25 Feb 2013
Posts: 3
Location: Mountain View

PostPosted: Mon Feb 25, 2013 8:18 pm    Post subject: [Error] The execute permission was denied ... Reply with quote

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:

Quote:
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
Back to top
View user's profile Send private message
RajK



Joined: 02 Feb 2012
Posts: 58

PostPosted: Thu Feb 28, 2013 11:29 am    Post subject: Reply with quote

Many thanks for your e-mail and apologies for inconvenience caused.

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.
Back to top
View user's profile Send private message
Agilone



Joined: 25 Feb 2013
Posts: 3
Location: Mountain View

PostPosted: Thu Feb 28, 2013 6:22 pm    Post subject: Reply with quote

Hi,

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.

Quote:

You do not have permission to run 'SYS.TRACES'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.ServerParsers.DefaultTraceParser.#DNSc(IEnumerable`1 , SqlCommand )
at RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.ServerParsers.DefaultTraceParser.#gJUc(ICollection`1 , SqlDateTime )
at RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.ServerParsers.DefaultTraceParser.#Emb(ICollection`1 , SqlDateTime )
at RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.ServerParsers.DefaultTraceParser.GetAllEntries(String databaseName)
at #mlhb.#ny2b.#jiUc()
at RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.DatabasePollingManager.RunWithoutBackgroundPolling[T](Func`1 func)
at #GWeb.#WXM.#P6Uc(Func`1 )
at #mlhb.#ny2b.#xFV()
at #mlhb.#ny2b.#aQU(ICollection`1 #vfX, DatabaseObjectIdDictionary`1 #xfX, SourceControlOperation #oTc, ICompareDifferences #Ncsc)
at #mlhb.#RLZc.#L8c(SourceControlOperation #oTc, Boolean& #hD3c)
at #mlhb.#ry2b.#zYM(IDifferenceSelector #xrWb, ICancellableOperationStatus #KHc, IReadOnlySourceControlServerCallBacks #VHc, ToCommitChangeSet& #Ocsc)
at #GWeb.#VXM.#mYM[#NrPb](Func`2 , ICancellableOperationStatus , IReadonlySourceControlServerCallBacks , IDifferenceSelector , Action )
at #GWeb.#VXM.#jYM(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector )
at #GWeb.#4Ec.#7Jc(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector )
at #GWeb.#4Ec.#y2.#QB7b(ICancellableOperationStatus )
at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.#u3.#k5f()
at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperationBase.InvokeWithTracker(String featureUsageKey, Action action)
at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.Invoke()
at #eEc.#Qlg.Invoke()
at #JLc.#PLc.#Jfb.#EJf()
at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.Do(Action , Predicate`1 , Boolean )
at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.DoWithObviousExceptionsRethrowAll(Action action)
at RedGate.SQLSourceControl.CommonUI.Forms.ErrorDialog.DoWithObviousExceptionsRethrowAll(Action action)


Thanks
Back to top
View user's profile Send private message
RajK



Joined: 02 Feb 2012
Posts: 58

PostPosted: Sun Mar 03, 2013 2:06 pm    Post subject: Reply with quote

To use SQL Source Control, users need following permissions to:

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>
Back to top
View user's profile Send private message
Agilone



Joined: 25 Feb 2013
Posts: 3
Location: Mountain View

PostPosted: Mon Mar 04, 2013 6:16 pm    Post subject: Reply with quote

Thanks for this detailed answer.

However, we also all have the "ALTER" permission and same error.

Any workaround ?
(sysadmin is not acceptable...)

Thank you for your time
Back to top
View user's profile Send private message
JJB7



Joined: 20 Jul 2012
Posts: 8

PostPosted: Thu May 16, 2013 10:59 am    Post subject: Reply with quote

I've come across a similar issue in my team, stumbled on this post and have fixed my issue as a result, thanks for the help. We've had to source control some databases in production as we don't have development / uat versions of them....yet. This issue doesn't arise in our dev area since my guys all have sa access to that box.

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".
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group