January 3, 2008 at 1:53 pm
[font="Tahoma"]I am having difficulty getting the triggers I have created to work. The triggers work fine when I manipulate the data from enterprise manager, but I receive an error when the trigger fires after manipulating the data through a VB6 front-end using an application role that is created in the production database.
The triggers are quite simple. When there is any kind of update or delete transaction, the previous version of the record is inserted into an identical table in a history database. As I mentioned, when I update the record in enterprise manager, the trigger works as expected.
On the VB side, I am using adodb command objects and my connection uses an application role set. The error displayed states that my Windows login account is not a valid user in the history database; not the application role. Also, my Windows login account IS a user in the history database. I am thinking that this must be a context issue, but I am not sure how to resolve this.
Any ideas would be greatly appreciated.:w00t:[/font]
January 4, 2008 at 10:27 am
It probably has to do something with the application roles:
Application roles bypass standard permissions.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of an instance of SQL Server.
To ensure that all the functions of the application can be performed, a connection must lose default permissions applied to the login and user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked so the user can use the application successfully. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply