Triggers in sql server 2008 management studio

Question

Hi,

I am trying to set up the trigger in a way that when the administrator (not users) make any changes to the database, all the changed data with the administrator name and time gets saved in the audit table (already created) that has all the possible fields.

I have created triggers on each table for any sort of updates in those tables. The trigger saves the information in audittable. However, i want to restrict this action for administrators only. Like I only want to keep the record of changes made by adminsitrators with their name, time and changes made by them(I have a separate table for adminsitrator names, username, pw and all that).

Can someone please help me with that. Thanks

Answer 1

To get the user you may use:

1. server level (login)

   select system_user , suser_sname() , suser_sid()

2. db level (db user)

   select session_user , current_user , user , user_name() , user_id()

Than and check that this user is admin or not in that additional table.

Answer 2

You can try one of these two functions, depending on what you define as "administrator".

SELECT IS_MEMBER('dbo'), IS_SRVROLEMEMBER('sysadmin')

The IS_MEMBER function evaluates the database role and the IS_SRVROLEMEMBER evaluates the server role. So, if you want to know if the user is a database admin, you would use IS_MEMBER. These will work for user-defined roles as well as built-in roles.

UPDATE:

Here's an example of the trigger that would add data to the audit table when a server administrator inserts data to the table.

CREATE TRIGGER trg_InfoUpdates ON tblCustomerInfo 
FOR INSERT AS

    IF IS_SRVROLEMEMBER('sysadmin') = 1
      BEGIN
        INSERT INTO tblAuditLog (CustomerID)
        SELECT CustomerID
        FROM inserted
      END
    ;