User 'iboImport' does not have permission to run DBCC CHECKIDENT

Read this (from Developer’s Guide):

  Sql Server Identity Id Generation

The default DevForce processing for SQL Server tables with an identity column uses two SQL Server features which require that the connected user have elevated privileges:

·         DBCC CHECKIDENT, so we can re-set the curren seed for the next identity value.

·         SET IDENTITY_INSERT, so we can specify the Identity column value on insert.

When DevForce issues those commands, it must be logged in either as the owner of the tables or as a member of the sysadmin, db_owner or db_ddladmin roles.

This shouldn’t be an issue in an n-tier deployment where the DevForce process issuing these commands is running in a secure environment on a host machine.

Two-tier (“Client/Server”) applications may be ok too, especially if they execute on a secure LAN and the connection string specifies Windows authentication.

Some applications don’t fit these criteria. They are two-tier and n-tier, for some reason, is not a current option. Perhaps they can’t use Windows authentication and must specify a username and password in the connection string. No one wants connection credentials with administrator rights floating about.

Perhaps the application can switch from Identity Ids to a table-drive custom id design. That won’t be an option if there are legacy applications referencing the same data.

Identity Id Generation Via Stored Procedure

There is one remaining alternative: Identity Id generation using a special stored procedure shipped with the DevForce product. There are two conditions:

1.          The database must be on SQL Server 2005; this technique does not work in 2000.

2.          You must be willing and able to install the stored procedure

The stored procedure inserts behave a little differently than normal inserts.

Normal inserts are submitted as a sequence of individual insert statements. This stored procedure first stores the inserts in a temp table and adds the temp table to the permanent table. There are consequences to this difference.

·         There could be a slight performance penalty with insert-heavy applications in some server/database configurations due to the use of temp tables and stored procedure calls.

·         If the insert fails (e.g. a uniqueness constraint violation), we can’t tell which insert row caused it to fail. SQL Server doesn’t report the offending row when it inserts the temp table rows into the permanent. All the DevForce persistence layer can do is raise a PersistenceIdentityInsertException.

Install the stored procedure

ð      Locate the Sql script file usp_IdeaBlade_SqlIdentityGenerator in the DevForce\SampleCode\Sql folder.

ð      Open it in a query window in Microsoft SQL Server Management Studio.

ð      Connect to SQL Server with a login that has administrator or db owner privileges

The stored procedure uses the ‘EXECUTE AS’ clause to run the sproc with the elevated permissions of the creating user. You are that creating user so you must have the privileges necessary to run the DBCC CHECKIDENT and SET IDENTITY_INSERT commands for the target database. That means administrator or db owner privileges.

ð      Confirm that SQL Server Manager is pointing to the target database.

ð      Execute the sql script to create the stored procedure.

ð      Grant “execute” permission on the procedure to the application’s database user.

The command looks like this

grant execute on usp_IdeaBlade_SqlIdentityGenerator to appuser

where "appuser" is the userid specified in the connection string.

We do not need – and do not want – a separate database user (and password) for each application user. This could severely interfere with connection pooling (and scalability) if we ever decide on an n-tier deployment. It is more useful to track user activity through the application login process and by capturing the logged-in user’s id in creation and modification audit columns of the data tables.

Set the option for the database key in the IdeaBlade Configuration File

Now that we’ve installed the stored procedure, we must tell our DevForce application about it. We do that in the IdeaBlade Configuration File (IdeaBlade.ibconfig) by adding a dedicated option tag to the RdbKey associated with the database.

ð      Add the “UseSqlIdentityProc” option element to the RdbKey for the target database.

We can add it in XML using a text editor.

<option>UseSqlIdentityProc</option>

Many find it more safe and convenient to add this option with the IdeaBlade Configuration Editor

ð      Locate and open the Options for the RdbKey of the target database

ð      Enter the string "UseSqlIdentityProc".

Either way, be sure to enter this string exactly.

A complete specification of an RdbKey might look like this:

<rdbKey name="Default" databaseProduct="SqlServer">

  <connection>Provider=SQLOLEDB.1;Persist Security Info=False;

   Initial Catalog=IdeaBladeTutorial;Data Source=.;

   User Id=appuser;password=appuserpwd

  </connection>

  <probeAssemblyName>Model</probeAssemblyName>

  <option>UseSqlIdentityProc</option>

</rdbKey>