[Error] The execute permission was denied ...

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace, andy.campbell.smith

[Error] The execute permission was denied ...

Postby Agilone » Mon Feb 25, 2013 8:18 pm

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:

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
Agilone
 
Posts: 3
Joined: Mon Feb 25, 2013 8:11 pm
Location: Mountain View

Postby RajK » Thu Feb 28, 2013 11:29 am

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.
RajK
 
Posts: 58
Joined: Thu Feb 02, 2012 8:21 am

Postby Agilone » Thu Feb 28, 2013 6:22 pm

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.


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
Agilone
 
Posts: 3
Joined: Mon Feb 25, 2013 8:11 pm
Location: Mountain View

Postby RajK » Sun Mar 03, 2013 2:06 pm

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>
RajK
 
Posts: 58
Joined: Thu Feb 02, 2012 8:21 am

Postby Agilone » Mon Mar 04, 2013 6:16 pm

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
Agilone
 
Posts: 3
Joined: Mon Feb 25, 2013 8:11 pm
Location: Mountain View

Postby JJB7 » Thu May 16, 2013 10:59 am

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".
JJB7
 
Posts: 8
Joined: Fri Jul 20, 2012 11:39 am


Return to SQL Source Control 3

Who is online

Users browsing this forum: No registered users and 0 guests