Post reply

Trigger permissions

  • August 3, 2012 at 2:55 am

    #261986

    A customer's database loses data from one specific table when a particular user logs on to the Application. I have created a trigger on that table to log various bits of information as How, when why etc. The trigger includes this

    DECLARE @LASTCOMMAND NVARCHAR(max)

    --get he last command by the current spid:

    SELECT @LASTCOMMAND = DEST.TEXT

    FROM sys.[dm_exec_connections] SDEC

    CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle])

    AS DEST

    WHERE SDEC.[most_recent_session_id] = @@SPID[/size]

    This work ok when the table is updated from Management Studio, but fails when the table is updated via the application with a message about not having permissions to do this. Can anyone advise on permisions please?

  • August 3, 2012 at 9:02 am

    #1521453

    The permissions are those of the user executing the code, so whoever updates the table has their permissions used.

    I doubt most users have access to sys.dm_exec_connections. I would suggest you use a trace instead of a trigger to trap what is happening. You can set up a server side trace to get this.

    Depending on what you are looking for, you could also use the default trace to watch activity (http://www.sqlservercentral.com/articles/64547/)

  • August 3, 2012 at 9:08 am

    #1521459

    Thanks for you reply.

    A trace is impractible because this problem has only occured twice in three months.

  • August 3, 2012 at 9:33 am

    #1521475

    In that case, you could grant permissions to the login to get to the DMVs, but I wouldn't leave them there permanently.

  • August 7, 2012 at 2:31 am

    #1522434

    I am having problems granting permissions when the trigger is fired via the application software. The application is multi user and any user could cause the trigger to fire. I have set permisions for the master login that the application uses to no avail

  • August 7, 2012 at 4:10 am

    #1522477

    How you are authenticating from application.

    SQL Server Authentication or Windows Authentication ?

  • August 7, 2012 at 4:58 am

    #1522501

    SQL Server Authentication

  • August 7, 2012 at 5:54 am

    #1522522

    Check with the permission granted to user throwing the error.

    Requires VIEW SERVER STATE permission on the server

  • August 7, 2012 at 6:54 am

    #1522549

    Thank you

    I have already tried this but it doesn't solve the permissions problem unfortunately.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply