Spat's WebLog (Steve Patrick)

When things go wrong...

FIM CM and SQL APIs– The EXECUTE permission was denied on the object

FIM CM and SQL APIs– The EXECUTE permission was denied on the object

  • Comments 3

 

OK – time for the random Monday morning blog.

So I was working on some FIM CM SQL API testing and was hitting an error calling 'externalSubmitSuspendRequest'

clip_image002

So – based on some limited past experience I thought “Hrmm no DB access, well I do recall that the clmApp role would fix this..” so I granted him this role.

clip_image003

Sure enough – ran it again and the account could get past that error, only to hit another.

C:\Spat.CLM.App\FIMSQLapis - suspend\FIMSQLapis\bin\Debug>FIMSQLapis.exe
{29a06d65-0157-5113-2891-44153e24ffff}
MSBaseCSP
Active
---------------

Unhandled Exception: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'externalSubmitSuspendRequest', database 'FIMCertificateManagement', schema 'dbo'.
   at System.Data.SqlClient.SqlConnection.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 FIMSQLapis.Program.Main(String[] args) in C:\Users\administrator.SPATFIM\Documents\Visual Studio 2008\Projects\FIMSQLapis - suspend\FIMSQLapis\Program.cs:line 168

Now - based on http://msdn.microsoft.com/en-us/library/bb468093(VS.85).aspx   it sounded like all that is needed was clmExternalAPI but it still does not create the external request. Continued errors.

Now sometimes you just need to talk to someone ( sometimes yourself -- out loud ) in order to walk through the logic. This person doesn’t even need to be familiar with the code , or technology – just a sounding board sometimes.

That someone wandered in at just the right time :

Someone: “Whatcha working on , how was your weekend?”
Me: “Weekend was good , working on this access denied error” ( show him SQL traces etc.. )
Someone: “Hrmm interesting. You sure he isn’t denied somewhere else?”
Me: “Huh? Denied no I didn’t den—oh wait. Hrmm… aha!! You sir are a genius!”

Looking at the stored procedure I was calling shows that the clmAp role has an explicit deny on execute. Kapow! Thx Odin.

clip_image004

Posting this here in case someone else is bitten by it.. now off to complete my suspend code….

Spatdsg

Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
  • You're welcome!

  • I just ran into this with reguards to some custom notifications.   have you found any issues with removing the deny?

  • I did not remove the deny as it is easy to work around..

Page 1 of 1 (3 items)