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


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’


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.


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

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   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.


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


Comments (3)

  1. Odin says:

    You're welcome!

  2. Script Kitty says:

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

  3. SpatDSG says:

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