?
Solved

Trigger t-sql

Posted on 2000-03-31
2
Medium Priority
?
479 Views
Last Modified: 2008-03-03
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.
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
kpkp earned 200 total points
ID: 2679392
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 http://msdn.microsoft.com/library/psdk/sql/sys-p_3.htm)

Column      Data type            Description      
name

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.      
0
 

Author Comment

by:xoxomos
ID: 2681681
Looks like its all there, but i'm not going after it correctly.  Trying to insert sysobjects.name, sysprotects.id, uid, action, protecttype into logtable, but nothing is giong into it.  I'll try to formulate another question.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question