Trigger t-sql

I'd like the t-sql that would make a trigger which would,whenever a change in
                   permissions or indexes was made to a table or view, it would populate a table(logtable)
                   with getdate(), the id making the change , generate sql script of the original and the new
                   in directory called original and new.
                   If the points seem stingy, that can be negotiated.
Using version 6.5.
Who is Participating?
kpkpConnect With a Mentor Commented:
The 6.5 equivalent of the syspermissions table is SysProtects.  You could use insert/update and delete triggers on this table to record permission changes.  

sysprotects system table

Contains information about permissions that have been applied to security accounts with the GRANT and DENY statements. This table is stored in each database.

(from MSDN

Column      Data type            Description      

id            int                  ID of object to which these permissions apply.      
uid            smallint            ID of user or group to which these permissions apply.      
action            tinyint                  Can have one of these permissions:  
                              26 = REFERENCES
                              193 = SELECT
                              195 = INSERT
                              196 = DELETE
                              197 = UPDATE
                              198 = CREATE TABLE
                              203 = CREATE DATABASE
                              207 = CREATE VIEW
                              222 = CREATE PROCEDURE
                              224 = EXECUTE
                              228 = BACKUP DATABASE
                              233 = CREATE DEFAULT
                              235 = BACKUP LOG
                              236 = CREATE RULE      
protecttype      tinyint                  Can have these values:
                              204 = GRANT_W_GRANT
                              205 = GRANT
                              206 = REVOKE      
columns      varbinary(4000)      Bitmap of columns to which these SELECT or
                              UPDATE permissions apply. Bit 0 indicates all
                              columns; bit 1 means permissions apply to that
                              column and NULL means no information.      
grantor      smallint            User ID of the user who issued the GRANT or REVOKE permissions.      
xoxomosAuthor Commented:
Looks like its all there, but i'm not going after it correctly.  Trying to insert,, uid, action, protecttype into logtable, but nothing is giong into it.  I'll try to formulate another question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.