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.
xoxomosAsked:
Who is Participating?
 
kpkpCommented:
In SQL7 you could try adding a trigger to the SysPermissions table.  Something like this would work for an insert trigger and record all new permissions granted...


INSERT INTO LogTable
      SELECT SysObjects.Name AS Object,
      SysUsersGe.Name AS Grantee,
      SysUsersGo.Name AS Grantor,
      SysPermissions.ActAdd AS GrantedAllCol,
      SysPermissions.ActMod AS DeniedAllCol,
      SysPermissions.SelAdd AS GrantedSelect,
      SysPermissions.SelMod AS DeniedSelect,
      SysPermissions.UpdAdd AS GrantedUpdate,
      SysPermissions.UpdMod AS DeniedUpdate,
      SysPermissions.UpdAdd AS GrantedUpdate,
      SysPermissions.UpdMod AS DeniedUpdate,
      SysPermissions.UpdAdd AS GrantedReferences,
      SysPermissions.UpdMod AS DeniedReferences
      FROM Inserted SysPermissions
      JOIN SysObjects ON SysObjects.ID = SysPermissions.ID
      JOIN SysUsers SysUsersGe ON SysUsersGe.UID = SysPermissions.grantee
      JOIN SysUsers SysUsersGo ON SysUsersGo.UID = SysPermissions.grantor


For more detailed info on the columns in SysPermissions take a look at http://msdn.microsoft.com/library/techart/sql7security.htm
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If you find something like that, let me know!!!
What i use to version scripts, is that I generate scripts manually (using the Scripting Wizard), even if I could use SQL-DMO...

Thinking about this, you could add a trigger to master database tables: syscolumns, sysobjects, sysindexes ...
Sounds like a challenge ...

0
 
xoxomosAuthor Commented:
Forgot to mention, i'm on 6.5
0
 
xoxomosAuthor Commented:
Forgot to mention, i'm on 6.5
0
 
xoxomosAuthor Commented:
Installed a trial version of 7.0 .  Don't understand quite i'm seeing just yet, but that looks what i could use IF i had 7.0.
0
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.