Issue

SQL Server Service based SmartObject signature changes when SQL server security is applied and some of the parameters of the SmartObject change from optional to mandatory.

Symptoms

You use OOB SQL Server Service Broker to expose stored procedures as SmartObjects. Stored procedures reside in a separate custom SQL database. You use a custom defined SQL database security role for the K2 service account for this database.

Once you remove db_owner role membership for the K2 service account and apply a custom role, some of the properties of the stored procedure based SmartObjects change from optional to mandatory (required).

It looks like it affects stored procedures with parameters. Product documentation states the following:

"To discover the Stored Procedures, the K2 Service Account needs View Definition permissions on stored procedures in the targeted SQL database."

But to run them Execute permission is also required. In addition to this in "Troubleshooting the SQL Server Service Type" K2 documentation section we can see the following about Permission Levels:

"When creating a new SQL Server instance, if the database is on a different server from the SQL Server where the K2 Database resides, then a linked server object is created. This requires elevated permissions (i.e. sysadmin). If the account does not have these permissions, then it may only partially work and cause issues. We recommend that the linked server object is created in SQL prior to creating the new instance."

For a simple database, the permissions to create the instance are: data reader; data writer; and execute. However, depending on the nature of the stored procedures, DBO permissions may also be necessary. Granting DBO permissions can be a problem for very large databases, due to the broker and how it scans objects to create the instance. For very large databases you need to limit the permissions the account has to only the objects you want to create (otherwise create and refresh of service instance may take extremely an long time).

You can elevate permissions temporarily to create the instance and then change them back to limited permissions for runtime.

Resolution

Ensure that the K2 service account has the following rights on the target database/stored procedures:

  • View Definition
  • Execute

Depending on the nature of stored procedure, higher level of permissions may be also required - see details above.