sql server programming: Details: SQL Server 2000, SP3, NT 4.0 Server
Application (VB6 ADO 2.6, 2.7)

The application I've written works as expected when using Windows Integrated security from an Administrator's logon. ANY IDEAS?

Database read/write to individual tables/columns etc., access to SPs and views work fine when working from a SQL login (instead of a Windows Integrated login (this is the mode the application is expected to work from in production.) A view is creating the error below, though. The view uses a trigger to populate dependent values in related tables.

EXCEPTION: The following error occurs ONLY when using the SQL security, there must be a security setting that is implicitly granted by Windows Integrated Security (Administrative level) that has to be explicitly configured using SQL security. WITHOUT CHANGING CODE, THIS ERROR GOES AWAY WHEN USING WINDOWS INTEGRATED SECURITY (ADMINISTRATIVE LEVEL.)

Cannot insert or update columns from multiple tables.
Time='01/22/04 23:34:23' App='CAGUI:0.5.58' ADO-version='2.7' Computer='SV-DEVSERVER1'
Method: frmCProfile1.Form_Unload
Number: -2147467259 = &H80004005 = vbObjectError - 245755 = ERRMAP_APP_FIRST - 250953
Source: Microsoft OLE DB Provider for SQL Server
Description: Cannot insert or update columns from multiple tables.

We have the basics set for all the tables/views/stored procedures needed by the application. That is, the following are set:

Roles:
Public (duh)
db_owner (just to see if it would help, it didn't)
db_datareader
db_datawriter

(we tried setting every role simultaneously, it didn't work, either)

We have also set PERMIT access to every table, SP, and view in the database.

What rights do you have associated with the SQL Server user? Running as an
Administrator means that you are running as 'sa' within SQL Server and can
therefore do everything. If you have not given access to read\write\modify
to the objects in your DB for the SQL user you will get errors.

HTH

Semper Fi,
Red

[quoted text, click to view]
views work fine when working from a SQL login (instead of a Windows
Integrated login (this is the mode the application is expected to work from
in production.) A view is creating the error below, though. The view uses
a trigger to populate dependent values in related tables.
[quoted text, click to view]
there must be a security setting that is implicitly granted by Windows
Integrated Security (Administrative level) that has to be explicitly
configured using SQL security. WITHOUT CHANGING CODE, THIS ERROR GOES AWAY
WHEN USING WINDOWS INTEGRATED SECURITY (ADMINISTRATIVE LEVEL.)
[quoted text, click to view]

I would next review the ownership chain since the problem is with '...insert
or update columns from multiple tables'

Maybe try dbo.sp_column_privileges on the tables upon which insert\updates
are failing.

HTH

Semper Fi,
Red

[quoted text, click to view]